Help with calling Access Database for data?
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:
Code:
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!
