Access Project - Get SQL using VBA

[john1958]

Hi I have an Access project and I need to get the SQL from the queries using code.
I have already managed to get the list of each query in the database - now I need to obtain the SQ behins the query

Code to obtain Queries in database

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
' Search for open AccessObject objects in AllQueries collection.
For Each obj In dbs.AllViews
strList = strList & obj.Name & ","
Next obj

then I tried the following to get the SQL behind the query

strQuery = Me.cboQuery
Set qryDef = Application.CurrentDb.QueryDefs(strSQuery)
Me.txtSQL = qryDef.SQL

but I get an error Object Variable or With block variable not set

Any ideas?

John

 

[WayneRyan]

John,

You need to traverse the QueryDefs collection.
Then you can use the .SQL property and retrieve the SQL.

Wayne

 

[john1958]

Thanks Wayne but the problem I seem to have is that with an Access Project, it does not seem to accept the code eg the following code still gives the error Object Variable with block not set

Dim qryDef as QueryDef

For Each qryDef In Application.CurrentDb.QueryDefs 'Error occurs here

if .....

endif
next qryDef

Any ideas

John

 

[Dennisk]

if its an Access .adp file then there is no such object as a query as Access projects use SQL stored procedures or views, so that may your problem.

 

[john1958]

if its an Access .adp file then there is no such object as a query as Access projects use SQL stored procedures or views, so that may your problem.

I think you are right - does anyone know how to get the SQL of a view. What I am trying to do is obtain the SQL of User written queries and run via code - this way I can increase the timeout setting for SQL

Thanks John

 

[allan57]

Try the following code will display the SQL view of every query in the database:

Dim dbs As Database
Dim qdfLoop As QueryDef

On Error Resume Next

Set dbs = CurrentDb

dbs.QueryDefs.Refresh

For Each qdfLoop In dbs.QueryDefs

MsgBox qdfLoop.SQL

Next qdfLoop

dbs.Close
Set dbs = Nothing

 

[tehNellie]

Depends on what the backend database is that the project file links to.

You shouldn't be sending native SQL to the server anyway, it is more efficient to use a view or storedprocedure. If the database is configured correctly your project won't have permission to query the tables directly anyway and certainly shouldn't have permission to query the necessary tables to obtain the information you're looking for.

Your first port of call should probably be to the DBA of the database you link to.

 

[john1958]

Hi Allan

Still drops out with the same error at the dbs.QueryDefs.Refresh

I was wondering if an Access project (adp file) has an object Views - with the possibility of getting at the SQL of any View

Many thanks for your help

John

Try the following code will display the SQL view of every query in the database:

Dim dbs As Database
Dim qdfLoop As QueryDef

On Error Resume Next

Set dbs = CurrentDb

dbs.QueryDefs.Refresh

For Each qdfLoop In dbs.QueryDefs

MsgBox qdfLoop.SQL

Next qdfLoop

dbs.Close
Set dbs = Nothing

 

[allan57]

How odd it does work not, I must admit that I'v only used this method in access 97, will try later versions this weekend and get back to you.

 

[boblarson]

How odd it does work not, I must admit that I'v only used this method in access 97, will try later versions this weekend and get back to you.

It works in an MDB file but an ADP is different. I just tried it in an ADP file too and it doesn't work for me either.

 

[Moniker]

Instead of the objects, have you tried using the MSysObjects table in Access? It's a system table that holds the names of each object (along with other stuff about each object) and you can get each query name that way. The query to get each name would be like this:

SELECT Name FROM MSysObjects WHERE Type=5;

That would return a recordset where the name field contains the names of every query in your project. From there, using the .SQL property (as mentioned before) should do the trick.

I don't really work with ADP files too much, but that at least avoids the direct object references, which seems to be the problem.

 

[tehNellie]

But with an ADP, you aren't storing queries locally within Access, you're accessing the Views/Storedprocedures on the database server that you're linking to so again this probably wont work.

If you're linking to a SQL server you can query sysobjects or information_schema to get the view/SP names and extract the native SQL, but arguably your .adp application shouldn't have that level of access to the database to begin with.

 

[john1958]

At last I have found the solution - MANY thanks to all contributors above for putting me onto the right track. I have used the sysObjects and sysComments to obtain first the id then the text (SQL). The code is below, again many thanks

strSQL = "SELECT id FROM SysObjects Where Name='" & cboQuery & "'" 'cboQuery has already been populated with the names

Call Open_RS1(strSQLDb, strDBName, strSQL) 'routine to open recordset

intID = RS1.Fields("id")


Call Close_RS1 'routine to close recordset

strSQL = "SELECT text FROM SysComments Where id= " & intID 'gets SQL

Call Open_RS1(strSQLDb, strDBName, strSQL)

strSQL = RS1.Fields("Text")


Call Close_RS1

'the text returns with something like CREATE VIEW of SELECT..then the SQL of Query I removed the prefix to get the SQL to use later

intPos = InStr(1, strSQL, "SELECT")
strSQL = Right(strSQL, Len(strSQL) - (intPos - 1))

 

[tehNellie]

I'd still question why you want to make your application less efficient by doing this. You have to pass more network traffic to your database and you force SQL server to compile your query every time you submit it in this format whereas a view/SP is precompiled. (again your application shouldn't be able to query sysobjects to begin with, you're connecting using a high privilege account).

 

[john1958]

It is because I have a problem with the database timing out and the solution to this is to set the extended timeout period in code. Unfortunately the person using the database wants to write some bespoke queries but is unable/unwilling to copy the SQL to a text box for the query to be run from code. This method allows the views to be listed then selected then run via code instead of from the Access views page. Unless you know of a neater solution which I would be very grateful to hear

John

 

[golaat]

Hi I have an Access project and I need to get the SQL from the queries using code.
I have already managed to get the list of each query in the database - now I need to obtain the SQ behins the query

To get the Query text, try the following:

Dim rsCommandText As New ADODB.Recordset
Dim SQLStmt as String
rsCommandText.Open "exec sp_helptext dbo.MyQueryObject", CurrentProject.Connection
SQLStmt = rsCommandText.GetString(adClipString, , , vbCrLf)