runtime error '-2147217900 #80040e14#'

WGKaspar

New Member
Joined
Oct 16, 2019
Messages
2
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.'

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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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