Altering pass-through queries in VBA

[ejsmith]

I'm trying to change pass-through queries using code - but for some reason I can only 'see' the queries I'm trying to alter some of the time - other times I can see them and change their QueryDefs("qryName&quot.Sql property - which is the real goal here. Is there something I'm missing - shouldn't I be able to manipulate pass-through queries just as I would a normal Access query?
I also tried refering to the query using
Application.DBEngine(0).Databases(0).QueryDefs("qryname&quot but that also gave inconsistent results.
Here's what I'm attempting:

strSQL = "Select * From dbo.ASQLServer2000Table"

'Delete "qryTemp" if it exists
For Each qdf In Application.DBEngine(0).Databases(0).QueryDefs
If qdf.Name = "qryTemp" Then
Application.DBEngine(0).Databases(0).QueryDefs.Delete (qdf.Name)
End If
Next qdf

'create a temp passthrough query from an existing passthrough - this is the only way I could tink to do it...
DoCmd.CopyObject , "qryTemp", acQuery, "qryPassThroughTemplateSave" 'qryPassThroughTemplateSave is a passthrough template

'add the strSQL to the passthrough we just created
Application.DBEngine(0).Databases(0).QueryDefs("qryTemp&quot.SQL = strSQL
'since I need to make a table, create a qry to put it into a table.
DoCmd.RunSQL ("Select * INTO tbl01A030_AllDataForTopCategories_MD FROM qryTemp;&quot

Sorry about the spacing for the above...
I get errors saying that "qryTemp" is not found at the line where I look to add the .sql code.

Any help would be greatly appreciated - I'm definately missing something!

 

[cmmrfrds]

It looks to me that you could do what you need by setting up an ADO connection to sql server and send your sql through the execute method of the ADO conneciton. Here is an example. I normally set up a function with all the code needed and then call the function.

Function Atest2()
Dim cn As New ADODB.Connection, sql1 As String
Dim rs As New ADODB.Recordset, connString As String
dim recAffected as long
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
'- server name bigtuna
'- database is northwind
'- login in id is sa with no password

cn.ConnectionString = connString

sql1 = "Select * From dbo.ASQLServer2000Table"

Debug.Print sql1

cn.Execute sql1, recAffected

Debug.Print recAffected

cn.Close
Set cn = Nothing

End Function

 

[ejsmith]

Thanks - but I need to save the data returned in a table - and I am not permitted to save it on the SQL server... Do you think I can use your function in an Access make-table query?

 

[cmmrfrds]

No, because the code in the function is a connection to sql server and is not aware of the access tables at this point. Although, sql server has a function called openrowset that allows you to reference the access application from sql server. I don't want to steer you away from the querydef, but I will put in an example of using the openrowset function. This example uses the northwind access database and the northwind sql server database. In the example the access mdb is on my sql server PC, but the syntax should be very close to what you could use.

Public Function rowset()

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
'--- could put variables in at this point for location and database name
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function

 

[ejsmith]

Thanks cmmrfrds, this will come in handy!