VBA SQL Syntax Error - Please help

[twinkle99]

When running a Macro I am getting an error message as follows:

Run-time error '1004':SQL Syntax error

The line of code which appears to be effected is:

.refresh Backgroundquery:=False

I dont really know what this part of the code is trying to do and why it is highlighted yellow when I try and run the code. I am basically importing data from Access to Excel and this line is the last line of the code. If I remove this line, the error does not occur and the code completes. However, the data from Access is not imported so I am assuming it is an important part of the code!

Any ideas?

Thanks

If I take this line out, the code

 

[Richard Schollar]

Hi Twinkle

This is a real pain in the bum problem - it gets me every now and then where one day the code has worked, the next I get that error. Can you post your full code please - there are some tweaks we can try.

 

[Jonmo1]

we probably need to see the rest of the code.

at lest up to the line above that says

With ...

it's probably something like

With Sheets("Sheet1").Range("A1")

or something like that...

 

[twinkle99]

Thanks guys

I will try and post the code tomorrow

Thanks

 

[twinkle99]

Hi Guys

Please find attached code. I tried tweaking it but got nowhere. I actually think it might be something to do with the 'mygroup' variable which is entered at the front of the macro via an input box by the user. Basically, the group entered is substituted in the code as datinput1 i.e. "WHERE `All Data - Pre 07`.Group = " & datinput & "". I have used this similar piece of code when entering dates and never had this problem. I expect there is a simple solution to this. Thanks for your time


Application.ScreenUpdating = False
Dim mygroup As String
Dim datinput As String
mygroup = InputBox("Enter Group", "Please Enter", "AA")
datinput = "{ts'" & mygroup & "'}"

Sheets("AA").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\2007.mdb;DefaultDir=C:\;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `All Data - Pre 07`.Group, `All Data - Pre 07`.code, `All Data - Pre 07`.`Number`, `All Data - Pre 07`.Amount" & Chr(13) & "" & Chr(10) & "FROM `All Data - Pre 07` `All Data - Pre 07`" & Chr(13) & "" & Chr(10) & "WHERE `All Data - Pre 07`.Group = " & datinput & "" & Chr(13) & "" & Chr(10) & "ORDER BY `All Data -" _
, " Pre 07`.`Number`")
.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.Refresh BackgroundQuery:=False

End With

 

[NateO]

Gah, the syntax the Recorder spits out for MS Query is pretty offensive... All of those Chr(13)'s are ridiculous, why on Earth does the Recorder/MS Query do that?

With this:

With ActiveSheet.QueryTables.Add

Do you really need to add a new QueryTable Object every time? Or should you just be refreshing the one that's already in place? E.g.,

Sub foo()
Worksheets(1).QueryTables(1).Refresh False
End Sub

With the correct Item numbers for the Worksheet and QueryTable Objects within the collection...

 

[Jonmo1]

I'm just guessing, can't test it.. But It looks like you have backgroundquery set to true earlier in the code, then setting false on the last line...

maybe if it is set to false in the first line you can just remove the last line...

Application.ScreenUpdating = False 
Dim mygroup As String 
Dim datinput As String 
mygroup = InputBox("Enter Group", "Please Enter", "AA") 
datinput = "{ts'" & mygroup & "'}" 

Sheets("AA").Select 
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _ 
"ODBC;DSN=MS Access Database;DBQ=C:\2007.mdb;DefaultDir=C:\;DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _ 
)), Destination:=Range("A1")) 
.CommandText = Array( _ 
"SELECT `All Data - Pre 07`.Group, `All Data - Pre 07`.code, `All Data - Pre 07`.`Number`, `All Data - Pre 07`.Amount" & Chr(13) & "" & Chr(10) & "FROM `All Data - Pre 07` `All Data - Pre 07`" & Chr(13) & "" & Chr(10) & "WHERE `All Data - Pre 07`.Group = " & datinput & "" & Chr(13) & "" & Chr(10) & "ORDER BY `All Data -" _ 
, " Pre 07`.`Number`") 
.Name = "Query from MS Access Database" 
.FieldNames = True 
.RowNumbers = False 
.FillAdjacentFormulas = False 
.RefreshOnFileOpen = False 
.BackgroundQuery = False 
.RefreshStyle = xlInsertDeleteCells 
.SavePassword = False 
.SaveData = True 
.AdjustColumnWidth = True 
.RefreshPeriod = 0 

End With

 

[twinkle99]

Hi guys

Jonmo, I tried removing the last line and changed Background Query to False further up but I still get the Syntax error. I am actually convinced that it is something to do with the " & datinput & " which is taken from the input box entered by the user because if I remove it the code seems to work. Have I written that bit correctly. It works fine in another code but the datinput is a date rather than a string.

Nate, I am not sure what you mean sorry.

 

[twinkle99]

I've also just noticed that in the Dim section of the code: datinput = "{ts'" & mygroup & "'}", I am not sure if I need the ts bit?. mygroup is entered by the user and will either be AA, AB, AC, AD, AE or AF. I am sure this would solve the problem.

 

[Richard Schollar]

Twinkle

I've always thought that ts was used to denote date/time fields so it may well be that you don't need this in there to work (in which case that part of the SQL statement should look like:

Group = 'AA'

Note that I think the use of the back quotes (`) may well give you problems in this code too. For the table name, you should use square brackets [] instead.