How to reference current Access 2003 database with VBA ADO

[GY2]

Just started with Access 2003 and don't understand the Help layout very well
yet. I want to write a Sub in a VBA module which will fetch some records from
the current db but can't figure out how to do it. Do I have to go back to
DAO syntax or can I use ADO? I see the CurrentDB object but the Help seems to
indicate that it is only useful for DAO connections. What's the deal?

 

[Dave Patrick]

If you're using ADO then use something like this;

Dim cnn As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim strSQL1 As String
Set cnn = CurrentProject.Connection
Set rs1 = New ADODB.Recordset

Tools|References and check the box for 'Microsoft ActiveX Data Objects 2.x
Library'

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Just started with Access 2003 and don't understand the Help layout very
well
| yet. I want to write a Sub in a VBA module which will fetch some records
from
| the current db but can't figure out how to do it. Do I have to go back to
| DAO syntax or can I use ADO? I see the CurrentDB object but the Help seems
to
| indicate that it is only useful for DAO connections. What's the deal?

 

[GY2]

Thanks Dave. This CurrentProject does indeed get me to the table I want but
I got there without a reference to a database. I want to run an update query
so I would normally say:

dbMyDB.Execute sqlMySQL$

What do I do in this case? Where's the database?

 

[Dave Patrick]

Give this a go.

Set dbMyDB = CurrentProject.Connection
dbMyDB.Execute sqlMySQL$


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks Dave. This CurrentProject does indeed get me to the table I want
but
| I got there without a reference to a database. I want to run an update
query
| so I would normally say:
|
| dbMyDB.Execute sqlMySQL$
|
| What do I do in this case? Where's the database?

 

[GY2]

Thanks for you help.

Yes, your suggestion works fine but if I've already got an open connection
is it bad form to simply use that one instead of opening two of them?

Dim rs1 As adodb.recordset
Dim cnn As adodb.Connection
'Dim dbMyDB As adodb.Connection Do I need this one as well?

Set rs1 = New adodb.recordset
Set cnn = CurrentProject.Connection
'Set dbMyDB = CurrentProject.Connection

rs1.Open "tblWhatever", cnn

'dbMyDB.Execute sqlMySQL$
cnn.Execute sqlMySQL$

 

[Dave Patrick]

No you don't need two of them. You'll only need;
Remember to always close whatever you open.

Dim cnn As adodb.Connection

Set cnn = CurrentProject.Connection
cnn.Execute sqlMySQL$
cnn.Close
--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks for you help.
|
| Yes, your suggestion works fine but if I've already got an open connection
| is it bad form to simply use that one instead of opening two of them?
|
| Dim rs1 As adodb.recordset
| Dim cnn As adodb.Connection
| 'Dim dbMyDB As adodb.Connection Do I need this one as well?
|
| Set rs1 = New adodb.recordset
| Set cnn = CurrentProject.Connection
| 'Set dbMyDB = CurrentProject.Connection
|
| rs1.Open "tblWhatever", cnn
|
| 'dbMyDB.Execute sqlMySQL$
| cnn.Execute sqlMySQL$

 

[GY2]

Thanks, Dave. I think this piece is clear to me now.

 

[Dave Patrick]

Glad to hear it. You're welcome.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Thanks, Dave. I think this piece is clear to me now.

 

[Albert D.Kallal]

dbMyDB.Execute sqlMySQL$

Well, for the built in DAO, you would go

currentdb.Execute sqMySQL

if you want to do the same thing with ADO, then use the built in ADO
object....

go

currentproject.Connection.Execute sqMSQL


here is two reocrd set loops...

'An example DAO vs ADO recordset loop, you'll see how similar they are:

'--- begin DAO ---
Dim rst As dao.Recordset

Set rst = CurrentDb.OpenRecordset("select * from contacts")
Do While rst.EOF = False
Debug.Print rst!FirstName
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
'--- end DAO ---

'--- begin ADO ---
Dim rs As New ADODB.Recordset

rs.Open ("select * from contacts"), CurrentProject.Connection
Do While rs.EOF = False
Debug.Print rs!FirstName
rs.MoveNext
Loop
rs.Close
Set rs = Nothing

you can happily use either object.