Import table from Access to Excel using VBA (2007)

[davidb88]

Hi - I am creating a custom ribbon in Excel to perform a variety of analysis on a dataset that is refreshed everyday. The first button I have included on the ribbon is one that I would like to import data into an Excel worksheet from an Access table. I have the following module, however, I am getting an error on line 25, ".Open". Does anyone have any idea why I am getting this error or if there is a better way to connect to a database to pull in data from a table? Thanks!

Sub Macro1(control As IRibbonControl)
    
'Declare variables.
Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbFileName As String
Dim strSQL As String
Dim DestinationSheet As Worksheet
'Set the assignments to the Object variables.
Set dbConnection = New ADODB.Connection
Set dbRecordset = New ADODB.Recordset
Set DestinationSheet = Worksheets("Sheet1")


'Define the Access database path and name.
dbFileName = "C:\Users\david\Documents\Database2.accdb"


'Use SQL's SELECT and FROM statements for importing Table1.
strSQL = "SELECT [Today's Date], [Request Number], [Packet #], [Inquire Type], [Description/Issue], [Comments], [State] .* FROM tblmaster;"


'Clear the destination worksheet.
DestinationSheet.Cells.Clear


With dbConnection
'Open the connection.
.Open
'The purpose of this line is to disconnect the recordset.
.CursorLocation = adUseClient
End With


With dbRecordset
'Create the recordset.
.Open strSQL, dbConnection
'Disconnect the recordset.
Set .ActiveConnection = Nothing
End With


DestinationSheet.Range("A2").CopyFromRecordset dbRecordset


DestinationSheet.Range("A1:G1").Value = _
Array("Today's Date", "Request Number", "Packet #", "Inquire Type", "Description/Issue", "Comments", "State")


'Close the recordset.
dbRecordset.Close
'Close the connection.
dbConnection.Close


'Release Object variable memory.
Set dbRecordset = Nothing
Set dbConnection = Nothing
Set DestinationSheet = Nothing


End Sub

 

[Norie]

What is the error?

If it's occuring when you try and open the connection then the first thing to check would be the connection string.

 

[davidb88]

I get the following error:

Run timer error '2147467259 (800004005)':

[Microsoft][OBDC Driver Manager] Data source name not found and no default driver specified

Any idea what that might mean?

 

[Norie]

Basically that ADO/VBA isn't finding the data source specified in the connection string and you haven't specified a driver.

Can you post the connection string?

 

[davidb88]

I thought that at the beginning of the module I had established the connection. Is that what you mean by connection string? If not, do I need to establish a connection separately? Thanks for your help!

Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbFileName As String
Dim strSQL As String
Dim DestinationSheet As Worksheet
'Set the assignments to the Object variables.
Set dbConnection = New ADODB.Connection
Set dbRecordset = New ADODB.Recordset
Set DestinationSheet = Worksheets("Sheet1")

 

[Norie]

No you need a connection string to make the connection.

I actually thought you had one but had admitted it from the code because it contained sensitive data, eg passwords.

Here's example code that sets up a connection to an Access database named World.accdb which is located in C:\Data.

    dbPath = "C:\Data\World.accdb"
    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

    Set con = New ADODB.Connection

    con.ConnectionString = strConnection

    con.Open

You would obviously need to adjust dbPath to point to your database and might have to change the Provider based on the version of Access the database was created in.

 

[davidb88]

Thank you, Norie. I should have realized that I wasn't actually establishing a connection anywhere!

 

[Norie]

So did you get it to work?

 

[davidb88]

I am still getting an error when I am trying to open the recordset. It says "Invalid Bracketing of Name". This is the current code:

Sub Macro1(control As IRibbonControl)
    
    
'Declare variables.
Dim dbConnection As ADODB.Connection
Dim dbRecordset As ADODB.Recordset
Dim dbPath As String
Dim strSQL As String
Dim DestinationSheet As Worksheet


'Set the assignments to the Object variables.
Set dbConnection = New ADODB.Connection
Set dbRecordset = New ADODB.Recordset
Set DestinationSheet = Worksheets("Sheet1")




dbPath = "C:\Documents\Database2.accdb"
strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath


Set con = New ADODB.Connection


con.ConnectionString = strConnection


con.Open


'Use SQL's SELECT and FROM statements for importing Table1.
strSQL = "SELECT [Today's Date], [Request Number], [Packet #], [Inquire Type], [Description/Issue], [Comments], [State] .* FROM tblmaster;"




'Clear the destination worksheet.
DestinationSheet.Cells.Clear




With dbRecordset
'Create the recordset.
.Open strSQL, strConnection
'Disconnect the recordset.
Set .ActiveConnection = Nothing
End With




DestinationSheet.Range("A2").CopyFromRecordset dbRecordset




DestinationSheet.Range("A1:G1").Value = _
Array("Today's Date", "Request Number", "Packet #", "Inquire Type", "Description/Issue", "Comments", "State")




'Close the recordset.
dbRecordset.Close
'Close the connection.
dbConnection.Close




'Release Object variable memory.
Set dbRecordset = Nothing
Set dbConnection = Nothing
Set DestinationSheet = Nothing




End Sub