VBA SQL Syntax Error - Please help

T

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
 
Sort by date Sort by votes
Hi Richard (and anyone else that can help)

I have removed the 'ts' from the code but I am still experiencing the same syntax message.

I have identified the part of the code which is definately causing the problem:

"WHERE `All Data - Pre 07`.Group = " & datinput & "" & Chr(13) & "" & Chr(10)

If I replace the " & datinput & "" part of the code with 'AA' instead the code works. However, I need this part to be substituted by what is entered by the user in the input box which will not always be 'AA'.

I think I either need to:

a) Amend the definition of datinput in the DIM section or
b) Amend the bit of the code which starts "WHERE....

I think I mentioned earlier that I had used this code before but substituting a date entered by the user rather than a string. This link may help

http://www.mrexcel.com/board2/viewtopic.php?t=276012&highlight=

Your help is much appreciated.

Application.ScreenUpdating = False
Dim mygroup As String
Dim datinput As String
mygroup = InputBox("Enter Group", "Please Enter", "AA")
datinput = "{'" & 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

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi Richard (and anyone else that can help)

I have removed the 'ts' from the code but I am still experiencing the same syntax message.

I have identified the part of the code which is definately causing the problem:

"WHERE `All Data - Pre 07`.Group = " & datinput & "" & Chr(13) & "" & Chr(10)

If I replace the " & datinput & "" part of the code with 'AA' instead the code works. However, I need this part to be substituted by what is entered by the user in the input box which will not always be 'AA'.

I think I either need to:

a) Amend the definition of datinput in the DIM section or
b) Amend the bit of the code which starts "WHERE....

I think I mentioned earlier that I had used this code before but substituting a date entered by the user rather than a string. This link may help

http://www.mrexcel.com/board2/viewtopic.php?t=276012&highlight=

Your help is much appreciated.

Application.ScreenUpdating = False
Dim mygroup As String
Dim datinput As String
mygroup = InputBox("Enter Group", "Please Enter", "AA")
datinput = "{'" & 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
You need single quotes around the value provided by datainput, also, as Nate mentioned you don't need any of the chr(13) or chr(10) that result from the macro recorder code. Give the following a try:

Code: 
Application.ScreenUpdating = False
Dim mygroup As String
Dim datinput As String
mygroup = InputBox("Enter Group", "Please Enter", "AA")
datinput = "'" & 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 " & "FROM `All Data - Pre 07` `All Data - Pre 07`" & "WHERE `All Data - Pre 07`.Group = " & datinput & " 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
You must log in or register to reply here.

Similar threads

K
  • Question Question
Run-time error '-2147217900 (80040e14)': Syntax error or access violation
Replies
0
Views
146
kristianb63
K
P
  • Question Question
Runtime error 52 Bad file or number and syntax error
Replies
1
Views
262
rlv01
R
B
  • Solved
Error on method intersect
Replies
2
Views
136
Beneindias
B
gheyman
  • Question Question
Code error when it gets to Refreshing a table(s)
Replies
0
Views
356
gheyman
gheyman
T
  • Solved
Error Number: 1004
Replies
5
Views
169
Tablecloth98
T

Forum statistics

Threads
1,224,798
Messages
6,181,038
Members
453,014
Latest member
Chris258

Share this page

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