I'm trying to call the ADODB.Rs 'seek' method and am having problems. Here's the situation:
Dim rs As ADODB.Recordset
Dim con As ADODB.Connection
Set rs = New ADODB.Recordset
Set con = Application.CurrentProject.Connection
rs.Open "SELECT * FROM t_cycle_time_original", con, adOpenKeyset, adLockReadOnly, adCmdTableDirect
If rs.Supports(adSeek) And rs.Supports(adIndex) Then
I get a run-time error '3251', with description "Current provider does not support the necessary interface for index functionality" when I try to access the index/seek methods and both support calls return false.
I can utilize these methods outside of this database when I create a brand new connection in another database while this one is close, it looks like there is something wrong with utilizing the 'Application.currentproject.connection'.
I have tried creating a new connection in this situation, rather than using the old one and I get a connection error stating 'User Admin on machine xxxx has placed this database in a state where it cannot be connected.'
I got home and created a brand new database and a test table. I then wrote the same code to seek and it worked.
Anyway, once I do figure it out though, the question still remains, which is better to get at data and update? Grab it with a RecordSet and update it or write a query to update the data and pass in the values.
Apparently the cause of the problem was the fact that I was using a SQL statement as a source in the recordset.open method and not just a table. I don't think it stated that anywhere in the documentation?
At any rate, should I use recordsets to update data or update sql?