Access 2003 ADP

[Zahed]

Using Microsoft 2003 (ADP)connecting to SQL 2000. I am trying to create a simple form in Access and pass parameters to a SQL query.

tried but dosen't work :

SELECT Port_Code, Port_Name
FROM dbo.Port_Code
WHERE [forms!Select_Form![Name]]]

pls help

many thanks

[Luc Swennen]

First, create a pass-through query, no sql in it, and save it with name (eg. MyPTQuery)

you can fill in the Sql via code:

Sub UDS_Define_Pass_Through_Query()

Dim dbs As Database
Dim qdf As QueryDef
Dim strSql As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("MyPTQuery")

' Assuming Select_Form is open and <> design
strSql = _
"SELECT Port_Code, Port_Name " & _
"FROM dbo.Port_Code " & _
"WHERE Port_Name ='" & Forms!Select_Form![Name] & "'"
' single quotes because parameter is a string

qdf.SQL = strSql

Set qdf = Nothing
Set dbs = Nothing

End Sub

[Zahed]

Sorry to be confused. What do you mean by a pass-through query

First, create a pass-through query, no sql in it, and save it with name (eg. MyPTQuery)

you can fill in the Sql via code:

Sub UDS_Define_Pass_Through_Query()

Dim dbs As Database
Dim qdf As QueryDef
Dim strSql As String

Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("MyPTQuery")

' Assuming Select_Form is open and <> design
strSql = _
"SELECT Port_Code, Port_Name " & _
"FROM dbo.Port_Code " & _
"WHERE Port_Name ='" & Forms!Select_Form![Name] & "'"
' single quotes because parameter is a string

qdf.SQL = strSql

Set qdf = Nothing
Set dbs = Nothing

End Sub

[Luc Swennen]

Ok, let's start from an earlier point...

The Record Source of your form can be a table (linked or local) or a query.

The source of a query can be one or more tables and/or one or more queries.

In this case the Record Source is a query on a linked table since the database of that table is not Access but SQL 2000.

A pass-through query can access this data directly, without the need to link the table.

How:
Create a new query
Choose design view
Choose Close when Access ask you which table(s) / Queries
Open Menu Option "Query"
Choose SQL Specific
Choose Pass-Through
=> as from now, the Design View is not longer available and the screen is switched into SQL View

save it with name (eg. MyPTQuery) :-)