Hello all,

I am trying to use the seek command with a recordset based on the Customer table.  Here is the code I have so far:

Const adOpenStatic = 3
Const adLockOptimistic = 3
 
Dim oConnection
Dim oRecordset
Dim sMsg
Dim sConnectString
Dim sSQL
 
sConnectString = "DSN=Quickbooks Data;OLE DB Services=-2;"
sSQL = "SELECT * FROM Customer"
Set oConnection = CreateObject("ADODB.Connection")
Set oRecordset = CreateObject("ADODB.Recordset")
 
oConnection.Open sConnectString
oRecordset.Open sSQL, oConnection, adOpenStatic, adLockOptimistic

oRecordset.Index = "PrimaryKey"

It always chokes on the last line when I try to establish the index.

So, how can I use the seek command with a QB table?

Thanks in advance :^)

You simply state what index you want QODBC to use in a ORDER BY, for example:

sSQL = "SELECT * FROM Customer Order By FullName"

Hello,

Thanks for the quick reply.  I modified my code as you suggested so that it now reads:

sSQL = "SELECT * FROM Customer ORDER BY ListID"

I still get the error message:

Current provider does not support the necesary interface for index functionality. 

Any help would be appreciated!  Thank you.

Hello,

Can you provide some further advice please on how to use the seek command thru QODBC?  Or is there another way to optimize code that updates customers in the customers table.  I have code which establishes which customers need to have their address updated, and then loops through each one.  It is taking way too long; I have 14000 customers in my customers table.  Are there any suggestions to speed this up?

Thank you.

Here is a sample of my code:

Dim db As Database

Dim rso As Recordset

Dim rsrec As Recordset

Dim thispolicy As String

sqlitems = "Select * from QueryThatFindsUpdatedCustomers"

Set db = CurrentDb

Set rs = db.OpenRecordset(sqlitems)

thisListID = rs.Fields("ListID").Value

SQL2 = "Select * from Customer WHERE listID like '"

SQL2 = SQL2 & thisListID & "'"

Set rsrec = db.OpenRecordset(SQL2)

address2 = rs.Fields("addressfromaccess").Value

rsrec.Edit

rsrec.Fields("billaddressaddr2").Value = address2

rsrec.Update

rsrec.Close

rs.MoveNext

Loop

Replace 'like' with a = (equals) type operation. Like operations scan the whole table!