Retrieve data from an Access database
Posted by Ron on May 08, 2001 4:53 PM
I have an Excel application that we would like to add VBA code to which will retrieve data from an access database and place it into our Excel application. We would like this to be seamless and transparent to the user. Basically, we would like the user to enter an account # and have details brought into a sheet.
Any suggestion or ideas? Thanks.
Posted by J J on May 08, 2001 5:57 PM
References: Microsoft ActiveX Data Objects 2.1 Library
Sub CreateCustomersRecordset2()
'Declare variables
Dim cnNorthwind As ADODB.Connection
Dim rsCustomers As ADODB.Recordset
Dim strSQL As String
On Error GoTo Error_Handler
'set connection to a new connection
Set cnNorthwind = New ADODB.Connection
cnNorthwind.Provider = "Microsoft.Jet.OLEDB.4.0"
cnNorthwind.Open _
"c:\Program Files\Microsoft Office\Office\Samples\NWIND.MDB"
'instantiate recordset
Set rsCustomers = New ADODB.Recordset
'Build the SQL string
strSQL = "select * from customers"
'open books table
rsCustomers.Open strSQL, cnNorthwind
'past records on worksheet
ActiveSheet.Range("A1").CopyFromRecordset rsAdvts
'close recordset and connection
rsCustomers.Close
cnNorthwind.Close
Set rsCustomers = Nothing
Set cnNorthwind = Nothing
Exit Sub
Error_Handler:
MsgBox "An error occurred. The error number is " & Err.Number & _
" and the description is " & Err.Description
Exit Sub
End Sub
