Editing Stored Pass-through Queries via VB 1

[corchard]

I have an existing Pass-through query that I need to edit on the fly using code.
Specifically I need to replace a string in this passthrough query then save it back into the project whereas another Make Table query will access it.

I've tried using CreateQueryDef, but whenever I try to specify the SQL, I get errors from Access because the SQL contains non-Access understood SQL code like '||'s, etc.

Does anybody know how I can (through code) take a somewhat foreign SQL string and save it as a pass-through query in Access 97 for later use?

Cheers,

corchard


"Illegitimis non carborundum"
(don't let the b@st@rds get you down)

 

[dhookom]

I just create a generic Pass-Through with the proper connection properties, etc. Then I use a function that uses DAO to change the SQL property of the P-T.

Function ChangeSQL(pstrQuery as String, _
pstrSQL As String) as String
'pstrQuery is the query name
'pstrSQL is the full sql string
'returns the previous SQL value
Dim db as DAO.Database
Dim qd As DAO.QueryDef
Set db = CurrentDb
Set qd = db.QueryDefs(pstrQuery)
ChangeSQL = qd.SQL
qd.SQL = pstrSQL
Set qd = Nothing
Set db = Nothing
End Function

Duane
MS Access MVP

 

[LNBruno]

Yo, Duane!

Been banging my head trying to figure out how to dynamically change a stored query for reporting purposes and this code worked perfectly!

Star for the post!

< M!ke >