Big Honkin' Query

[Tod]

I normally use SQL queries in my VBA code like this:


Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

cn.Open "Driver={SQL
Server};Server=ServerName;Database=DBNAME;Uid=x;Pwd=y"

sql = "SELECT * FROM Table1 WHERE Field1 = 'MyValue'"

rs.Open sql, cn


It passes the string to the datebase and returns the results as a
recordset. But now I have a query so large that it can not easily be
contained as a string in my VBA. Is there a way to refer to a text file
where it is kept, or some other idea?

tod

[Smithochris]

When I have used queries in excel, I open up Microsoft Query (Data --> Import External Data --> New Database Query). From here it looks a lot like an access query. I've set up the query here how I want it, and then save it. After that is done, I can record a new macro and run this query. It then writes the whole sql statement out.

This probably doesn't help you out, because it's still not easily contained in VBA. It works for me though because if I want to change the query I can open it in Microsoft Query and adjust, instead of looking at a ton of sql statements in VBA. There probably is a better way to do this. I'm not sure how to refer to a txt file.

This reminds me of a problem I'm having right now...I have a number saved in a variable in VBA. I want it to be in my sql statement, but I can't seem to get it to work. Do you, by any chance, know how to do this? The sql looks like this:

WHERE CO_NO = '773'

But I want it to be WHERE CO_NO = variable that number is stored in...