PDA

View Full Version : Help with calling Access Database for data?



jsabo
09-15-2015, 04:02 PM
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
09-16-2015, 01:08 AM
i dont know if this resolves the problem, database must be cleary defined:

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