pass parameter from access to sql server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

pass parameter from access to sql server

Author

Topic

pgill
Starting Member

17 Posts

Posted - 2007-07-15 : 15:49:09

I am using an MS Access DB as a front end to SQL Server 2005. I have several parameterized queries on the MS Access front end that takes user input. I have created several stored procedures and queries in SQL Server 2005 that correlate to these MS Access queries. I have established an ODBC connection to SQL Server. I can created a pass through query on the Access front end to call the SQL Server queries with hard coded parameters. My issue is how to allow the user to enter the parameters to the queries and pass them to SQL Server? This is quite easy to do in an Access DB but I have not been able to pass the parameters through an input form on the Access front end. Anyone familiar with how to do this?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-15 : 18:09:19

I guess you could link the tables in SQL Server to Access, and use a query as you normally would do...

Peter Larsson
Helsingborg, Sweden

pgill
Starting Member

17 Posts

Posted - 2007-07-15 : 20:05:11

Peter - Thank you for your reply. I want to be able to send the user input to the SQL Server query in order to take advantage of the SQL Server security. This seems like it should be a simple thing to do but so far it has perplexed me.
My SQL server query call on the Access front end, through a parameterized query looks like this:

EXEC MyParamQuery JSmith

With the "JSmith" being the hard coded input parameter. What I want to do is create a form that will use a text box control for the parameter input. So if the text box name is MyTextInput.text I would pass this to the parameterized query:

EXEC MyParamQuery MyTextInput (This doesn't work).
I also tried MyTextInput.Text.

Any ideas why this won't work? I keep getting "ODBC call failed". I know the connection string is OK because it will work with the hard coded parameter.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-07-16 : 03:58:03

We have done this with Access 97 and SQL Server 7. I would imagine the principles haven't changed much.

What we do is basically this (it's a long time since I've done this so please bear with me)

Create a pass through query called say MyQuery - it doesn't really matter what the SQL in it is
Create a form for user input, in that you redefine the SQL in MyQuery to be what you need so for example


Private Sub ButtonClickEvent()

dim d as database
dim q as querydef

set d = currentdb
set q = d.querydefs("MyQuery")
d.SQL = "Exec MySproc '" & Forms!MyForm!UserInput.Value & "'"

I think that's all there is of it. Hopefully it should point you in the right direction at least

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

pgill
Starting Member

17 Posts

Posted - 2007-07-16 : 05:10:04

Steve - Thank you so much. I'll give it a try and let you know how it goes. Again, thanks.

pgill
Starting Member

17 Posts

Posted - 2007-07-16 : 12:32:30

Steve - Thanks for pointing me in the right direction with QueryDef.
Here is what worked for me:

Private Sub Command11_Enter()
Dim d As DATABASE
Dim q As QueryDef
Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection
cnn.Open "ODBC;DATABASE=MyServer_MyDB_SQLServer_BE;UID=pword;PWD=;DSN =MyServer_SQLServer_MyDB"

Set d = CurrentDb
Set q = d.QueryDefs("Query11")
q.SQL = "Exec QueryFNameLName '" & Forms!Form11!txtCaseNo.Value & "'"
DoCmd.OpenQuery "Query11"
End Sub

The passthrough query looked like this:

EXEC QueryFNameLName ''

When I ran the form and input the value the passthrough query paramater was then filled in (QueryFNameLName 'JDoe') and it ran with the desired result.

Thanks for your help. Now what I need to do is to fill in a form with the query results. Do I have to use a RecordSet for this?

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-07-17 : 03:00:04

You should be able to set the name of your pass-through query as the datasource for the form. You know what the field names will be and can then make use of them where you need to or you can programatically generate a recordset and use that

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

pgill
Starting Member

17 Posts

Posted - 2007-07-17 : 07:29:17

Steve - That makes sense, I don't know why I didn't think of that. Thanks for all your help. You made my job a litter easier.

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2007-07-18 : 05:15:39

Glad to be able to help

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

pootle_flump

1064 Posts

Posted - 2007-07-20 : 19:01:30

quote:
Originally posted by elwoos

You should be able to set the name of your pass-through query as the datasource for the form. You know what the field names will be and can then make use of them where you need to or you can programatically generate a recordset and use that

OP - remember that the former is read only and the latter, with some additional code, can be updateable.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources