 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / February 2006How to reference current Access 2003 database with VBA ADO
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? 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'  SignatureRegards, 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? 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? > If you're using ADO then use something like this; > [quoted text clipped - 17 lines] > to > | indicate that it is only useful for DAO connections. What's the deal? Give this a go. Set dbMyDB = CurrentProject.Connection dbMyDB.Execute sqlMySQL$ SignatureRegards, 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 [quoted text clipped - 3 lines] | | What do I do in this case? Where's the database? 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$ > Give this a go. > [quoted text clipped - 10 lines] > | > | What do I do in this case? Where's the database? 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 SignatureRegards, Dave Patrick ....Please no email replies - reply in newsgroup. Microsoft Certified Professional Microsoft MVP [Windows] http://www.microsoft.com/protect | Thanks for you help. | [quoted text clipped - 13 lines] | 'dbMyDB.Execute sqlMySQL$ | cnn.Execute sqlMySQL$ Thanks, Dave. I think this piece is clear to me now. > No you don't need two of them. You'll only need; > Remember to always close whatever you open. [quoted text clipped - 22 lines] > | 'dbMyDB.Execute sqlMySQL$ > | cnn.Execute sqlMySQL$ Glad to hear it. You're welcome. SignatureRegards, 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. > 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. SignatureAlbert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal@msn.com http://www.members.shaw.ca/AlbertKallal |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.