Reading from a text file in vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I would like to send queries straight to the Oracle database that I am
connecting to. I do not want Access to process it since they seems to be a
lot faster when I use a pass through query. However, I want to use variables
in the SQL statements which I dont believe pass through queries will work.
So I want to use vba code to send the queries through and then output the
results to excel. I was thinking that maybe the best way to do this would be
to have text files of the queries that I can read into string variables.
Some of the queries are pretty unwieldy and I dont want them hardcoded. So
my question is: How can I read in a full text file into a string variable?

Mark
 
There is an easier way. All stored queries in Access are stored as SQL
strings. The query builder you see only converts it to a GUI so it is easier
to use than writing SQL by hand. When you save a query from the query
builder, it converts it back to the SQL. So all you need to do is read the
SQL string from the Querdef into a string variable and do whatever you want
with it.

strSQL = CurrentBb.QueryDefs("MyQueryName").SQL
 
This has nothing to do with DAO or ADO. Queries, regardless of type, are
saved this way. As long as the query you define works with ADO, this
technique will work. It is much easier than trying to maintain and read a
text file.
 
Back
Top