[thelist] RE: 11th hour plea: ASP/Access snapshot hell

John Schomp jschomp at ghnonline.com
Tue May 8 09:03:27 CDT 2001

Hmmm.  Querydef isn't supported in ADO, which is now the default library for
Access 2000.  I'm no ADO expert, but I did find this code in a quick Google
search.  Perhaps you can use it?

Sub ModifyQuery(strDBPath As String, _
                strQryName As String, _
                strSQL As String)
   Dim catDB As ADOX.Catalog
   Dim cmd   As ADODB.Command

   Set catDB = New ADOX.Catalog
   ' Open the Catalog object.
   catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & strDBPath

   Set cmd = New ADODB.Command
   ' Get the query from the Procedures collection.
   Set cmd = catDB.Procedures(strQryName).Command

   ' Update the query's SQL statement.
   cmd.CommandText = strSQL

   'Save the updated query.
   Set catDB.Procedures(strQryName).Command = cmd

   Set catDB = Nothing
End Sub

>> Subject: [thelist] RE: 11th hour plea: ASP/Access snapshot hell
>> My first post.  Hope I don't bung this up.
>> I've run into this situation before.  Hopefully, your report is based on
>> query.  If not, make it so.  Then, using the QueryDef object, edit the
>> of the underlying query before opening the report.
>> Good Luck,
>Thanks, I'll need it.
>I've never used a QueryDef object in VBScript before.  Lucky for me, my
Access 2000 help doesn't include any entries on QueryDef, much less
examples.  I can't make heads or tails of the VB examples in MSDN Visual
Studio Library 6a. I can't find >any mention of "QueryDef" in my VBScript
books. *groan*
>Yes, my report is built on a query.  All I need to add is:
>"WHERE Cover_EmployeeName="&Session("UserLogin")
>1) Is there a simple snippet of code I can add?
>2) is the underscore in Cover_EmployeeName going to throw off my ASP page?
Access generated the name.  I'm not sure about >hunting back through the
report to find all the underscore fields.

>If you can point me to any commented code samples, I would greately
appreciate it.  Thanks for your help so far.

More information about the thelist mailing list