Passing a variable to an Excel query

[BajanPOET]

I have an SQL query that selects all the Requisition data out of two Access tables Requisition and Items. I need to isolate a single set of requisition data at a time and send that information into a preformatted Excel spreadsheet.

SELECT Items.Description, Items.Qty, Items.EstPrice, Items.Supplier
FROM `O:\GAIA Requisition`.Items Items, `O:\GAIA Requisition`.Requisition Requisition
WHERE Requisition.ReqNo = Items.ReqNo AND ((Requisition.ReqNo=?))


This SQL query brings up all of the requisition data I have stored in my database. It has been set up with a parameter, so that the user can enter the required requisition number. How do I store the current requisition number in an open Access record and pass it on to this query so that I can isolate data for one requisition at a time and show it in a preformatted Excel spreadsheet?

GOD is in charge, though men may say, "Not so!

 

[BajanPOET]

I unchecked the Referesh data on file open on both my queries, but I still get the same dialog box.


GOD is in charge, though men may say, "Not so!

 

[SkipVought]

You still have a querytable with that option checked.

Look in your Names DropDown and select each name that says something like "Query from..."

For each QT you have selected, check the Data Range Properties.

Skip,

_[red][/red]

 

[BajanPOET]

I checked all the queries and made sure they didn't have that option checked, but I'm still seeing the dialog box.


GOD is in charge, though men may say, "Not so!

 

[SkipVought]

Exactly what does the dialog box say?

Skip,

_[red][/red]

 

[BajanPOET]

O:\Purchase Orders

This workbook contains queries to external data that refresh automatically.

Queries are used to import external data to Excel, but harmful queries can be used to access confidential information or write information back to a database.

If you trust the source of this workbook, you can enable automatic query refresh. If you disable automatic query refresh, you can later refresh queries manually, if you are satisfied that the queries are safe.

*Enable automatic refresh* *Disable automatic refresh*
(buttons)

That's the exact wording of the dialog box

GOD is in charge, though men may say, "Not so!

 

[SkipVought]

run this procedure

Sub test()
    Dim ws As Worksheet, qt As QueryTable
    For Each ws In Worksheets
        For Each qt In ws.QueryTables
            qt.RefreshOnFileOpen = False
        Next
    Next
End Sub

Skip,

_[red][/red]

 

[BajanPOET]

I've taken some of you guys' advice and split the database so that it would be easier to distribute as a client/server app... now I have to do over my queries that are in Excel so that they point to the new database O:\GAIA Requisition_be.mdb.

I am now getting an ODBC error when I'm trying to refresh the queries... The offending code is ActiveWorkbook.RefreshAll in the Workbook_Open() procedure.

What could be the problem?

What? Who? ME????

 

[SkipVought]

Running the previously posted code would at least identify WHICH qt had a problem.

How did you change the path?

I'd suggest macro recording EDITING on of your queries -- post the code.

Skip,

_[red][/red]

 

[BajanPOET]

Hey Skip... I couldn't figure out how to stop the ODBC error even after deleting all my queries and re-entering them... so I just deleted the RefreshAll command. It's only one dialog box... and only one user will be using that Excel speadsheet anyway, so I'm just going to tell her to make sure she allows automatic refresh and leave it at that.

What? Who? ME????