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;Pw d=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

I feel your pain... I have run into the same thing. I solved this by passing
a concatenated string. So instead of having a varialbe called sql you end up
with 3 varaibles like

strSelect = "SELECT * "
strFrom = "FROM Table1 "
strWhere = "Field1 = 'MyValue'"

rs.Open strSelect & strFrom & strWhere , cn

Which oddly enough works...

HTH

"Tod" wrote:

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;Pw d=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

How about a stored procedure in the database itself?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Tod" wrote in message
ups.com...
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;Pw d=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