Get Query String from Access
 

Hi Ken

Firstly, I'm no great shakes when it comes to ADO. Hence, I don't know
if this is possible thru ADO. However, I do know it's straight-forward
using DAO to connect to the Access Db. An example of how to return the
SQL of a give query is below:

Sub return_sql()
'set a ref to DAO 3.X - or use late binding
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = OpenDatabase("C:\MyDatabase.mdb")
Set qdf = db.QueryDefs("MyQuery")
Debug.Print qdf.Sql
End Sub

This was a very simple example. I presume if you used this to return
your SQL, you could then utilise this for onward use in the server db.

Do you think this could be of use?

Best regards

Richard


Ken Valenti wrote:
I am using ADO to connect to a SQL database & run queries in Excel 2003.

The SQL code is written in Access, which I then copy to Excel.

I would like to be able to change the queries in Access and have Excel run
the new query without having to manually copy / paste the SQL code to Excel.

Is there a way to get the SQL code for an Access query using code, so I
don't have to copy / paste after a modification to the query has been made?

I would simply use Excel to run the Access query directly, but Access uses
ODBC (which I don't want to setup on everybody's machine).

Thanks

Get Query String from Access
 

Thanks - I used your code and it works perfect.

Still couldn't figure out how to do it in ADO.

"RichardSchollar" wrote:

Hi Ken

Firstly, I'm no great shakes when it comes to ADO. Hence, I don't know
if this is possible thru ADO. However, I do know it's straight-forward
using DAO to connect to the Access Db. An example of how to return the
SQL of a give query is below:

Sub return_sql()
'set a ref to DAO 3.X - or use late binding
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = OpenDatabase("C:\MyDatabase.mdb")
Set qdf = db.QueryDefs("MyQuery")
Debug.Print qdf.Sql
End Sub

This was a very simple example. I presume if you used this to return
your SQL, you could then utilise this for onward use in the server db.

Do you think this could be of use?

Best regards

Richard


Ken Valenti wrote:
I am using ADO to connect to a SQL database & run queries in Excel 2003.

The SQL code is written in Access, which I then copy to Excel.

I would like to be able to change the queries in Access and have Excel run
the new query without having to manually copy / paste the SQL code to Excel.

Is there a way to get the SQL code for an Access query using code, so I
don't have to copy / paste after a modification to the query has been made?

I would simply use Excel to run the Access query directly, but Access uses
ODBC (which I don't want to setup on everybody's machine).

Thanks