call a stored procedure 1

[jacob94]

Is it possible to run a stored procedure from an Access Project form in VBA with out showing the output to the screen???

I simply want to execute a stored procedure that has no results but can't seem to do this without it displaying to the user...

Any help?

 

[mkov]

Try This.....

'Sets values
Set cnSQL = New ADODB.Connection
Set cmd = New ADODB.Command

'Opens a connection to the SQL server
cnSQL.Open _
"Provider= sqloledb;Driver=SQL Server;" & _
"Server=servername;" & _
"Database=DatabaseName;UID=sa;PWD=********"

'Passes data to a SP for processing. This sp uploads the file just created to their ftp site
With cmd

.ActiveConnection = cnSQL
.CommandText = "spMyStoredProcedure"
.CommandType = adCmdStoredProc
.Execute

'Closes and resets the recordset
cnSQL.Close
Set cmd = Nothing
End With

 

[jacob94]

Do I need to provide a connection string, I am in access project with the ability to just click on the stored procedure...

 

[jacob94]

Also, how would I prevent a time out from occurring. Sometimes this happens when running SP's from access project versus just executing the SP in query analyzer.

 

[mkov]

The connection string is this....

cnSQL.Open _
"Provider= sqloledb;Driver=SQL Server;" & _
"Server=servername;" & _
"Database=DatabaseName;UID=sa;PWD=********"

replace servername with the name of the server
replace databasename with the name of the database on that server
replace sa with whatever user id you are using to connect to the database
replace ******** with the password associated with the user name you are using

If you are having a lot of time outs, look at your sp's and see if there is anything you can do to streamline them. I rarely see timeouts, so I have never tried to find a way to prevent them other than tweaking my sp's so that they run more efficiently.

 

[hwkranger]

You're sort of screwing the pooch unfortunately with an ACCESS connection and using a USP.

Honestly, there is a set limit to how long something can run before it times out with an access connection.

Even if you have timeout set to 999999 there's a max value, I forget what it is...

Ussually what I do is, cn.execute "exec usp_MySpName"

Where cn is the current connection for the database to the backend.

My suggestion to you -- if it's something that takes an extreme amount of time (example: 10 hours...) then you need to schedule a JOB on the server.

Additionally, I suggest you move away from Access and move towards a more friendly front end frame work. (.Net?) Continue to keep an Access FE for reports and what not -- I know people love their Access for reports...

Another thought, if it's a LONG job which exceeds the wait limit for the connection, you could use a TRIGGER instead of a sp. You can insert into a table, on insert run your sp. When the SP is done, have it change a value in the table. Have your app check the value in the table -- OR you can have your DB raise an ERROR to notify that the USP is finished.

This of course is not the prettiest of solutions.....

My suggestion: If it's a time issue, effecting your connection -- You need to invest in examing the performance inhibitors of the process and/or examining the process itself.



Randall Vollen
Meryl Lynch