Error message on recordset .Edit: Method or data member not found

[SashaBuilder3]

Hi,

Here is a simple VBA code I'm trying to use to update a table (Access 2000):

*****************
Dim dbs
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection

Set dbs = CurrentDb()

Set cnn = CurrentProject.Connection
rst.Open "tbl2", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

rst.MoveFirst

With rst

Do
.Edit
!Data2 = Data2 & "_a"
.Update
.MoveNext
Loop While rst.EOF = False

End With

rst.Close

*******************

When I run it an error message comes up: 'Method or data member not found'


What's wrong with this code?


Thanks,

Alexandre

 

[JPeters]

You need to set your recordset.

Set Rst = DBS.OpenRecordset("tblorqryname", dbOpenDynaset)

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[SashaBuilder3]

Thanks Josh,

I thought I did same thing with

Set cnn = CurrentProject.Connection
rst.Open "tbl2", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect

Anyway, I tried your version and still have the same message with .Edit method.


???

Alexandre

 

[cmmrfrds]

You have a mixture of data objects, some are DAO and some are ADO, but you are not using the DAO objects so it should not be a problem. The suggestion by JPeters is for DAO objects it does not apply to the ADO Open method. Also you should make the cnn New like you did on the recordset object - this may be part of the problem. I assume tbl2 is a table name in your database.

Dim dbs '- DAO object
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection
'-- should be
Dim cnn As New ADODB.Connection
Set dbs = CurrentDb() '- DAO object

Set cnn = CurrentProject.Connection
rst.Open "tbl2", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
'- I don't know the definition of "adCmdTableDirect" I usually use "adCmdTable" when selecting from a table.

'-- Check for EOF at this point or you might not have an object to work with.

If rst.EOF = True then
msgbox "no records"
exit sub
End IF

rst.MoveFirst

 

[SashaBuilder3]

cmmrfrds,

I tried this. Same result. :-(


Thanks anyway!

A.

 

[cmmrfrds]

Do you have a reference set to the ADO library??? Which library?

Set cnn = CurrentProject.Connection
'- at this point you should be able to display the connection string. This would help to identify where the problem is.

debug.print "cnn = "; cnn

 

[SashaBuilder3]

cmmrfrds,

Seems you caught me. I didn't even have a least clue about those settings. Sorry, I just started doing these things.

But I suspected something wrong since not all examples I picked up from manuals worked. For instance these declarations don't go on my system (I use Access 2000):
Dim dbs As database
Dim rst As recordset

Well, could you tell me how to set everything up?

One more thing. If in that piece of code I use .AddNew method it works. Does it tell anything?


Thanks,

Alexandre

 

[JPeters]

Whoops. Glad that you guys got it figured out. I left work last night after posting. Just re-read your original post... I should read some of these closer so I can actually give real advice.. ;-) .. Well, back to the job!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[SashaBuilder3]

Josh,

Looks like your time is EST, yeah? Have a good Thursday!


Alexandre

 

[JPeters]

Close, Central time here in Iowa, USA. It's 8:50am. You have a good Thursday too!

-Josh ------------------
-JPeters
Got a helpful tip for Access Users? Check out and contribute to 'How to Keep Your Databases from becoming Overwhelming!'
thread181-293590
jpeters@guidemail.com
------------------

 

[cmmrfrds]

While in your vba code check the references by going up to tools and then references. There will be a list of libraries and some will be checked. Scroll down and check an ADO library which will be something like the following.

Microsoft ActiveX Data Objects 2.6 library

There may be multiple, 2.1 or 2.5 or 2.6 - take the latest.



Do this and then copy and paste your connection string here like I suggested, then we can go from there. Otherwise the code and syntax looks like it should work.

 

[Guest_imported]

This works just fine with MS-Access 2000

