upload file of values for Access "IN" clause?

[seagreen]

Hi,
What is the best way to use a form to allow an enduser to upload a file of IDs so that they can be used to query the database? In other words, so that they would be part of an "IN" statement like:

Select field1, field2, field3 from table1 where myID IN ('val1', 'val2', 'val3',...'valn')

I would like the file values to replace the val1...valn in the select statement. I would like to do this in MS access if possible. And, if it is possible, does anyone know what the limit is for the number of values?

Alternatively, if this cannot be done by file upload, if I had a form with textboxes for all the values, what would happen if some were left blank?

I'm hoping someone will just let me know if any of these scenarios are feasible, although a point to a link would be welcome too!

Thanks!

 

[gaj104]

Personally i wouldn't take that approach. Id create a temporary table to populate the data with the IDs. Then join this temp table to the main table

SELECT tbl_MAIN.myID, tbl_MAIN.field1, tbl_MAIN.field2, tbl_MAIN.field3
FROM tb_TEMP LEFT JOIN tbl_MAIN ON tb_TEMP.myID = tbl_MAIN.myID

This way you have no restriction on the number of items you choose to populate.

My two cents

 

[xenou]

Just to tag onto the above post, for your end-user you could probably create a macro or write a short procedure that your user can initiate with a button click: first, import the records into a new temp table (or clear out an existing "work" table and import new data into it), then, second, run the query as above using the new table in your join criteria.


Some questions would be:
Is the user going to save a standard file in a standard place? Or will you need to be flexible enough to let the user provide the file location?

Do you have enough control over this to be sure its all "good" data?

Will you need to validate the data first, when you import it, or before you run the query?

Note: an import spec (if you create one) will require the data be "standardized" in the sense of having the same datatypes, number of columns, and so on...

More on creating an import spec:
http://office.microsoft.com/en-us/access/HP051876101033.aspx?pid=CH063648351033
http://www.databasedev.co.uk/text-import-macro.html

Or using a vba macro with TransferText:
http://office.microsoft.com/en-us/access/HP051866541033.aspx?pid=CH062526681033

Hope this helps,
Alex

 

[seagreen]

Personally i wouldn't take that approach. Id create a temporary table to populate the data with the IDs. Then join this temp table to the main table

SELECT tbl_MAIN.myID, tbl_MAIN.field1, tbl_MAIN.field2, tbl_MAIN.field3
FROM tb_TEMP LEFT JOIN tbl_MAIN ON tb_TEMP.myID = tbl_MAIN.myID

This way you have no restriction on the number of items you choose to populate.

My two cents

Thanks Graeme I was thinking of something like that but don't know how to get the data from the end-user into the table. I appreciate that you verified that this is the way to go!

 

[seagreen]

Just to tag onto the above post, for your end-user you could probably create a macro or write a short procedure that your user can initiate with a button click: first, import the records into a new temp table (or clear out an existing "work" table and import new data into it), then, second, run the query as above using the new table in your join criteria.


Some questions would be:
Is the user going to save a standard file in a standard place? Or will you need to be flexible enough to let the user provide the file location?

Do you have enough control over this to be sure its all "good" data?

Will you need to validate the data first, when you import it, or before you run the query?

Note: an import spec (if you create one) will require the data be "standardized" in the sense of having the same datatypes, number of columns, and so on...

More on creating an import spec:
http://office.microsoft.com/en-us/access/HP051876101033.aspx?pid=CH063648351033
http://www.databasedev.co.uk/text-import-macro.html

Or using a vba macro with TransferText:
http://office.microsoft.com/en-us/access/HP051866541033.aspx?pid=CH062526681033

Hope this helps,
Alex

Thanks Alex! You've helped me again. I really appreciate the great thought and ideas you always provide. I will study them and the links you've included and get this done.