Parameter Query in ADP? (1 Viewer)

[vidus]

I have been unable to find the answer to this question, nobody seems to know so perhaps its not possible?

I switched my database over to adp format so it runs on sql, I had a parameter query that I would like to use on this new database. In regular Access, it's just [] in the criteria field to prompt an input box. But what is the code for adp??

Hoping someone on this forum has some experience with adp?

 

[pbaldy]

You'll find that not many of us use ADP's (though many use SQL Server). I don't personally like them. Your query became a view or stored procedure, which run on the server. SQL Server can't communicate directly with the user like Access can, so there is no equivalent to the [Enter blah] method of getting parameters. You'll need to gather the input via a form and pass it to the stored procedure.

 

[vidus]

You'll find that not many of us use ADP's (though many use SQL Server). I don't personally like them. Your query became a view or stored procedure, which run on the server. SQL Server can't communicate directly with the user like Access can, so there is no equivalent to the [Enter blah] method of getting parameters. You'll need to gather the input via a form and pass it to the stored procedure.

Ah, I see. Thanks for the explanation. Would you recommend using accdb with linked tables instead? Will parameter queries such as [] function the same that way?

 

[pbaldy]

That is the configuration that MS itself now recommends. Don't get me wrong, very smart people that I respect use ADP's, and love them, so I'm not saying they're "bad". I just personally don't like them.

An Access query with [] would perform the same. For slower performing queries, you can change them to stored procedures or views to get the performance advantages of SQL Server (but of course you'd then lose the [] method). I should note than most of us don't use the [] method anyway. You have a lot more control when you have the user enter their input on a form.

 

[vidus]

That is the configuration that MS itself now recommends. Don't get me wrong, very smart people that I respect use ADP's, and love them, so I'm not saying they're "bad". I just personally don't like them.

An Access query with [] would perform the same. For slower performing queries, you can change them to stored procedures or views to get the performance advantages of SQL Server (but of course you'd then lose the [] method). I should note than most of us don't use the [] method anyway. You have a lot more control when you have the user enter their input on a form.

Thanks pbaldy. I think I will stick with adp.. ill have to find another method for this query I guess.

 

[SQL_Hell]

Hi

I have a little experience in this, albiet a while ago now.
Firstly you will need to convert your query into a stored procedure or sql server UDF (user defined function).
There is a designer for creating stored procs, views and functions in ADP if you're not happy with the raw SQL code right now. It will be very similar to the normal query designer

Typical sql server parameterised query looks like this in sql code

SELECT * FROM Customers WHERE CustomerID = @CustomerID

@CustomerID being the parameter, if you run a bit of code that like you should see the prompt come up asking for the value of @CustomerID

If you plan to use the query as the data source for a form or report, then there is an "input parameters" box in the data tab of the forms properties page, you can use this to parse parameters from your form to your query.


Hope this helps

 

[vidus]

Hi

I have a little experience in this, albiet a while ago now.
Firstly you will need to convert your query into a stored procedure or sql server UDF (user defined function).
There is a designer for creating stored procs, views and functions in ADP if you're not happy with the raw SQL code right now. It will be very similar to the normal query designer

Typical sql server parameterised query looks like this in sql code

SELECT * FROM Customers WHERE CustomerID = @CustomerID

@CustomerID being the parameter, if you run a bit of code that like you should see the prompt come up asking for the value of @CustomerID

If you plan to use the query as the data source for a form or report, then there is an "input parameters" box in the data tab of the forms properties page, you can use this to parse parameters from your form to your query.


Hope this helps

thanks for the post! this looks promising. i dont have any knowledge on creating stored procedure or where to start.. any links or info you can provide would be helpful to get me started?

 

[SQL_Hell]

There are lots of guides, here's a good one

http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

It really depends what you want to do with this parameterised query, if it is a forms data source then you will come into issues using a stored procedure, a user defined function is a better choice. But this only applies to ADP.

If you just want to run the query manually, go to the stored procedure pane in the database window and paste in your query but replace the square brackets with a '@' parameter as shown above

Do you have sql server management studio installed also? you should use this for creating views, stored procs ect moving forward

If you need any more info, could you supply more information about your environment and what you want to achieve.

 

[vidus]

There are lots of guides, here's a good one

http://www.sql-server-performance.com/articles/dba/stored_procedures_basics_p1.aspx

It really depends what you want to do with this parameterised query, if it is a forms data source then you will come into issues using a stored procedure, a user defined function is a better choice. But this only applies to ADP.

If you just want to run the query manually, go to the stored procedure pane in the database window and paste in your query but replace the square brackets with a '@' parameter as shown above

Do you have sql server management studio installed also? you should use this for creating views, stored procs ect moving forward

If you need any more info, could you supply more information about your environment and what you want to achieve.

Its an adp running on sqlexpress 2005. I just want to have a button on a form that when pressed will query a certain table with the parameters the user enters, which will be a 4 digit number. That number wont be used for anything other than returning the appropriate records.

Do you create a store procedure in sql server management studio or in access? and how do you call for it with a command button once its created?

 

[vidus]

Little update. I have managed (I think) to create a store procedure, and created a command button to run it. It works this way:

    SELECT Job FROM Materials WHERE Job = '4444'

But now I want to make it throw an input box. This does not work:

SELECT Job FROM Materials WHERE Job = @Job

 

[SQL_Hell]

The stored procedure code should look like this in sql mode

ALTER PROCEDURE dbo.Test
(@Job int)
AS 
 
SELECT Job FROM Materials WHERE Job = @Job

Here is a good guide

http://www.aspfree.com/c/a/Microsoft-Access/Working-with-Stored-Procedures-in-an-MS-Access-Project/

 

[vidus]

The stored procedure code should look like this in sql mode

ALTER PROCEDURE dbo.Test
(@Job int)
AS 
 
SELECT Job FROM Materials WHERE Job = @Job

Here is a good guide

http://www.aspfree.com/c/a/Microsoft-Access/Working-with-Stored-Procedures-in-an-MS-Access-Project/

Thanks SQLHELL. Works good. Is there anyway of getting rid of the <DEFAULT> and <NULL> dropdown in the input box?