PDA

View Full Version : VBA code to import .mdb file into excel 2013



imalc
05-23-2017, 06:21 AM
I’m still only a beginner with VBA and I’m struggling to import data from a MDB file, I have tried to Google it but not found any code that works. I have a program “ASoft32” that stores its data in a .mdb file (WorkData). When I try and import it to Excel 2013 it comes up with “Cannot open database created in a previous version of your application Is it possible using VBA to convert the file then import it into Excel 2013?

mdmackillop
05-23-2017, 04:12 PM
Don't know if this helps. I downloaded the Access sample database NorthWind.mdb. It imports OK to Excel 2016 using this code.

Sub Test()


Dim Srce As String
Dim Tbl As String
Dim DipName As String

Srce = "C:\VBAX\Nwind.MDB"
Tbl = "Employees"
Dispname = "TableNwind"

Application.CutCopyMode = False
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Srce & ";Mode=Share Deny Write;Extended Propert" _
, _
"ies="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:" _
, _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password" _
, _
"="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLE" _
, _
"DB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Valid" _
, _
"ation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array(Tbl)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = Source
.ListObject.DisplayName = Dispname
.Refresh BackgroundQuery:=False
End With
End Sub

imalc
05-25-2017, 03:49 AM
Don't know if this helps. I downloaded the Access sample database NorthWind.mdb. It imports OK to Excel 2016 using this code.

Sub Test()


Dim Srce As String
Dim Tbl As String
Dim DipName As String

Srce = "C:\VBAX\Nwind.MDB"
Tbl = "Employees"
Dispname = "TableNwind"

Application.CutCopyMode = False
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & Srce & ";Mode=Share Deny Write;Extended Propert" _
, _
"ies="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:" _
, _
"Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password" _
, _
"="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLE" _
, _
"DB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Valid" _
, _
"ation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
), Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdTable
.CommandText = Array(Tbl)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = Source
.ListObject.DisplayName = Dispname
.Refresh BackgroundQuery:=False
End With
End Sub




When I run this i'm getting an error [error 1004] on the last line of the code!

mdmackillop
05-25-2017, 04:10 AM
I obviously can't test it. What is the error description?

Edit: Can you post the mdb file?