Create/Delete access queries from VB code

  • Thread starter Thread starter nitaarvind
  • Start date Start date
N

nitaarvind

I have been facing a situation here. There are some queries in my
Access DB that magically disappear after its used once. Any subsequent
use of the application gives the error "query should have at least one
destination field" and all that shows in the respective query is
"select ;" . I have been trying to get around this problem by writing
VB code to drop the query in Access and recreate the query after every
use. Is it possible?

Regards,

Nita
 
Ayon kay (e-mail address removed):
I have been facing a situation here. There are some queries in my
Access DB that magically disappear after its used once. Any subsequent
use of the application gives the error "query should have at least one
destination field" and all that shows in the respective query is
"select ;" . I have been trying to get around this problem by writing
VB code to drop the query in Access and recreate the query after every
use. Is it possible?

Regards,

Nita
Click to expand...

yes it is possible..

here is sample code how to create a query
Dim mydb As Database
Dim MyQ As QueryDef
Dim sqlstr As String

Set mydb = CurrentDb
Set MyQ = mydb.CreateQueryDef("testing")

sqlstr = "DELETE * FROM T_SJIS13Interim"
MyQ.SQL = sqlstr
MyQ.Execute

mydb.Close
MyQ.Close


to delete a specific query use this
mydb.QueryDefs.delete "testing"


hth
 
Back
Top