Help!!! Unable to use .Edit with my ADODB.recordset 2

[laakins]

I am connecting to an Access 2000 database through VB6. For some reason it doesn't like the .Edit on my ADODB.Recordset object. It gives me an "Method or data member not found". Here is an example of my code:

Public gdbDatabase As ADODB.Connection
Public gdbTableO As ADODB.Recordset

Set gdbDatabase = New ADODB.Connection

gdbDatabase.Provider = "Microsoft.Jet.OLEDB.4.0"
gdbDatabase.ConnectionString = "C:\database.mdb"
gdbDatabase.Open

Set gdbTableO = New ADODB.Recordset
Set gdbTableO.ActiveConnection = gdbDatabase

gdbTableO.Source = "SELECT * FROM table"
gdbTableO.CursorLocation = adUseClient
gdbTableO.CursorType = adOpenStatic
gdbTableO.LockType = adLockOptimistic
gdbTableO.Open

gdbTableO.Edit
gdbTableO!Date = Date
gdbTableO.Update

 

[LazyMe]

That's because it doesn't have an edit method.

Just change whatever you like and call update.

Greetings,
Rick

 

[laakins]

Great one less line of code!! Thanks. Maybe you could help me this one to. I am changing all my previous DAO code to ADODB. Previously I did the following:

gdbTableO.Filter = "Acct = '" & gAcct & "'"
Set rec = gdbTableO.OpenRecordset
rec.Edit
If gStatementReorder = True Then
rec!StatementReorder = -1
Else: rec!StatementOrderDate = Date
End If
rec.Update
rec.Close

Well, obviously I can get rid of the .Edit, but it doesn't like the OpenRecordset. I basically want to find a record and change a value in the recordset I've already opened. I'm sure there is another, MUCH BETTER, way to do this. Any help would be much appreciated. Thanks again for that other tidbit.

 

[LazyMe]

The fastest way would be to execute plain SQL UPDATE queries against the ADO connection object (use the execute method of the connection for this).

Greetings,
Rick

 

[CCLINT]

Or just set the Filter After opening the recordset, or, possibly better, use the Find method:

Set rec = gdbTableO.Clone
With rec
.Filter = "Acct = '" & gAcct & "'"
If gStatementReorder = True Then
.Collect("StatementReorder&quot = -1
Else
.Collect("StatementOrderDate&quot = Date
End If
.UpdateBatch
.Close
End With
Set rec=Nothing

Or

Set rec = gdbTableO.Clone
With rec
.Find "Acct = '" & gAcct & "'"
If gStatementReorder = True Then
.Collect("StatementReorder&quot = -1
Else
.Collect("StatementOrderDate&quot = Date
End If
.UpdateBatch
.Close
End With
Set rec=Nothing

 

[laakins]

Thanks both of you. I gave you both stars. The code compiles now, but whenever it gets to the .AddNew line it gives me an Overflow error? Any ideas why?

 

[CCLINT]

Well, we were talking about updates.
Will need to see what your are doing with the AddNew now...

 

[laakins]

I get past the AddNew line now, but then it gives me a 3265 "Item cannot be found in the collection corresponding to the requested name or ordinal." when I get to specific fields. It allows certain fields but not others. The field names are correct because I copied them directly from the database & they worked in DAO. I use the same syntax in other code & no problems. Not quite sure what to do now. Here is just a little bit of the code:

gdbTableO.AddNew
gdbTableO!OriginationDate = Date 'didn't like this one
gdbTableO!Acct = gAcct 'took this one
gdbTableO!FirstName = "Sue" 'didn't like this one
gdbTableO!LastName = "Smith" 'didn't like this one
gdbTableO.Update

Actually looking at it, it seems that the only thing the code recognizes the the primary key field which is the Acct field. Please help.

 

[LazyMe]

Then they're not in the fields collection of the table. You have to explicitly select all fields you want to reference or take the whole table...

Greetings,
Rick

 

[laakins]

Here is the code I use for the recordset:

Set gdbTableO = New ADODB.Recordset
Set gdbTableO.ActiveConnection = gdbDatabase

gdbTableO.Source = "SELECT * FROM tblAccountInfo"
gdbTableO.CursorLocation = adUseClient
gdbTableO.CursorType = adOpenStatic
gdbTableO.LockType = adLockOptimistic
gdbTableO.Open

I'm selecting all the columns so they should all be in the collection.

 

[CCLINT]

This links to thread702-575962 doesn't it?

I wish you would have mentioned this!
Please do not do this with-out a reference to the other thread.

 

[laakins]

Sorry, it was sort of a whole new problem so I started a new thread. I've got people here who are doing this process manually until I can get this fixed & I've been given 2 other major projects I have to finish before July. I'm a chicken with its head cut off. Barely had time to post. I'll make sure to reference other related threads next time. Thanks.

 

[laakins]

Actually, for my new problem I created a new thread: Thread709-577656

Again, my apologies. Please reference the above thread. Thanks for your help.