Search code examples
vbaexcelms-accessms-access-2003

Read data from a table in .MDB file into Excel 2013 with VBA

I'm trying to import some data from an Access .MDB file into an Excel 2013 spreadsheet.

So far, I've tried converting the database to 2007 format but that hasn't worked (2 methods I've tried so far)

Is there a simpler way of importing data straight from a table in the .MDB file into a sheet in my Excel 2013 spreadsheet? (End result)

Sub convertaccessdb()
Application.ConvertAccessProject _
 SourceFilename:="C:\new\pabxcalls.mdb", _
 DestinationFilename:="C:\My Documents\pabxcalls2007.accdb", _
 DestinationFileFormat:=acFileFormatAccess2007
End Sub
' **** THIS GIVES AN RUNTIME ERROR 438 - OBJECT DOESN'T SUPPORT THIS PROPERTY OR METHOD ***

And

Sub Command7_Click()
Dim app As New Access.Application
app.Visible = True
app.AutomationSecurity = msoAutomationSecurityLow
app.SysCmd 603, "C:\New\pabxcalls.mdb", "C:\new\pabxcalls_new.mdb"
Set app = Nothing
End Sub
' *** THIS RUNS, BUT DOES NOT OUTPUT THE FILE REQUIRED ***

Thanks guys!!!

MDB Description

Solution

  • Found out that I didn't actually have to convert the database. Simply pull data using a query and ADODB connection, as follows.

    Thanks anyway!

    Sub GetCallData()

Dim objAdoCon As Object
Dim objRcdSet As Object
Dim DailyOutgoing, DailyIncoming, MonthlyOutgoing, MonthlyIncoming As String

DailyOutgoing = "SELECT calls.Extension,Sum(calls.Duration) FROM calls WHERE (((calls.Calldate)=Date())) AND (((calls.calltype)=""O"")) GROUP BY calls.Extension, calls.Calldate ;"
DailyIncoming = "SELECT calls.Extension,Sum(calls.Duration) FROM calls WHERE (((calls.Calldate)=Date())) AND (((calls.calltype)=""I"")) GROUP BY calls.Extension, calls.Calldate ;"

Set objAdoCon = CreateObject("ADODB.Connection")
Set objRcdSet = CreateObject("ADODB.Recordset")

 objAdoCon.Open "Provider = Microsoft.Jet.oledb.4.0;Data Source = \\remotehost\PABXSoft\Call Collector\Data\pabxcalls.mdb"

 ' *** GET DAILY OUTGOING ***

 ThisWorkbook.Worksheets("CALL_DATA").Range("A3:B24").Value = ""
 objRcdSet.Open DailyOutgoing, objAdoCon
 ThisWorkbook.Worksheets("CALL_DATA").Range("A3").CopyFromRecordset objRcdSet
Set objRcdSet = Nothing

 ' *** GET DAILY INCOMING ***

Set objRcdSet = CreateObject("ADODB.Recordset")
 ThisWorkbook.Worksheets("CALL_DATA").Range("A27:B46").Value = ""
 objRcdSet.Open DailyIncoming, objAdoCon
 ThisWorkbook.Worksheets("CALL_DATA").Range("A27").CopyFromRecordset objRcdSet

Set objAdoCon = Nothing
Set objRcdSet = Nothing

End Sub