Connecting to Access using macro

  • Thread starter Thread starter George
  • Start date Start date
G

George

Hi there, I am using this code to connect to the access
database but its not opening the tables I want to open:
Sub Auto_Open()
Dim cn As ADODB.Connection
ans = InputBox("What Table you want to analyze?")
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=C:\FolderName\DataBaseName.mdb;"
' open a recordset
End Sub
Pls if you have any information that I can use to fix this
problem let me know
 
Your code merely creates a connection to a database, it doesn't open any tables.

Replace your line:

' open a recordset

With these lines:

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.Source = "SELECT * FROM " & ans
.ActiveConnection = cn
.Open
End With
' Do something with recordset
rs.Close
cn.Close
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

import table access to excel 1
Connecting Excel to Access Using Late Binding 5
Overwrite a record instead of add 3
VBA Excel data to SQL Sever table 1
Excel Import to Access null issue 3
Exporting data with vba from Excel to an Access 2007 db with password 8
ADO data access to excel 2
Error on connection to recordset 2

Back
Top