Write to an MS Access Query from VBA

[SimonFinn]

Hi

I have a SQL statment in a string variable (strSQL) in Access/VBA and i am trying to overwrite a query stored in MS Access Querys section (query name = qryInterests).

How could i do this?

Cheers Si

 

[RobCPA]

Try this:

Sub ReplaceSQL()

dim db as database
dim qdf as querydef
dim strsql as string

set db = currentdb
set qdf = db.querydefs("qryInterests&quot
strSQL = "Select * from tbl;"

qdf.sql = strSQL

End Sub


The qdf line sets the query definition to qryInterests
Then, the qdf.sql simply replaces the sql string in qryInterests to the variable strSQL

Rob

 

[SimonFinn]

Thanks Rob

Thats worked a treat!

Cheers Si

 

[Omadmax]

I used the eaxact code listed by robCPA and I am getting an error that the Database type is a user defined type.

What setup is needed so that Access 2000 recognizing the Database type.

 

[RobCPA]

I think that Access 2000 defaults to using ADO. The code that I wrote was in DAO.

Change the code like this:

dim db as DAO.database
dim qdf as DAO.querydef

Also, make sure that a reference is set to the Microsoft DAO 3.51 Object Library (you can set it the 3.6 library instead). You can find the references when opening a VBA module and choosing Tools-> References, then scroll down until you find the MS DAO 3.51 Object Library.


Rob