Modifying pass-through query from VBA 2

[alanst881]

Hello everyone.

I have a front-end Access database connected via ODBC to a SQL Server 7 backend.

In Access, I have a simple pass-through query pointing to the first of over 2500 tables in SQL Server:

"SELECT * FROM table_1"

I have saved the pass-through query as "qryTable"

Without getting into the reasons why (they are fairly complicated and beyond the scope of this post), I need to find a way to change the SQL statement of qryTable from within VBA.

For example, changing the SQL Statement from "SELECT * FROM table_1" to "SELECT * FROM table_2". As I said, the reason behind the need is fairly complicated. I can't just simply create a separate query for each table.

So, if anyone knows how to change the SQL of a saved query from VBA, I'd love to hear about it.

If I cannot dynamically change the definition of a saved query from VBA, how about tips on creating a permanent pass-through query from VBA.

Many thanks!

-- Alan

 

[bab]

I think what you need is pretty straightforward:

Dim sSQL As String
Dim dbs As Database
Dim qdf As QueryDef

sSQL = "SELECT * FROM table_2"
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("qryTable&quot
With qdf
.SQL = sSQL
.Close
End With

Set qdf = Nothing
Set dbs = Nothing

Hope that helps!

 

[alanst881]

Excellent! Thank you very much. I appreciate the speedy and concise reply.

-- Alan