Error in query statement in VBA

[Mac_Wood]

Hi Guys,
Can you help please? I have entered the following SQL statement in my sub and I got the following error:

The SELECT statement includes a reserved word or argument name that is missspelled or missing, or the punctuation is incorrect

Where have I gone wrong?

Dim MySQL As String
MySQL = "SELECT XRayedConsignments.[EntryDate/Time], XRayedConsignments.EntryTime, XRayedConsignments.AWBNumber, XRayedConsignments.Pieces, XRayedConsignments.Weight, XRayedConsignments.OpsIDNumber, XRayedConsignments.Mail, XRayedConsignments.Transhipment, XRayedConsignments.Iberia, XRayedConsignments.UnitedAirlines, XRayedConsignments.ELAL, XRayedConsignments.AirMauritus, FROM XRayedConsignments"
MyRecordset.Open MySQL

Thanks very much

 

[Uncle Gizmo]

I would guess it is unhappy with this part here: .[EntryDate/Time], time being a reserved word I reckon.

Reserved words are the words reserved by the program for its own use, it does not like it if you use them in your code.

To proof yourself against falling foul of reserve words, within your own program and maybe other programs that yours interacts with, a good strategy is never to use an actual word! For example the word that is not a reserved word today, may well be a reserved word in the future, therefore the strategy is never to use a real word in your code, the naming convention prefix in words with "str" "txt" "cbo" goes a long way to preventing conflicts, you don't have to use a naming convention, you could prefix a word for example with your own initials.

 

[Uncle Gizmo]

On a second look it could be this character: /

 

[Mac_Wood]

I renamed the field dteEntryDateTime but still got the error message

 

[Mac_Wood]

I renamed the field dteEntryDateTime but I stll got the error message.

 

[Uncle Gizmo]

Change this line Thus:

MyRecordset.Open "Select * FROM XRayedConsignments"

What result do you get?

 

[Rabbie]

On a second look it could be this character: /

Access definitely wouldn't like a / in the middle of a name.

 

[WayneRyan]

And it absolutely hates this:

XRayedConsignments.AirMauritus, FROM XRayedConsignments

Wayne

 

[Mac_Wood]

Thanks Uncle Gizmo that worked a treat. Unfortunately I now have a runtime error 432 File name or class name not found during automation operation. Here's my code:

Private Sub btnExportToExcel_Click()
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn

Dim MySQL As String
MySQL = "SELECT XRayedConsignments.[dteEntryDateTime], XRayedConsignments.EntryTime, XRayedConsignments.AWBNumber, XRayedConsignments.Pieces, XRayedConsignments.Weight, XRayedConsignments.OpsIDNumber, XRayedConsignments.Mail, XRayedConsignments.Transhipment, XRayedConsignments.Iberia, XRayedConsignments.UnitedAirlines, XRayedConsignments.ELAL, XRayedConsignments.AirMauritus, FROM XRayedConsignments"
MyRecordset.Open "Select * FROM XRayedConsignments"


                                                                         
Dim MySheetPath As String
MySheetPath = "E:\Worksheets\MyRecordsetSheet.xls"
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)



Xl.Visible = True
XlBook.Windows(1).Visible = True

Set XlSheet = XlBook.Worksheets(1)

XlSheet.Range("A4").CopyFromRecordset MyRecordset

MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing
End Sub

Look forward to your suggestions.

 

[Mac_Wood]

And it absolutely hates this:

XRayedConsignments.AirMauritus, FROM XRayedConsignments

Wayne

Wayne I did end the statement with double quotes is there something else wrong with this section?

 

[boblarson]

Instead of this:

Set Xl = CreateObject("Excel.Application")

Try this (when using early binding like you are if you Dim Xl as Excel.Application)

Set Xl = New Excel.Application

and then

Set xlBook = Xl.Workbooks.Open(MySheetPath)

 

[WayneRyan]

Mac,

With the "," after AirMauritus the parser will think that there is one more
field and its name is "FROM". "FROM" would be the reserved word that the
Error Message was referring to.

Wayne

 

[Mac_Wood]

Thanks Bob for your suggestion. I changed both lines but got a rte 424 Object required.

 

[Mac_Wood]

Hi Wayne,

Mac,

With the "," after AirMauritus the parser will think that there is one more
field and its name is "FROM". "FROM" would be the reserved word that the
Error Message was referring to.

Wayne

Your browser must be displaying my code differently because I don't have double quotes before and after the comma or around the FROM. I copied the SQL statement straight from the SQL statement in the query and replaced the semi-colon with double quotes.

 

[Rabbie]

The double quotes are just to make the comma stand out. You shouldn,t have a comma just before the FROM. Remove it and see if that helps.

should be

XRayedConsignments.AirMauritus FROM

and not

XRayedConsignments.AirMauritus, FROM

 

[boblarson]

Yep, I messed that one about the comma just before the FROM. You can't have one of those there like Wayne and Rabbie have mentioned.

 

[Mac_Wood]

Thanks guys,
That has sorted the SQL Statement. Can you give any more guidance on the rte 424 object required?

 

[Rabbie]

Are you still getting the rte 424 object required error? I sort of assumed it was caused by the incorrect comma.

 

[Mac_Wood]

Yup the little beauty appeared after I corrected the SQL Statement.

 

[boblarson]

Wanna post the full code you have now and the mark the line which generates the error?