ADO vs DAO, opening a database

[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

 

[Ken Dales]

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

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!

 

[Tim Ferguson]

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).

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

 

[Guest]

Ken, you're the man!