VBA use column values as parameter in SQL query

[tndinnc]

I have a spreadsheet that pulls data from a SQL database. Then I have a button that lets the user FTP the sheet to send the manipulated spreadsheet to a customer. What I need to do is take one column of the spreadsheet, take the unique values in it and either use it as where clause in a sql update back to the database or an insert statement. Either will work.

Sample Column Values
123456A
123455A
123445A
123345A
122345A

sSTRSQL = update tblOrders where OrderNumber in ('123456A','123455A','123445A','123345A','122345A')

or do a loop and insert each order number into a tracking table in the database

Can someone point me in the right direction on how to do that?

 

[par60056]

It somewhat depends on the database that you are using. In general you will want to loop over the data and build the insert or update statements.

Use the macro recorder to record doing a simple query or insert. This will give you the information you need to create the connection string to talk to the database. Then look at the DAO objects. In the macro editor go to the Tools->references menu. In the Available References find "Microsoft DAO" I currently have several versions available. I would recommend using the one with the highest number.

To get an idea what objects are available in the DAO library, type "Dim D as DAO." and you will see the available objects. You will probably need to look at DBEngine, Database, Connection, RecordSet, QueryDef and maybe Workspace. These will allow you to connect to the database, issue commands and get results.