asked on

Query ACCESS 2003 database table

I have a simple need to query the database and have the enter contents of a table, tblHolidays, end up on an Excel worksheet. It is a small table and I have the below, but it does not link the Commandtype. Can I pass the table name as an array?

sandra

Sub GetHolidays()
Dim cnn As New ADODB.Connection
Dim strPath As String

ThisWorkbook.Worksheets("Holidays").Activate
Range("A1").Activate
strPath = "\\Sf1\user1\shared\DepositServices\CIA\SOS_APPLICATIONS\ADMIN\PRD\Admin.mdb"

With cnn
.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strPath & ";" 'Create DB connection
.CommandType = xlCmdTable
.CommandText = Array("tblHolidays")
.SourceDataFile = strPath
.ListObject.DisplayName = "Holidays"
.Refresh BackgroundQuery:=False
End With

End Sub

Jeffrey Coachman

If your ultimate goal here is to export an Access table to Excel, then you can do it like so:

Dim strTableName As String
strTableName = "tblHolidays"
DoCmd.TransferSpreadsheet acExport, , strTableName, "C:\YourFolder\" & strTableName & ".xls", True

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sandra Smith

ASKER

I do not want to export from Access to Excel, but bring in each month the holiday table into a particular worksheet in a workbook with other tabs that use this data. Thus, simply doing a transfer would not work. but the importAccess did the trick.

Thank you both.