Web Development

Ask something
Discussion Locked

Excel VBA Querying Access DB

0 helpful
3 replies

by kolsson

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

My client has an MS Access database and wants to import 87 fields of data from it into an Excel worksheet. The only filter is the cycle number (an integer). Access field names are long so it needs multiple queries to get it all.

The twist: We presume we have dumb users, so we will provide each user with a workbook and when they open the workbook there should be only a button that brings up an input dialog box (for cycle number) so zero user training is needed. (Workbook is read only so theyare forced to do a SaveAs.) I have the VBA working fine for a single query but how can I code it to enter the cycle number just once for the multiple queries needed to collect all the data?

3 replies

by dklippert

You can delcare the Cycle number as Public.

"Public Optional. Used to declare procedures that are available to all other procedures in all modules."

Include in Declarations section of module, something like:
.
Public CycleNumber As Integer

by kolsson

My projects typically being small, I declare all variables as Public unless a particular problem arises.
Additional symptom: I get General ODBC error messages anytime I tried to replace the ? in the WHERE statement with a variable.
[i.e. WHERE (ChargingBathData.Cycle_Count = ?)]

This discussion is closed to new replies.