SQL Pass Through Query

[Emmitska]

Hello,

I have the following pass through query:
exec dbo.CDEPress_PointInTime @DistrictCode = '67439', @Date = '2004-12-19'

CDEPress_PointInTime is the name of the stored procedure.
The users are not Access litterate and I would like to know if that would be possible to create a form so they can enter a new DistrictCode (Date would be always the same...)? Once they submit the form, the value would be transmitted to this SQL Server query and executed.

Thank you very much,

Emilie.

 

[OnTheFly]

This is how I run a stored procedure and pass it parameters

dim cmdQ as ADODB.Command
Dim prm as ADODB.Parameter

Set cmdQ=New ADODB.Command

With cmdQ
.ActiveConnection=your connection string
.CommandText="CDEPress_PointInTime"
.CommandType=adCmdStoredProc
End With

Set prm=cmdQ.CreateParameter("DistrictCode", adVarChar, adParamInput, 5 (length of field), Value from User input on Form)

cmdQ.Parameters.Append prm

cmdQ.Execute





Hope this helps.

OnTheFly

 

[dhookom]

You can modify the sql property of the saved pass-through query with DAO code:

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
Set db = CurrentDb
Set qd = db.QueryDefs("qsptYourPT")
strSQL = "exec dbo.CDEPress_PointInTime @DistrictCode = '" _
     & Me.cboDistrictCode & "', @Date = '2004-12-19'"

qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Emmitska]

Hey guys,

I put this in the ActiveConnection String, and it doesn't work:

.ActiveConnection = "ODBC;DSN=CDS;Description=CDS;DATABASE=CDS;Network=DBMSSOCN;Address=Enterprise,1433;Trusted_Connection=Yes"

I used the code given by OnTheFly.

This string is basically what I copied from the properties of the path through query (my stored procedure).

Error message is : Data source name not found and no default driver specified.

So what's up now?

 

[OnTheFly]

This is the syntax I used for an SQL Server Connection

Set ConnQ = New ADODB.Connection
ConnQ.Open "Provider=SQLOLEDB;Data Source=YourSQLServerName;Database=YourDatabaseName Database;Trusted_Connection=yes;"

Thisconnects directly to the server by passing the ODBC Connection.

Hope this helps.

OnTheFly

 

[dhookom]

I'm not sure why all the code regarding connection strings and ODBC etc. If you have a saved Pass-Through query, it should already have a valid connection etc. All you need to do is change the SQL of the saved Pass-Through query.

Why all the code?

What is the desired use of the pass-through? Do you use it as the record source for a form or report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Emmitska]

Well,
Now, when running, it says nothing anymore about the connection, but right after execute, it is over. Do I need to do anything special to have the result showing up on the screen? The thing is that I put some code to have my form hidding the Access window so it looks like an independent application... is this why I do not see the result? And how to do next?

Thank you so much!

 

[OnTheFly]

Not knowing exactly what the stored procedure is doing it is hard to give advise on how to display the results. You can bind a form to a stored procedure or you can bind a form to a query or table that the stored procedure affects.

If the form you are using is already bound you may need to do a requery on the form after the stored procedure runs to get the new results.



Hope this helps.

OnTheFly

 

[Emmitska]

Well

When I am running my stored procedure through Pass Through Query:
exec dbo.CDEPress_PointInTime @DistrictCode = '67439', @Date = '2004-12-19'
I have the immediate result as a table like usually for a regular query, right?

I though I would have the same when puting the part @DistrictCode = '67439' from a Form and executing the procedure from code behind the form.

And what is a requery?

 

[OnTheFly]

If your form is bound to this table/query then you will need to do a requery on the form. This refreshed the form to show any changes that were made from the time it was opened to now. At the end of the code that runs the stored procedure add something like

Forms!YourFormName.Requery

Hope this helps.

OnTheFly

 

[Emmitska]

It doesn't do anything new...

OK, to summarize.
I used your code behind the button "Submit" of my Form. This Form contains one textBox where you can put the District Code. Then behind "Submit", it runs your code and among others, executes cmdQ, right?

Well, it DOES something, in the background I guess, but the result doesn't pop-up on the screen like it did when I didn't use a Form but when I was entering the district code directly in the pass through query (accessing my stored procedure).

So I am not sure I need to requery the stuff. It seems to me that the result is hidden because I hid the Access window to have only the Form visible on the desktop (so that it looks nice and users think they are not IN Access...). So do you think this could be the reason why I do not switch to data view when my result is run?

How would I code, in VBA of my Form, to show the result?

 

[PHV]

With your Form object have you tried
.Requery ?
or .Refresh ?
or .Recalc ?
or .RecordSource = .RecordSource ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhookom]

Emmitska,
There might be some confusion here.

Does the stored procedure return records that you want to display in a form, report, datasheet?

Or, does the stored procedure simply run some procedures that maybe modify some data or do something else without returning any records for the users to view?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[OnTheFly]

What is yoru form bound to?

Hope this helps.

OnTheFly

 

[Emmitska]

I want it in a datasheet.

Like a simple query would return a result table in data view.

 

[dhookom]

Then go back to my first reply and add the code to a command button or whatever on a form. Then run code to open the pass-through query in datasheet view.

Ignore all the ODBC and command parameters etc. It's all good code but not appropriate for what you are attempting to accomplish.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Emmitska]

Ok, I will try this, but can you tell me about this:

Set db = CurrentDb
Set qd = db.QueryDefs("qsptYourPT")

What does it mean?

Thanx

 

[dhookom]

I have commented the code. All it does is to identify your pass-through query in your list of queries and then change its SQL property. You could run this code in the after update event of you District Code combo box.

Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
[green]'set the object "db" to the current mdb field[/green]
Set db = CurrentDb
[green]'set the object "qd" to the pass-through query[/green]
Set qd = db.QueryDefs("qsptYourPT")
[green]'build the new sql string[/green]
strSQL = "exec dbo.CDEPress_PointInTime @DistrictCode = '" _
     & Me.cboDistrictCode & "', @Date = '2004-12-19'"
[green]'set the sql property of your pass-through to the new sql string[/green]
qd.SQL = strSQL
[green]'get rid of these objects[/green]
Set qd = Nothing
Set db = Nothing

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Emmitska]

It blocks with DAO...

 

[dhookom]

You may need to set a reference to the DAO library.

While in the code, select Tools->References and scroll down to find and check "Microsoft DAO 3.x..."

After checking the box, close the references dialog and compile. This code should then work.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]