Passing array as paramter to SQL query

[sumdumgai]

Is it possible to pass an array as a parameter to an SQL query to retrieve data from an Access database? I may have one to many values that need to used as a parameter to then select records from the database. If possible, can someone please provide an example of the query?

Or, is there an alternative way to select records from the database using more than one argument value.

Thanks.

 

[gemma-the-husky]

can you elaborate?

do you have an array that comprises the records you want to select, or something like that?

 

[sumdumgai]

Thanks. I'll be building an array in Excel (or a temporary table in Access) that contains one to many values. These values will be used as arguments to select records from the database which will then be exported to Excel. Example. A field in a customer (Access) table is 'zip' code, which is part of the customer's address. I want to select all of the customers who are in the list of zip codes that are passed, either as an array or as a temporary table, as the parameter. If only one zip code, then I want all customers in that zip code. If more than one zip code, then I want all customers in all those zip codes.

 

[Pat Hartman]

You can't pass an array as an argument. You can build the SQL in VBA dynamically and use an In() clause to provide the criteria. If it is only a few items, that will be fine but there are limits on SQL string sizes and if you may run into those, just use a temp table.

 

[sumdumgai]

Thanks. I'll probably be initiating the arguments (i.e., building the array or string) in Excel. I'll then import that data into Access, building the temp table. I've never passed a table as argument to a query. Can you do that directly or do you have to build the SQL statements using VBA and data in the temp table? If you can do it directly (pass the table as criteria) can you please give me a syntax example?

 

[Pat Hartman]

Add the temp table to the query with an inner join.

I'm not sure why you would have your data in Access, exit to Excel to create a filter, and then bring the filter back into Access to filter the data??? Sounds unnecessarily complicated.

 

[sumdumgai]

The filtering data (e.g. a list of zip codes) is the user's input to an ad hoc report request. The data that will be filtered, hundreds of thousands of records, already exists in the Access database. I don't know how many zip codes will be passed in any given query. Could be one to hundreds of them, so I can't see a good way to keep the filtering data in Access. Does it make sense now, or can you suggest another way? Thanks.

 

[Pat Hartman]

You can create temp tables and use those. This allows the user to retain a previously selected list or delete it and start from scratch. You coul use the same technique to maintain multiple sets by adding another column to the table. In this case, you would have the user specify set "a" or set "f" and include that criteria in the query that does the selection. You would be selecting set "x" from the temp table and joining that query to the main table to get just the required records. The set is an argument that will be supplied at runtime so you only have a single query. It will select a set of zipcodes based on a form field.

Temp tables can be defined as permanent tables with rows being added or deleted or can be recreated each time via make-table queries. The end result is the same - database bloat so you will probably need to set the FE to compact on close to clear the bloat so it doesn't become a problem.

 

[sumdumgai]

Good ideas. Thanks.