Passing variables from Access to SQL

[mike franklin]

Hi,
I have created a view. However this view needs a specific variable that a user has inputted in Access. The variable I need to pass into the first view is a DocketID..Similar to a StoreID. I then must run the second view, which also depends on the same DocketID. Finally I must run a final query that takes the result from the second view, which takes the results of the first view.
Basically, I want to pass the variable from my VBA application to the SQL statement. I have been reading, and it seems that like in VB I must declare a local variable in a DECLARE statement. Is that right?
How would I pass the variable that can be held in the VBA application ex. DocketId = 220 and pass that 220 to my SQL statement which is running is SQL 7.0. Most of the statement will be running in SQL, not the VBA application.

[Lazarus Long]

did you try upsizing the access database? or dts in sql?

------------
mike franklin at 4/24/01 11:16:32 AM


Hi,
I have created a view. However this view needs a specific variable that a user has inputted in Access. The variable I need to pass into the first view is a DocketID..Similar to a StoreID. I then must run the second view, which also depends on the same DocketID. Finally I must run a final query that takes the result from the second view, which takes the results of the first view.
Basically, I want to pass the variable from my VBA application to the SQL statement. I have been reading, and it seems that like in VB I must declare a local variable in a DECLARE statement. Is that right?
How would I pass the variable that can be held in the VBA application ex. DocketId = 220 and pass that 220 to my SQL statement which is running is SQL 7.0. Most of the statement will be running in SQL, not the VBA application.

[mike franklin]

upsizing? don't understand

I have used DTS, but for larger queries. This is a realitively small query, and I need to take what the user has inputed in a text box, and run a few nested queries with that Docket ID here is my first view for example:

SELECT DSTA.DocketID,
GTTA.DistCode, Min(GTTA.Offset)
AS MinOfOffset
FROM [dbo].DocketStoreTA DSTA INNER JOIN
[dbo].TAGeogTypes GTTA ON
DSTA.TAID = GTTA.TAID
GROUP BY DSTA.DocketID, GTTA.DistCode

I need a WHERE statement to hold the variable paseed from ACCESS


ex. WHERE DSTA.DockeID = [VARIABLE GOES HERE]


------------
Lazarus Long at 4/24/01 11:28:00 AM


did you try upsizing the access database? or dts in sql?

------------
mike franklin at 4/24/01 11:16:32 AM


Hi,
I have created a view. However this view needs a specific variable that a user has inputted in Access. The variable I need to pass into the first view is a DocketID..Similar to a StoreID. I then must run the second view, which also depends on the same DocketID. Finally I must run a final query that takes the result from the second view, which takes the results of the first view.
Basically, I want to pass the variable from my VBA application to the SQL statement. I have been reading, and it seems that like in VBA I must declare a local variable in a DECLARE statement. Is that right?
How would I pass the variable that can be held in the VBA application ex. DocketId = 220 and pass that 220 to my SQL statement which is running is SQL 7.0. Most of the statement will be running in SQL, not the VBA application.

[Hiku]

Mike,
YOu need to write a store procedure something like...
create procedure test
@dockeId int as
begin
SELECT DSTA.DocketID,
GTTA.DistCode, Min(GTTA.Offset)
AS MinOfOffset
FROM [dbo].DocketStoreTA DSTA INNER JOIN
[dbo].TAGeogTypes GTTA ON
DSTA.TAID = GTTA.TAID
where DSTA.DockeID = @dockeID
GROUP BY DSTA.DocketID, GTTA.DistCode
end

then to execute "exec test 123". You might have to use ASP for
passing the variable to SQL. There might be other routes but I use
ASP.
Hth


------------
mike franklin at 4/24/01 11:41:36 AM

upsizing? don't understand

I have used DTS, but for larger queries. This is a realitively small query, and I need to take what the user has inputed in a text box, and run a few nested queries with that Docket ID here is my first view for example:

SELECT DSTA.DocketID,
GTTA.DistCode, Min(GTTA.Offset)
AS MinOfOffset
FROM [dbo].DocketStoreTA DSTA INNER JOIN
[dbo].TAGeogTypes GTTA ON
DSTA.TAID = GTTA.TAID
GROUP BY DSTA.DocketID, GTTA.DistCode

I need a WHERE statement to hold the variable paseed from ACCESS


ex. WHERE DSTA.DockeID = [VARIABLE GOES HERE]


------------
Lazarus Long at 4/24/01 11:28:00 AM


did you try upsizing the access database? or dts in sql?

------------
mike franklin at 4/24/01 11:16:32 AM


Hi,
I have created a view. However this view needs a specific variable that a user has inputted in Access. The variable I need to pass into the first view is a DocketID..Similar to a StoreID. I then must run the second view, which also depends on the same DocketID. Finally I must run a final query that takes the result from the second view, which takes the results of the first view.
Basically, I want to pass the variable from my VBA application to the SQL statement. I have been reading, and it seems that like in VBA I must declare a local variable in a DECLARE statement. Is that right?
How would I pass the variable that can be held in the VBA application ex. DocketId = 220 and pass that 220 to my SQL statement which is running is SQL 7.0. Most of the statement will be running in SQL, not the VBA application.

[mike franklin]

thanks...it worked


------------
mike franklin at 4/24/01 11:16:32 AM


Hi,
I have created a view. However this view needs a specific variable that a user has inputted in Access. The variable I need to pass into the first view is a DocketID..Similar to a StoreID. I then must run the second view, which also depends on the same DocketID. Finally I must run a final query that takes the result from the second view, which takes the results of the first view.
Basically, I want to pass the variable from my VBA application to the SQL statement. I have been reading, and it seems that like in VB I must declare a local variable in a DECLARE statement. Is that right?
How would I pass the variable that can be held in the VBA application ex. DocketId = 220 and pass that 220 to my SQL statement which is running is SQL 7.0. Most of the statement will be running in SQL, not the VBA application.