VBA SQL Syntax Error - Please help

twinkle99

Board Regular
Joined
Aug 7, 2005
Messages
240
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
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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.
 
Upvote 0
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...
 
Upvote 0
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
 
Upvote 0
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:

Code:
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.,

Code:
Sub foo()
Worksheets(1).QueryTables(1).Refresh False
End Sub
With the correct Item numbers for the Worksheet and QueryTable Objects within the collection... ;)
 
Upvote 0
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...

Code:
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top