Hello,
I've been trying to retrieve some data from an Access database, but I'm getting the above error message followed by 'Syntax error in ORDER BY clause.'
Calculation_SelectedProcessOrder is a Public variable created on another part of the code. I'm guessing there is something wrong with the syntax of my query but this is the first time I'm doing one so I have no idea what it is I'm doing wrong. Any help will be greatly appreciated.
I've been trying to retrieve some data from an Access database, but I'm getting the above error message followed by 'Syntax error in ORDER BY clause.'
Code:
Sub LookIntoAccess()
'This macro opens the Database From Excess.accdb database, creates and runs an SQL query.
'Then, it copies selected fields back in the Excel sheet.
'The code uses late binding, so no reference to external library is required.
'Declaring the necessary variables.
Dim con As Object
Dim rs As Object
Dim AccessFile As String
Dim strTable As String
Dim SQL As String
Dim i As Integer
'Disable screen flickering.
Application.ScreenUpdating = False
'Specify the file path of the accdb file.
AccessFile = "C:\users\tkasparidis1\documents\Database from Excess.accdb;"
'Set the name of the table you want to retrieve the data.
strTable = "Table1"
On Error Resume Next
'Create the ADODB connection object.
Set con = CreateObject("ADODB.connection")
'Check if the object was created.
If Err.Number <> 0 Then
MsgBox "Connection was not created!", vbCritical, "Connection Error"
Exit Sub
End If
On Error GoTo 0
'Open the connection.
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
AccessFile
'Create the SQL statement to retrieve the data from table.
'Get the necessary information for the Process Order
SQL = "SELECT Material, Description, Qty, Unit, Amount, Currency, Batch " & _
" FROM " & strTable & " WHERE Process Order = Calculation_SelectedProcessOrder " & _
" ORDER BY Material "
Debug.Print SQL
On Error Resume Next
'Create the ADODB recordset object.
Set rs = CreateObject("ADODB.Recordset")
'Check if the object was created.
If Err.Number <> 0 Then
'Error! Release the objects and exit.
Set rs = Nothing
Set con = Nothing
'Display an error message to the user.
MsgBox "Recordset was not created!", vbCritical, "Recordset Error"
Exit Sub
End If
On Error GoTo 0
'Set thee cursor location.
rs.CursorLocation = 3 'adUseClient on early binding
rs.CursorType = 1 'adOpenKeyset on early binding
'Open the recordset.
rs.Open SQL, con
'Check if the recordet is empty.
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
con.Close
'Release the objects.
Set rs = Nothing
Set con = Nothing
'Enable the screen.
Application.ScreenUpdating = True
Exit Sub
End If
'Copy the recordset headers.
For i = 0 To rs.Fields.Count - 1
Sheets("CALCULATION").Cells(1, i + 1) = rs.Fields(i).Name
Next i
'Write the query values in the sheet.
Sheets("CALCULATION").Range(Calculation_ProcessingSKU).CopyFromRecordset rs
'Close the recordet and the connection.
rs.Close
con.Close
'Release the objects.
Set rs = Nothing
Set con = Nothing
'Adjust the columns' width.
Sheets("CALCULATION").Columns("A:E").AutoFit
'Enable the screen.
Application.ScreenUpdating = True
'Inform the user that the macro was executed successfully.
MsgBox "The Data were successfully retrieved from the '" & strTable & "' table!", vbInformation, "Done"
End Sub
Calculation_SelectedProcessOrder is a Public variable created on another part of the code. I'm guessing there is something wrong with the syntax of my query but this is the first time I'm doing one so I have no idea what it is I'm doing wrong. Any help will be greatly appreciated.