• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

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
0
JCJG
Asked:
JCJG
  • 3
  • 2
1 Solution
 
pteranodon72Commented:
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
0
 
JCJGAuthor Commented:
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!
0
 
JCJGAuthor Commented:
Actually I have one more question.  I'd like expand the criteria from cell E5 to E5:E6.  Is it doable?  Thanks.
0
 
pteranodon72Commented:
JCJG,
You'll need to delete the table (if it exists) from the tabledefs collection:
' 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 db = acObj.DBEngine(0)(0)

    'skip over table deletion if table does not exist
    On Error Resume Next

    'delete the old table
    db.TableDefs.Delete "YOUR_TABLE_NAME_HERE"
    db.TableDefs.Refresh 'update the collection
    On Error Goto 0 'resume breaking on errors

    Set qdf = db.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



For your question on macros, the DoCmd object that you used in the old code has a RunMacro method:

acObj.DoCmd.RunMacro "macroname"

For expanding the criteria of the query, it involves modification of the query (adding a second parameter or changing the SQL), so I think it deserves a separate question.

Hope this helps,

pT72
0
 
JCJGAuthor Commented:
Thanks for your help!
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now