[AccessD] PassThrough (Creation/ Modification) Issues

I get the idea of what you are trying to do but I'm not sure what you
are trying to accomplish.  

I have a very similar routine "ModifyQuery", that I use to alter a
"Template" or "Standard" PTQ.  I can dynamically create a sql statement
in its correct syntax Oracle, TSQL, or JET then alter the appropriate
PTQ to insert the correct sql to execute.  

At that point you can call your report, ListBox, Forms, Queries..
whatever, which then looks at your dynamically altered PTQ.  But again I
am not sure what you are trying to do.  If you are trying to be neat and
clean, I'd suggest keeping a Template or Standard PTQ as it will retain
all the connection info in it once built then all you need to do is
alter the SQL Statemtent and you are good to go.  But again I'm guessing
on your end result.

Rich

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Susan Harkins
Sent: Tuesday, August 02, 2005 10:40 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com