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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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

C
  • Solved
How to use conditional compiling in VBA
Replies
4
Views
100
Jon Peltier
J
A
  • Solved
VBA formula
Replies
3
Views
176
Anthony47
A
O
  • Solved
Code works EXCEPT when code is running??
Replies
6
Views
183
OxfordCurmudgeon
O
A
  • Solved
Macro for CSV Import
Replies
9
Views
692
Legacy 143009
L
K
  • Question
Sub RunQuery() Syntax Error using VBA
Replies
5
Views
289
RoryA
RoryA

Forum statistics

Threads
1,214,965
Messages
6,122,499
Members
449,089
Latest member
Raviguru

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