By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,490 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,490 IT Pros & Developers. It's quick & easy.

Access 2003 ADP

P: 3
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
Feb 24 '06 #1
Share this Question
Share on Google+
3 Replies


P: 4
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
Feb 24 '06 #2

P: 3
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
Feb 27 '06 #3

P: 4
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) :-)
Mar 6 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.