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.
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Watch MrExcel Video

Forum statistics

Threads
1,114,476
Messages
5,548,259
Members
410,825
Latest member
Dave12
Top