Running an Access Parameter Query from Excel (1 Viewer)

[Alansidman]

I am attempting to run a parameter query directly from Excel. There is only one parameter that is supplied in Cell D3 which is a date. The name of the database Excel is attempting to open is in Cell D4.

The code bombs out on the highlighted red line. It compiles with out issue. While the path shows here as a hyperlink, it is not in the code.

I get no error message, just a popup with a big red X

Sub RunParameterQuery()
 
'Step 1: Declare your variables
  Dim MyDatabase As DAO.Database
  Dim MyQueryDef As DAO.QueryDef
  Dim MyRecordset As DAO.Recordset
  Dim MyPath As String
  Dim MyDatabaseName As String
  Dim i As Integer
 
  MyPath = "[URL="file://\\officechihome7.office.adroot.bmogc.net\Userdata7$\asidman\home\CCDM"]\\officechihome7.office.adroot.bmogc.net\Userdata7$\asidman\home\CCDM[/URL] Prod DB files\"
  MyDatabaseName = Range("D4").Value
 
'Step 2: Identify the database and query
[COLOR=red]Set MyDatabase = DBEngine.OpenDatabase(MyPath & MyDatabaseName)[/COLOR]
  Set MyQueryDef = MyDatabase.QueryDefs("Query2")
 
'Step 3: Define the Parameters
  With MyQueryDef
  .Parameters("[Enter Date]") = Range("D3").Value
End With
 
'Step 4: Open the query
  Set MyRecordset = MyQueryDef.OpenRecordset
 
'Step 5: Clear previous contents
  Sheets("Main").Select
  ActiveSheet.Range("A6:K10000").ClearContents
 
'Step 6: Copy the recordset to Excel
  ActiveSheet.Range("A7").CopyFromRecordset MyRecordset
 
'Step 7: Add column heading names to the spreadsheet
  For i = 1 To MyRecordset.Fields.Count
  ActiveSheet.Cells(6, i).Value = MyRecordset.Fields(i - 1).Name
Next i
 
MsgBox "Your Query has been Run"
End Sub

Any thoughts on this. What am I missing?

Running Excel 2010 and Access 2010 on Office XP.

Edit: I forgot to provide the error message when I run a Step through the code (F8)

Runtime 3343 Application-defined or object defined error.

:banghead:

 

[boblarson]

Alan:

Does the file name in the Excel Range have the file extension too?

Also, you can leave off the DBEngine part and just use

OpenDatabase(...etc.)

 

[Alansidman]

Alan:

Does the file name in the Excel Range have the file extension too?

Yes. The extension is included. Will try again without DBEngine and advise.

Thanks
Alan

 

[Alansidman]

Bob;
Removed DBEngine and the error persists.

Alan

 

[boblarson]

Now that I look back on it, I'm not sure if you can open a database like that using a URL. UNC - yes, but as you are using a URL instead, I don't know.

Try opening it as a local with the same code and see.

 

[Alansidman]

No difference. If I understood you correctly, I changed the path to H:\CCDM Prod DB files\ which by the way at this location is the same as My Documents.

Alan

 

[Alansidman]

I wonder if the references would make a difference. Here is what I have referenced.

See attached.

 

[boblarson]

Is this file your code is in an XLS or XLSX file and is the database file MDB or ACCDB? If XLS and ACCDB, it might be because it has no knowledge of that version.

 

[boblarson]

I wonder if the references would make a difference. Here is what I have referenced.

See attached.

If it is an ACCDB file you are connecting to, change your reference to

Microsoft Office 14 Access Database Engine Object Library

from the

Microsoft DAO 3.6 Object Library

reference.

 

[Alansidman]

Thank you Bob. That was it. I swapped out the references, changed the Dim statements to reflect that change and bingo. Some days, you can't see the forest for the trees. BTW. It was an ACCDB file in an .xlsm file

Thanks big time for the help.

Alan