How to reference current Access 2003 database with VBA ADO GY2 30.01.2006 18:56:27 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?

Re: How to reference current Access 2003 database with VBA ADO "Dave Patrick" <DSPatrick[ at ]nospam.gmail.com> 30.01.2006 22:41:13 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 "GY2" wrote: | 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?

Re: How to reference current Access 2003 database with VBA ADO "GY2" <2muchspam[ at ]wherever.com> 31.01.2006 21:05:29 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" <DSPatrick[ at ]nospam.gmail.com> wrote in message news:%23OCAF4eJGHA.3728[ at ]tk2msftngp13.phx.gbl... [Quoted Text] > 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 > > "GY2" wrote: > | 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? > >

Re: How to reference current Access 2003 database with VBA ADO "Dave Patrick" <DSPatrick[ at ]nospam.gmail.com> 01.02.2006 01:46:06 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 "GY2" wrote: | 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?

Re: How to reference current Access 2003 database with VBA ADO "GY2" <2muchspam[ at ]wherever.com> 01.02.2006 16:07:57 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" <DSPatrick[ at ]nospam.gmail.com> wrote in message news:ufNlCEtJGHA.2036[ at ]TK2MSFTNGP14.phx.gbl... [Quoted Text] > 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 > > "GY2" wrote: > | 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? > >

Re: How to reference current Access 2003 database with VBA ADO "Dave Patrick" <DSPatrick[ at ]nospam.gmail.com> 02.02.2006 03:44:50 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 "GY2" wrote: | 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$

Re: How to reference current Access 2003 database with VBA ADO "GY2" <2muchspam[ at ]wherever.com> 02.02.2006 13:01:46 Thanks, Dave. I think this piece is clear to me now. "Dave Patrick" <DSPatrick[ at ]nospam.gmail.com> wrote in message news:eiayCr6JGHA.1192[ at ]TK2MSFTNGP11.phx.gbl... [Quoted Text] > 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 > > "GY2" wrote: > | 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$ > >

Re: How to reference current Access 2003 database with VBA ADO "Dave Patrick" <DSPatrick[ at ]nospam.gmail.com> 03.02.2006 03:24:29 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 "GY2" wrote: | Thanks, Dave. I think this piece is clear to me now.

Re: How to reference current Access 2003 database with VBA ADO "Albert D.Kallal" <PleaseNOOOsPAMmkallal[ at ]msn.com> 28.02.2006 23:06:45 [Quoted Text] > 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. -- Albert D. Kallal (Access MVP) Edmonton, Alberta Canada pleaseNOOSpamKallal[ at ]msn.com http://www.members.shaw.ca/AlbertKallal