[AccessD] PassThrough (Creation/ Modification) Issues

SD,

<<Does anybody know why the CreatePassThroughQry sub
does not work and why it does not show up in the
database container?>>

 Read the first paragraph of the link you posted (the one that says
"Important"<g>).

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Sad Der
Sent: Tuesday, August 02, 2005 2:26 AM
To: Acces User Group
Subject: [AccessD] PassThrough (Creation/ Modification) Issues


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