Call SQL Stored procedure in Access 2002

  • Thread starter Thread starter Sam
  • Start date Start date
S

Sam

I have a form based on a set of records in a SQL 2000 table and I want to
move the processing to a stored procedure because I am getting ODBC timeouts
at certain distant locations. I created the stored procedure on the SQL
database and now I need to call it and pass it some variables. I cannot
find instructions to do this in the help files.

Can someone help me with an example please? Thanks
 
Some sample code below.
This calls two procedures in a SQL Server Database.
The first just runs a delete routine and didn't need any parameters.
The second sends two parameters up to SQL Server and receives a message back
from SQL Server.

The connection string in this case is based on using a File DSN to hold the
connection information.
Cheers,
Peter
_________________________________________________________________

Dim conSQLHP As New ADODB.Connection
Dim cmdDelete As New ADODB.Command
Dim cmd As New ADODB.Command
Dim param As ADODB.Parameter
Dim paramYr As ADODB.Parameter
Dim paramWk As ADODB.Parameter

'Dim rstSQLHP As ADODB.Recordset 'No need for a recordset for this
routine as no rows are returned
Dim strConn As String
Dim varErrorMsg As Variant
strConn = "FILEDSN=SQLServerDatabase" 'only if using a File DSN
substitute name given in DSN
conSQLHP.ConnectionString = strConn
conSQLHP.Open
cmdDelete.ActiveConnection = conSQLHP
cmd.ActiveConnection = conSQLHP

cmdDelete.CommandText = "procDeleteRoutine" 'name of routine in SQL
Server
cmdDelete.CommandType = adCmdStoredProc
cmdDelete.CommandTimeout = 0 '0 for unlimited time (? time may be in
milliseconds normally)
cmdDelete.Execute
Set cmdDelete = Nothing


cmd.CommandText = "procUpdateRoutine" 'name of another routine in SQL
Server
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("ErrorMsg", adVarChar, adParamOutput,
200)
Set paramYr = cmd.CreateParameter("YrD", adSmallInt, adParamInput)
Set paramWk = cmd.CreateParameter("WkD", adSmallInt, adParamInput)
paramYr.Value = intYear
paramWk.Value = intWeek
cmd.Parameters.Append param
cmd.Parameters.Append paramYr
cmd.Parameters.Append paramWk

'Set rstSQLHP = cmd.Execute
cmd.CommandTimeout = 0 '0 for unlimited time (? time may be in
milliseconds normally)
cmd.Execute

'Set rstSQLHP = Nothing
Set cmd = Nothing
Set conSQLHP = Nothing
Exit Sub
 
Hi Sam

Create a new, empty query, and then change it to a SQL pass-through query
(Query>SQL Specific>Pass-through).

Then, in the SQL text box, type:
exec YourStoredProcName

Now, click View>Properties and fill in the correct ODBC connect string and
save the query.

If the SP takes parameters then you will need to change the SQL string every
time you run the query. You can do this in VBA code. For example:

Public Function ExecuteMySP(Param1 as Integer, Param2 as String)
Dim db as DAO.Database, qdf as DAO.QueryDef
Set db = DBEngone(0)(0)
Set qdf = db.QueryDefs("qryExecuteMySP")
With qdf
.SQL = "exec MySP " & Param1 & ", '" & Param2 & "'"
.Execute
End With
Set qdf = Nothing
Set db = Nothing
End Function

Of course, you can put whatever you like into the SQL string at run-time, so
you could use the same query shell to call a host of different stored
procedures.
 
OK, this is beautiful. I have it all set up with one problem. When I
compile it stops on Dim db as DAO.Database and says dao.database
user-defined type not defined. So close but I am obviously missing
something.

How do I define that? I am using access 2002


Graham Mandeno said:
Hi Sam

Create a new, empty query, and then change it to a SQL pass-through query
(Query>SQL Specific>Pass-through).

Then, in the SQL text box, type:
exec YourStoredProcName

Now, click View>Properties and fill in the correct ODBC connect string and
save the query.

If the SP takes parameters then you will need to change the SQL string every
time you run the query. You can do this in VBA code. For example:

Public Function ExecuteMySP(Param1 as Integer, Param2 as String)
Dim db as DAO.Database, qdf as DAO.QueryDef
Set db = DBEngone(0)(0)
Set qdf = db.QueryDefs("qryExecuteMySP")
With qdf
.SQL = "exec MySP " & Param1 & ", '" & Param2 & "'"
.Execute
End With
Set qdf = Nothing
Set db = Nothing
End Function

Of course, you can put whatever you like into the SQL string at run-time, so
you could use the same query shell to call a host of different stored
procedures.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Sam said:
I have a form based on a set of records in a SQL 2000 table and I want to
move the processing to a stored procedure because I am getting ODBC timeouts
at certain distant locations. I created the stored procedure on the SQL
database and now I need to call it and pass it some variables. I cannot
find instructions to do this in the help files.

Can someone help me with an example please? Thanks
Click to expand...
Click to expand...
 
-----Original Message-----
I have a form based on a set of records in a SQL 2000 table and I want to
move the processing to a stored procedure because I am getting ODBC timeouts
at certain distant locations. I created the stored procedure on the SQL
database and now I need to call it and pass it some variables. I cannot
find instructions to do this in the help files.

Can someone help me with an example please? Thanks


.
Click to expand...
Hi Sam, use online help to lookup 'Create an SQL-specific
query'

Luck
Jonathan
 
Hi Sam

You will need to add a reference to the DAO object library. From the code
window: Tools>References, then find Microsoft DAO 3.x Object Library in the
list and check its box.

Are you using any ADO stuff in your code? If not, then also uncheck the
Microsoft ActiveX Data Objects line.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Sam said:
OK, this is beautiful. I have it all set up with one problem. When I
compile it stops on Dim db as DAO.Database and says dao.database
user-defined type not defined. So close but I am obviously missing
something.

How do I define that? I am using access 2002


Graham Mandeno said:
Hi Sam

Create a new, empty query, and then change it to a SQL pass-through query
(Query>SQL Specific>Pass-through).

Then, in the SQL text box, type:
exec YourStoredProcName

Now, click View>Properties and fill in the correct ODBC connect string and
save the query.

If the SP takes parameters then you will need to change the SQL string every
time you run the query. You can do this in VBA code. For example:

Public Function ExecuteMySP(Param1 as Integer, Param2 as String)
Dim db as DAO.Database, qdf as DAO.QueryDef
Set db = DBEngone(0)(0)
Set qdf = db.QueryDefs("qryExecuteMySP")
With qdf
.SQL = "exec MySP " & Param1 & ", '" & Param2 & "'"
.Execute
End With
Set qdf = Nothing
Set db = Nothing
End Function

Of course, you can put whatever you like into the SQL string at
Click to expand...
run-time,
so
you could use the same query shell to call a host of different stored
procedures.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand


Sam said:
I have a form based on a set of records in a SQL 2000 table and I want to
move the processing to a stored procedure because I am getting ODBC timeouts
at certain distant locations. I created the stored procedure on the SQL
database and now I need to call it and pass it some variables. I cannot
find instructions to do this in the help files.

Can someone help me with an example please? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top