I have an access database, and at the moment, I'm busy writing code to copy some of the queries I have. The problem is that I need to access the SQL of these queries so that I can automatically update the selection criteria and the like (replace existing selection criteria with new criteria).
Does anyone know how to do this. When replying, please bear in mind that I am not a developer, so a simple solution would be appreciated.
open your query.
try the box top-left of the main tool bar: it offers you run-view, design-view, ...and SQL view.
watch out for string criteria when building your SQL from the copy paste e.g.
strSQL = "SELECT blah FROM blah WHERE TextField = Forms!MyForm.MyBox;" 'wont work if MyBox is text
you must modify it to get the string criteria in quotes like:
strSQL = "SELECT blah FROM blah WHERE TextField = '" & Forms!MyForm.MyBox & "';"
i never tried reading the existing SQL from code: i write it fresh from scratch each time.
dim dbs as database
dim qdf as QueryDef
set dbs = currentdb 'not AS
if isobject(dbs.QueryDefs("theName")) then 'missing ")"
set qdf = dbs.CreateQueryDef("theName")
qdf.SQL = mySQLstring
sorry can't help with the read.
glad you managed to work around my two typos - fixed above