HELP!!! Unable to use .Edit with my ADODB.Recordset

[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

 

[grtammi]

Do you have the correct reference to Microsoft ActiveX Data Objects?

Greg

Boss quote from an office meeting: We're going to continue to have these meetings until we figure out why no work is getting done ...

 

[danvlas]

You don't need it. Delete the line

gdbTableO.Edit

It should work. ADO doesn't have the .Edit method...

Good luck





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[laakins]

Thanks both of you. I deleted the .Edit and now the code compiles, but whenever it gets to an .AddNew line it gives me an Overflow error? Any ideas why? Is that also not needed?

 

[danvlas]

From a first look, this line:
gdbTableO.CursorType = adOpenStatic
denies adding records (the recordset is not updatable). You should use adOpenDynamic.

AddNew is required if you want to add records.

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cmmrfrds]

It is okay to use adOpenStatic and usually preferrable. It is more expensive and sometimes difficult to maintain a dynamic cursor. In this case, adOpenStatic is not needed since ADO defaults to a static cursor when it is a client side cursor - adUseClient.

gdbTableO.CursorType = adOpenStatic

I would check the field you are updating it is a reserved word and that may be causing the problem. It is hard to say the what Access is going to do with the reserved work Date.

At least change to
gdbTableO![Date] = Date
this way Access will not evaluate as a reserved word - not a good practice to use reserved words as field names.

 

[danvlas]

I have to disagree...
The ADO adOpenStatic is equivalent to the DAO dbOpenSnapshot.
You can't add or edit records.

But I agree on not using reserved words as field names.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[cmmrfrds]

I use adOpenStatic all the time with updatable recordsets. I am using the OLE-DB Providers for Access and SQL Server, perhaps some of the other Providers are different. The locktype determines whether you can add etc.. The default locktype is read only and would not be updatable.

For example.
rs.Open sql1, cn, adOpenStatic, adLockReadOnly
Without specifying the locktype it is adLockReadOnly and will not be updateable.

This is an updateable recordset.
rs.Open sql1, cn, adOpenStatic, adLockOptimistic

 

[danvlas]

Right...I relied on what MS said on their site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate.asp

(Retrieving and modifying data)

It appears that dbOpenStatic is not equivalent adOpenStatic after all. In DAO it is not updatable, no matter what you do.

My apologies.




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[laakins]

Here is the code I used for the recordset:

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

gdbTableO.Source = TableName
gdbTableO.CursorLocation = adUseClient
gdbTableO.CursorType = adOpenStatic
gdbTableO.LockType = adLockOptimistic
gdbTableO.Open

I'm using adOpenStatic & adLockOptimistic. I use this same code in other projects & I have no problems adding new records.

 

[CCLINT]

This seems to link with thread702-575962, doesn't it?

 

[CCLINT]

Sorry, wrong thread number:

thread709-575972

 

[laakins]

Actually, I posted it here on accident & meant to post it to the Visual Basic: Database Forum. I posted it there & didn't get to reference that thread here before you guys were so quick to respond. For my new problem I created a new thread: thread709-577656

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