abhay_547
04-18-2010, 08:56 AM
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
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