asked on

Excel 2007: Passing value from Excel to Access query

Hi, I use the following code to execute an Access query with from Excel. Then an input box pop up require user to enter a criteria for the query. I'd like to automatically pass a value from Excel to the query criteria. The value is stored in cell E5 or named range "Input" on the Excel file. How do I do that? Thanks.

Sub RunAccessQuery()

Dim acObj As Object

Set acObj = CreateObject("Access.Application")
acObj.OpenCurrentDatabase "C:\Documents and Settings\Work\DB.accdb"
acObj.DoCmd.OpenQuery ("qry_01")
acObj.CloseCurrentDatabase
Set acObj = Nothing

End Sub

pteranodon72

JCJG,

Give this code a shot.

' code in Excel
Sub RunAccessQuery()

    Dim acObj As Object 'Access.Application
    Dim db As Object 'DAO.Database
    Dim qdf As Object 'DAO.QueryDef

    Set acObj = CreateObject("Access.Application")
    acObj.OpenCurrentDatabase "C:\Documents and Settings\Work\DB.accdb"
    Set qdf = acObj.DBEngine(0)(0).QueryDefs("qry_01")
    qdf.Parameters(0) = Range("E5")
   
    'execute the action query
    qdf.Execute

    'If you want feedback on the passed parameter, recs affected
    'Debug.Print qdf.Parameters(0), qdf.RecordsAffected

    Set qdf = Nothing
    Set db = Nothing
    acObj.CloseCurrentDatabase
    acObj.Quit 2   'acQuitSaveNone = 2
    Set acObj = Nothing

End Sub

Open in new window

Hope this helps,
pT72

JCJG

ASKER

Hi, I got an error message saying the Access table already exist. I think it is because the query I executed is a make-table query and the existing table needs to be replaced. How to fix it?

In addition, can you help me with a second set of code to run an Access macro instead?

Thanks!

JCJG

ASKER

Actually I have one more question. I'd like expand the criteria from cell E5 to E5:E6. Is it doable? Thanks.

ASKER CERTIFIED SOLUTION

pteranodon72

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JCJG

ASKER

Thanks for your help!