Coding Pass through queries with "On the Fly" input.

[TomPose]

I am trying to make a pass through querie on the fly. I want to select a series of variable by either drop down box or typing in the filter in an input box and then make the SQL string that defines the pass through.

The code snippet at the bottom works to some extent. But, when I open it, it will not "PassThrough" how do i make a pass through where I can select any input as a criteria?

In the properties box of a previously set up Working PasThrough, the "ODBC Connect Str" is formated as : ODBC;DSN=Title;SERVER=servername;pwd=password and the code is the same format that is generated here with this code - (just the pass through issue is sticking)

I want the "EnterNumberHere" to represent user input. I know that when running a regular query it will work, don't know if it would work in a pass through so if a variable would be better suited I would need to know how to get the input into the variable first.



Using:
Private Sub Command1_Click()
Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT DISTINCT table1.no, table1.name " & _
"FROM d_base.table1 " & _
"WHERE table1.no= '" & "EnterNumberHere" & "' "
Set qdf = dbs.CreateQueryDef("TestQry", strSQL)



Any help is greatly appreciated.

 

[randy700]

How about...

Private Sub Command1_Click()
Dim dbs As Database, qdf As QueryDef, strSQL As String
[COLOR=red]Dim intUserInput As Integer
intUserInput = InputBox("Enter number here")[/color]
Set dbs = CurrentDb
strSQL = "SELECT DISTINCT table1.no,  table1.name " & _
    "FROM d_base.table1 " & _
    "WHERE table1.no= " & [COLOR=red]intUserInput[/color]
Set qdf = dbs.CreateQueryDef("TestQry", strSQL)

Randy

 

[jrbarnett]

Randy's post demonstrates how to filter on specific user input. To actually save your query as a pass through query to the database requires a bit of extra work.

To turn a normal query into a pass through query, you need to set the connection property, so just combine Randy's technique and mine:

Note that I have just preceded the objects with "DAO" to remove ambiguity between DAO and ADO objects of the same name.

Private Sub Command1_Click()
  Dim dbs As DAO.Database, qdf As DAO.QueryDef, strSQL As String

  Set dbs = CurrentDb
  strSQL = "SELECT DISTINCT table1.no,  table1.name " & _
    "FROM d_base.table1 " & _
    "WHERE table1.no= '" & "EnterNumberHere" & "' "
  Set qdf = dbs.CreateQueryDef("TestQry", strSQL)
  ' Set the connection string for pass through purposes

  qdf.Connect = "ODBC;DSN=Title;SERVER=servername;pwd=password"

  ' Append the query to the querydefs collection
  ' note will need to delete original first otherwise you will
  ' get an "Item already exists in this collection" error
  
  On Error Resume Next
  dbs.QueryDefs.Delete "TestQry"

  ' Now add the new one to the collection
  dbs.QueryDefs.Append qdf

  ' and clear the variables
  Set dbs = Nothing

End Sub

John

 

[TomPose]

This is Great!! with one caveat...

When the pass through is created, it generates the following:

SELECT DISTINCT table1.no, table1.name FROM [d_base].table1 WHERE table1.no= '123xxx'


The brackets around d_base, after the FROM, are creating errors - ODBC Call Failed, invalid tablename.

Any suggestions...? It is so close...

Thanks,

 

[jrbarnett]

What's your back end database platform?

John

 

[jrbarnett]

Ah, think I've got it. If you're using SQL Server, the object naming convention is:

server.database.owner.object

What you've done is use database.object, so it assumes d_base is the owner of the table.
Assuming you've got the default table owner of dbo, change the SQL to

[d_base].dbo.table1

and you should be fine.

John