ADO vs DAO, opening a database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My experience is with DAO. When I write a procedure within an Access DB to open a recordset, here's the code:

Set db = Currentdb()
Set rs = db.OpenRecordset("table", dbOpenDynaset)

This opens a table within the current database and allows me to work with the data.

Now with ADO I do it this way:

Set cnDB = CreateObject("ADODB.Connection")
cnDB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = C:\AccessFile.mdb"
cnDB.Open

Set RS = New ADODB.Recordset
RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic
RS.Open "table", cnDB, , , adCmdTable

However, this has to be written in and run from a file separate from the file that contains the data. If I try to run this in the same database that contains the data, I get an error that the database is locked (makes sense).

Using ADO, how can I look at data where the table and the code exist in the same file? I would just use the DAO model, but I'm having to pull data from OLE DB sources as well.

Please don't tell me that Microsoft, in an attempt to move forward, has actually moved back!
Thx
 
Solution: Use CurrentProject.Connection to set up your
database connection object

See:
http://msdn.microsoft.com/library/default.asp?
url=/library/en-us/dndao/html/daotoadoupdate.asp
-----Original Message-----
My experience is with DAO. When I write a procedure
Click to expand...
within an Access DB to open a recordset, here's the code:
Set db = Currentdb()
Set rs = db.OpenRecordset("table", dbOpenDynaset)

This opens a table within the current database and allows me to work with the data.

Now with ADO I do it this way:

Set cnDB = CreateObject("ADODB.Connection")
cnDB.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = C:\AccessFile.mdb"
cnDB.Open

Set RS = New ADODB.Recordset
RS.CursorType = adOpenDynamic
RS.LockType = adLockOptimistic
RS.Open "table", cnDB, , , adCmdTable

However, this has to be written in and run from a file
Click to expand...
separate from the file that contains the data. If I try to
run this in the same database that contains the data, I
get an error that the database is locked (makes sense).
Using ADO, how can I look at data where the table and the
Click to expand...
code exist in the same file? I would just use the DAO
model, but I'm having to pull data from OLE DB sources as
well.
Please don't tell me that Microsoft, in an attempt to
Click to expand...
move forward, has actually moved back!
 
If I try to run this in the same database that contains the data, I
get an error that the database is locked (makes sense).
Click to expand...

Like Ken says...

or else don't open the database (the first time) in Exclusive mode. It's
perfectly legal, if a bit redundant, to create a handle to a database that
is already open, but only if it's open in shared mode.

HTH


Tim F
 
Back
Top