----------------------------------------------
Dim rs As Recordset
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("MainExcelData&quot

Do While Not rs.EOF
rs.Edit
rs!Test = "Testing"
rs.Update
rs.MoveNext
Loop

 

[Guest_imported]

BTW... MainExcelData is an Access table but it would probably work against an updatable query also.

Prior versions of Access would use:

db = DBEngine.Workspaces(0).OpenDatabase("YourDB.mdb&quot

in lieu of:

db = CurrentDB 'This is Access 2K specific


What you're using is the DAO which is built into Access. You don't need to use ADODB unless you using an external data source (i.e. SQL Server, Oracle, DB2). You might be able to use DAO for those too, I just don't know why you would want to.

 

[cmmrfrds]

Alexandre, there are at least 2 libraries that have data objects, some of these objects have the same name but they are different objects and may do things differently. The most common confusion is between the DAO and ADO data objects. Access 2000 defaults to using ADO so if you mix in DAO objects they will not be recognized or vis-versa. You can use both libraries in the same program but you need to be explicit in the naming - especailly where objects have the same name. For example, rst as DAO.Recordset or rst as ADODB.Recordset. Each of these recordsets has properties and methods but they are from different libraries and are completely different code, although the methods may do the same thing.

Best to set these explicit
Dim dbs As DAO.database
Dim rst As DAO.recordset or ADODB.recordset

If you want to set a reference to the DAO library then do it just like you did for the ADO library. I suggest since you are just starting out stick with ADO since that is Microsoft's direction and the ADO library is available in many products from Microsoft and other vendors. Once, you can use ADO in Access they will be the same objects in an ASP Web page or other front end.

 

[Guest_imported]

FYI...

Out of Access 2K help...

When a recordset is asked for in a Microsoft Access database (.mdb), a DAO recordset is returned, in a Microsoft Access project (.adp), an ADO recordset is returned.

The read/write behavior of a form whose Recordset property has been set using Visual Basic is determined by the type of recordset (ADO or DAO) and the type of data (Jet or SQL) contained in the recordset identified by the property.

 

[cmmrfrds]

Robert, FYI....
Do this test in Access 2000.
Create a new access mdb and don't set any references.
Go to references and see what references are checked.
Don't set any additional references.
Link to orders table in the Northwind.mdb.
Paste this code into a standard module.
Run the code.

Function checkthings()
Dim rs As Recordset
Set rs = New Recordset
rs.Open "orders", CurrentProject.Connection
Debug.Print "rs data = "; rs(0)
End Function

Does this return the recordset?
Also, go into the object browser. Do you see the DAO library.
In my case, the above is an ADO recordset. Also, it is the only recordset when I open the object browser. There have been no references set, so, I am assuming this is the default.

 

[Guest_imported]

I'm wrong. You're right.

Interesting how their own help files show that though and since I've upgraded all twenty or so production MDB's from other versions, I've never had to set it.

Thanks...

 

[SashaBuilder3]

So mush I should digest. This ADO-DAO stuff makes me feel uneasy...

Well, here's what I have.

1.In References I have checked 'Microsoft ActiveX Data Objects 2.1 Library'.

2. In declaration part I can do just this:
Dim dbs
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection

If I put
Dim rst As Recordset
Dim dbs As Database

I get error messages (No such user-defined type or so)

And it doesn't accept 'Dim dbs as DAO.database' either.

3. Among methods the system offers when you type a dot after an object (sorry for my silly lingo) there is no 'Edit'. There is, for example, AddNew or Delete but no Edit. None of the several dozens (relying just on the names) seems useful to set values in existing records.


Maybe I need to update the reference library?


Gentlemen, I still need your advice!


Thanks everybody!

Alexandre

 

[SashaBuilder3]

So much I should digest. This ADO-DAO stuff makes me feel uneasy...

Well, here's what I have.

1.In References I have checked 'Microsoft ActiveX Data Objects 2.1 Library'.

2. In declaration part I can do just this:
Dim dbs
Dim rst As New ADODB.Recordset
Dim cnn As ADODB.Connection

If I put
Dim rst As Recordset
Dim dbs As Database

I get error messages (No such user-defined type or so)

And it doesn't accept 'Dim dbs as DAO.database' either.

3. Among methods the system offers when you type a dot after an object (sorry for my silly lingo) there is no 'Edit'. There is, for example, AddNew or Delete but no Edit. None of the several dozens (relying just on the names) seems useful to set values in existing records.


Maybe I need to update the reference library?


Gentlemen, I still need your advice!


Thanks everybody!

Alexandre

 

[cmmrfrds]

I have been using both DAO and ADO objects and it took awhile to work through the confusion. I am glad I started using ADO since my back end databases are both Access and SQL Server. When I started doin ASP pages it was easy to work with the data sources since I had already become familiar with the ADO objects.

I have gotten a lot of information from this link.

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