Help with calling Access Database for data?

[jsabo]

Hello,

I am trying to call an access database from Excel - version 2013. I have the below code but when I try to run it, it gives the error "Unspecified Error" and highlights the connection string:

Sub AccessDBTest2()
'
' Macro4 Macro
'
'
    Dim con         As Object
    Dim rs          As Object
    Dim AccessFile  As String
    Dim strQuery    As String
    Dim i           As Integer
            
    'Disable screen flickering.
    Application.ScreenUpdating = False
    
    'Specify the file path of the accdb file. You can also use the full path of the file like:
    AccessFile = "[redacted]"
    
    'Set the name of the query you want to run adn retrieve the data.
    'strQuery = "VENDOR_SCORECARD"
    strQuery = "SELECT [FiscalYear] & [FiscalMonth] AS YearQTR, SSRM_SELECTED_ORDERS.PO_NUMBER, SSRM_SELECTED_ORDERS.DESCRIPTION, SSRM_SELECTED_ORDERS.Vendor, SSRM_SELECTED_ORDERS.Buyer, Avg(IIf([ssrm_scores].[category]=""COST"",((IIf(IsNull([RESULT]),0,IIf([result]=""POOR"",1,IIf([result]=""AVERAGE"",5,10))))),Null)) AS COST, Avg(IIf([ssrm_scores].[category]=""SCHEDULE"",((IIf(IsNull([RESULT]),0,IIf([result]=""POOR"",1,IIf([result]=""AVERAGE"",5,10))))),Null)) AS " _
& "SCHEDULE, Avg(IIf([ssrm_scores].[CATEGORY]=""QUALITY"" Or [ssrm_scores].[category]=""OTHER"",((IIf(IsNull([RESULT]),0,IIf([result]=""POOR"",1,IIf([result]=""AVERAGE"",5,10))))),Null)) AS QUALITY, Sum(((IIf(IsNull([RESULT]),0,IIf([result]=""POOR"",1,IIf([result]=""AVERAGE"",5,10))))/10*[weight])) AS [Weighted Score] FROM SSRM_SELECTED_ORDERS INNER JOIN SSRM_SCORES ON (SSRM_SELECTED_ORDERS.FiscalMonth = SSRM_SCORES.MONTH_SELECTED) AND (SSRM_SELECTED_ORDERS.FiscalYear = SSRM_SCORES.YEAR_SELECTED) AND (SSRM_SELECTED_ORDERS.po_seqno = SSRM_SCORES.po_seqno) WHERE" _
& " SSRM_SELECTED_ORDERS.PO_NUMBER = 'XXXXXXXXXX' AND YEARQTR = '20151' GROUP BY [FiscalYear] & [FiscalMonth], SSRM_SELECTED_ORDERS.PO_NUMBER, SSRM_SELECTED_ORDERS.DESCRIPTION, SSRM_SELECTED_ORDERS.Vendor, SSRM_SELECTED_ORDERS.Buyer ORDER BY SSRM_SELECTED_ORDERS.PO_NUMBER"
    
    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
    
    On Error Resume Next
    'Create the ADODB recordset object.
    Set rs = CreateObject("ADODB.Recordset")

Any ideas? I am also suspicious of the strQuery string. Thanks for your help!

[mancubus]

i dont know if this resolves the problem, database must be cleary defined:

AccessFile = "P:\MyFolder\MySubFolder1\MySubFolder2\MyAccDB.accdb"