Extract data from MS Access table using excel macro.

[abhay_547]

Hi All,

I have some data in MS Access database tables. I have recorded a macro from excel using import external data function in excel .i.e Data-> Import External Data -> Import Data. Now while importing the data I enter my parameters in Edit query box I have got the sql statement from the access where i had created my query using design view. I had copied the same query from sql view of ms access and then pasted the sql statement in edit query box while recording macro from excel. It imports the correct data at first instance but at the second time while I try to run that recorded macro once again then it shows me a "Compile Error: Syntax error". Below is my macro code.


Sub Macro5()

With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\mydatabase\mydatabase.mdb;Mode=Share Deny Write;Extend" _
, _
"ed Properties="""";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 Databas" _
, _
"e Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fal" _
, "se;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array( _
"SELECT mydata.CAC, mydata.Year, mydata.[Cost Element], mydata.[Cost Element Name], mydata.Name, mydata.Username, mydata.[Document Type], mydata.[Cost Center], mydata.[Ref Doc Num], mydata.Period, mydata.[Name of Offsetting Acct], " _
, _
"mydata.[Document Number], mydata.[Document Header Text], mydata.[CO Object Name], mydata.[Posting Row], mydata.[Reference Doc Type], mydata.[Company Code], mydata.[Cost element Descr], mydata.Subsystem, mydata.[Management Area], m" _
, _
"ydata.[Aux Account Assignment], mydata.[Offsetting acct no], mydata.[Value/obj curr], mydata.[Object Currency], mydata.[Report Currency], mydata.[Posting Date], mydata.[Valin RepCurr], mydata.[Created On], mydata.[Personnel Number" _
, _
"], mydata.[Reversed Flag], mydata.[Reversal Ref Doc], mydata.[DR/CR indicator], [Cost Element Mapping].FSI_LINE3_DESC, [Cost Center mapping].[Product / UBR], [Cost Element Mapping].FSI_LINE2_DESC, [Cost Element Mapping].FSI_LINE1_" _
,,)
.Name = "mydatabase"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\mydatabase\mydatabase.mdb"
.Refresh BackgroundQuery:=False
End With
End Sub





I have gone through the entire recorded macro and I have identified that I am getting syntax error in my macro because my sql statement is getting truncated in my recorded macro. Following is my sql statement which i got from the sql view of my access query, Now I want the below sql statement to be implemented in the above recorded macro


SELECT mydata.CAC, mydata.Year, mydata.[Cost Element], mydata.[Cost Element Name], _
mydata.Name, mydata.Username, mydata.[Document Type], mydata.[Cost Center], _
mydata.[Ref Doc Num], mydata.Period, mydata.[Name of Offsetting Acct], _
mydata.[Document Number], mydata.[Document Header Text], mydata.[CO Object Name], _
mydata.[Posting Row], mydata.[Reference Doc Type], mydata.[Company Code], _
mydata.[Cost element Descr], mydata.Subsystem, mydata.[Management Area], _
mydata.[Aux Account Assignment], mydata.[Offsetting acct no], mydata.[Value/obj curr], _
mydata.[Object Currency], mydata.[Report Currency], mydata.[Posting Date], _
mydata.[Valin RepCurr], mydata.[Created On], mydata.[Personnel Number], _
mydata.[Reversed Flag], mydata.[Reversal Ref Doc], mydata.[DR/CR indicator], _
[Cost Element Mapping].FSI_LINE3_DESC, [Cost Center mapping].[Product / UBR], _
[Cost Element Mapping].FSI_LINE2_DESC, [Cost Element Mapping].FSI_LINE1_DESC _
FROM (mydata INNER JOIN [Cost Element Mapping] ON _
mydata.[Unique Indentifier 1] = [Cost Element Mapping].CE_SR_NO) INNER JOIN _
[Cost Center mapping] ON mydata.[Cost Center] = [Cost Center mapping].[Cost Center] _
WHERE ((([Cost Center mapping].[Product / UBR])="EM Debt"));

Please expedite...:think:

tags fixed

[lucas]

Please use the green vba button instead of the html button when posting code.

[mdmackillop]

Sql = "SELECT mydata.CAC, mydata.Year, mydata.[Cost Element], mydata.[Cost Element Name], " & _
"mydata.Name , mydata.UserName, mydata.[Document Type], mydata.[Cost Center], " & _
"mydata.[Ref Doc Num], mydata.Period, mydata.[Name of Offsetting Acct], mydata.[Document " & _
"Number], mydata.[Document Header Text], mydata.[CO Object Name], mydata.[Posting Row], " & _
"mydata.[Reference Doc Type], mydata.[Company Code], mydata.[Cost element Descr], " & _
"mydata.Subsystem, mydata.[Management Area], mydata.[Aux Account Assignment], " & _
"mydata.[Offsetting acct no], mydata.[Value/obj curr], mydata.[Object Currency], " & _
"mydata.[Report Currency], mydata.[Posting Date], mydata.[Valin RepCurr], mydata.[Created On], " & _
"mydata.[Personnel Number], mydata.[Reversed Flag], mydata.[Reversal Ref Doc], " & _
"mydata.[DR/CR indicator], [Cost Element Mapping].FSI_LINE3_DESC, [Cost Center " & _
"mapping].[Product / UBR], [Cost Element Mapping].FSI_LINE2_DESC, " & _
"[Cost Element Mapping].FSI_LINE1_DESC" & vbCr & _
"FROM (mydata INNER JOIN [Cost Element Mapping] ON mydata.[Unique Indentifier 1] = " & _
"[Cost Element Mapping].CE_SR_NO) INNER JOIN [Cost Center mapping] ON mydata.[Cost Center] = " & _
"[Cost Center mapping].[Cost Center]" & vbCr & _
"WHERE ((([Cost Center mapping].[Product / UBR])=" & Chr(34) & "EM Debt" & Chr(34) & "));"

[abhay_547]

Hi mdmackillop,

The query posted by you was not working, However I have now understood the logic and I have completed the truncated part of my query in my macro and it works fine now. Following is my Code :

Now I need some other help, actually I had created this macro so that I can link all the parameters mentioned in this macro with the combo and list boxes of an excel userform. Now what I need from you is :

1) As far as linking the parameters with Combobox is concerned I can easily do that but when it comes to listbox I am bit confused because in list box user can select multiple items. Can you provide me an example in the below macro where I have used "Or" (which means i have two or more parameters) that how can l link the same with the listbox.

2) Period Issue. I have a month column in my database with the numbers 1 to 12 .i.e 1 is for Jan, 2 is for Feb and so on. Now I have Qtrly tables .i.e I have data for Qtr1 in 2009Qtr1 of my access database and Qtr2 in 2009Qtr2 of same access database and this qtrly tables are separate for each year for e.g 2009Qtr1, 2010Qtr1 etc. I want to put a condition in my macro that if the period selected by user is (considering year) is between 1 to 3 then it should query the data from 2009Qtr1 and so on.


Sub Macro3()
'
'

'
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\mydatabase\mydatabase.mdb;Mode=Share Deny Write;Extend" _
, _
"ed Properties="""";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 Databas" _
, _
"e Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=Fal" _
, "se;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"), _
Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array( _
"SELECT mydata.*, [Cost Center mapping].[Product UBR Code], [Cost Center mapping].[Sub Product UBR Code], [Cost Center mapping].[Sub-sub Product UBR Code], [Cost Element Mapping].FSI_LINE1_code, [Cost Element Mapping].FSI_LINE2_cod" _
, _
"e, [Cost Element Mapping].FSI_LINE3_code, [Country and Region Mapping].Country, [Country and Region Mapping].Region" & Chr(10) & "FROM ((mydata INNER JOIN [Cost Center mapping] ON mydata.[Cost Center] = [Cost Center mapping].[Cost Center]) IN" _
, _
"NER JOIN [Cost Element Mapping] ON mydata.[Unique Indentifier 1] = [Cost Element Mapping].CE_SR_NO) INNER JOIN [Country and Region Mapping] ON mydata.[Company Code] = [Country and Region Mapping].[Company Code]" & Chr(10) & "WHERE ((([Cost Ce" _
, _
"nter mapping].[Product UBR Code])=""P_6957"") AND (([Cost Center mapping].[Sub Product UBR Code])=""P_8456"" Or ([Cost Center mapping].[Sub Product UBR Code])=""P_8453"") AND (([Cost Center mapping].[Sub-sub Product UBR Code])=""P_6975"" " _
, _
"Or ([Cost Center mapping].[Sub-sub Product UBR Code])=""P_6984"") AND ((([Cost Element Mapping].[FSI_LINE1_code])=""F1750000000"") AND (([Cost Element Mapping].[FSI_LINE2_code])=""F1753000000"" Or ([Cost Element Mapping].[FSI_LINE2_code])=""F1757001000"") " _
, _
"AND (([Cost Element Mapping].[FSI_LINE3_code])=""F1753007000"" Or ([Cost Element Mapping].[FSI_LINE3_code])=""F1757002000"") AND ((mydata.Period)=1) AND ((mydata.Year)=2010)));" _
)
.Name = "mydatabase"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = "C:\mydatabase\mydatabase.mdb"
.Refresh BackgroundQuery:=False
End With
End Sub

Thanks for your help in advance. :bow: