1

I am trying to find values in my recordset with Find as Seek does not seem to be supported, but I can't get past the error

Rowset does not support scrolling backward

According to this SO thread, I have to specify adOpenDynamic, but this did not change the error code.

I am using a stored procedure (sp_fkeys), which returns all keys for all tables, and in this case for one table as I specify the table name.

Private Sub maintablebox_Change()

Dim cnn As ADODB.Connection
Dim keys As ADODB.Recordset
Set cnn = New ADODB.Connection
connstring = "omitted"
cnn.Open connstring

Set keys = New ADODB.Recordset
keys.CursorLocation = adUseServer
query = "EXEC sp_fkeys @fktable_name = 'astAssets'"
keys.Open query, connstring, adOpenDynamic, adLockReadOnly

' >>>>>Error on the line below
keys.Find "PKTABLE_NAME = 'astAssetTypes'"

Debug.Print keys.Fields("FKCOLUMN_NAME")
End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39

2 Answers2

0

I tested your code and could reproduce the error. For me it was solved by adding an moveFirst.

Private Sub maintablebox_Change()

Dim cnn As ADODB.Connection
Dim keys As ADODB.Recordset
Set cnn = New ADODB.Connection
Set keys = New ADODB.Recordset

cnn.Open DBPORT

Set keys.ActiveConnection = cnn
keys.CursorType = adOpenStatic

keys.Open "Select city_name, afas_rel_number, city_code from pkn_cities where has_month_report = true order by city_name ASC;"

keys.MoveFirst 'this did the trick for me
keys.Find "city_code = '1366'"
Debug.Print keys.Fields("city_name")

End Sub
Tim Stack
  • 3,209
  • 3
  • 18
  • 39
Steven
  • 1
  • 1
  • Wouldn't the CursorType be overruled later by `adOpenDynamic` in the `keys.Open` statement? – Tim Stack Apr 29 '19 at 08:31
  • Going to answer my own question and say that it indeed does. Regardless, setting the cursor type to `adOpenStatic` works neither in the `keys.Open` statement nor by setting it right after `Set keys = New ADODB.Recordset` – Tim Stack Apr 29 '19 at 08:33
  • Hmm, that's weird. This is some code I used in the past as an example. [link] (https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/find-method-example-vb?view=sql-server-2017) – Steven Apr 29 '19 at 08:47
  • No change, unfortunately. `keys.MoveLast` does not work either: "Rowset does not support fetching backward" – Tim Stack Apr 29 '19 at 08:52
  • I did play around with `MoveFirst` before starting this thread. Seeing as how it did work for you, I am attributing the error to the stored procedure – Tim Stack Apr 29 '19 at 11:34
0

The problem lies not in the keys.CursorType statement, but in the keys.CursorLocation statement.

I replaced keys.CursorLocation = adUseServer with keys.CursorLocation = adUseClient, which immediately resolved the issue.

The working code has become

Private Sub maintablebox_Change()

Dim cnn As ADODB.Connection
Dim keys As ADODB.Recordset
Set cnn = New ADODB.Connection
connstring = "omitted"
cnn.Open connstring

Set keys = New ADODB.Recordset
keys.CursorLocation = adUseClient
query = "EXEC sp_fkeys @fktable_name = 'astAssets'"
keys.Open query, connstring, adOpenDynamic, adLockReadOnly

keys.Find "PKTABLE_NAME = 'astAssetTypes'"

Debug.Print keys.Fields("FKCOLUMN_NAME")
End Sub

I found the solution on this vbforums thread. I noticed the OP using a stored procedure just like I am, so the error code I was presented with may be due to that factor.

I have only limited knowledge of ADODB recordsets, so if someone would be willing to explain why setting the cursorlocation to adUseClient fixed my problem, please be my guest.

Tim Stack
  • 3,209
  • 3
  • 18
  • 39