[AccessD] PassThrough (Creation/ Modification) Issues

The most likely reason is that the code doesn't comply with the data source.
Remember, a pass-thru isn't necessarily Jet SQL -- it's whatever the source
requires. 

Susan H. 

Hi group,

I've created a passthrough query. I searched the Microsoft MSDN site and
found several nice pieces of code (see below). I modified them a bit for my
personal use.

I noticed the following:
- The 'ModifyQuery' works like a charm. It alters the PTQ like it should and
displays the records accordingly.
- The 'CreatePassThroughQry'created a PTQ but I do not see it in the
database container. Where is it? When I try to use the newly created PTQ as
a recordsource it does not display any records.
(I want to use the CreatePassThroughQry sub in my multiuser environment!)

Does anybody know why the CreatePassThroughQry sub does not work and why it
does not show up in the database container? The newly created query does
show when I loop the ADOX views!?

Url:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/htm
l/adocreateq.asp
Regards,

SD
-------------------------------------
Public 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
   catDB.ActiveConnection = CurrentProject.Connection
   
   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

Public Function CreatePassThroughQry(strSQL As String, _
                         strODBCConnect As String) As String
   Dim catDB   As ADOX.Catalog
   Dim cmd     As ADODB.Command
   Dim varProp As Variant
   Dim strQryName As String
   Dim oGeninf As cGenInf
   Set oGeninf = New cGenInf
   
   Set catDB = New ADOX.Catalog
   ' Open the Catalog object.
   catDB.ActiveConnection = CurrentProject.Connection
   

   Set cmd = New ADODB.Command
   ' Define SQL statement for query and set provider-specific
   ' properties for query type and ODBC connection string.
   With cmd
      .ActiveConnection = catDB.ActiveConnection
      .CommandText = strSQL
      .Properties("Jet OLEDB:ODBC Pass-Through
Statement") = True
      .Properties("Jet OLEDB:Pass Through Query Connect String") =
strODBCConnect
   End With
   
   strQryName = oGeninf.UserName & Format(Now(),
"_hhmmss") & "_tmp"
   ' Name and save query to Procedures collection.
   catDB.Procedures.Append strQryName, cmd
   CreatePassThroughQry = strQryName
   Set catDB = Nothing
End Function

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com