「Access VBA」Use ADO to connect Access

by Unknown 01:51:00 0 comments Access ADO connection database VBA

1. First, you should include the component "Micrsofost ActiveX Data Objects # Library"  from  Tools > Reference. (# means version number)

2. And then, use the follow code to connect the DB and you can use the data from the assigned DB.
(The following example code I write with Access 2010, also you can write with other Microsoft Office production.)

● Data was not in this DB

　　Dim cnn As ADODB.Connection
　　Dim rs As ADODB.Recordset
　　Dim strDbName As String
　　Dim strConnectString As String
　　Dim strQuery As String

　　'The address of DB, you also can use 

   'currentproject.path & "\test1.accdb" to make address dynamic.
　　strDbName = "D:\test1.accdb" 

　　Set cnn = New ADODB.Connection 
　　Set rs = New ADODB.Recordset

　　'The Query's name that I want to get data from. 
　　strQuery="log"


　　With cnn
　　　'If you use Microsoft Office 2007 or 2003, 

     'you should use "Microsoft.Jet.OLEDB.4.0" be your provider.  
　　　.Provider = "Microsoft.ACE.OLEDB.12.0"

　　　'Open the DB you want to get data.  
　　　.Open strDbName 
　　End With

　　rs.Open strQuery, cnn, adOpenStatic, adLockReadOnly

　　'print the data, also can use rs("headname")
　　debug.print rs(0)

● Data was  in this DB

　　Dim cnn As ADODB.Connection
　　Dim rs As ADODB.Recordset
　　Dim strQuery As String

　　Set cnn = CurrentProject.Connection ' Set connection in this DB.
　　Set rs = New ADODB.Recordset
　　strQuery = "log"

　　rs.Open strQuery, cnn, adOpenStatic, adLockReadOnly

　　'print the data, also can use rs("headname") 
　　debug.print rs(0)

3. Finish! : " )