Calling a stored procedure in SQl Server 2k

[Guest_imported]

I'm trying to call a stored procedure on my SQLSVR2K database from an Access 97 front-end using ODBCDirect.

Two date parameters (start, end) are collected on an Access
form. I then want to pass these paramaters off to a stored procedure on the SQLSVR2K DB and return the results in an Access report.

I've tried a myriad of ways but am open to any suggestions at this point... Anyone?!?
thanks

Oh yeah linking tables is not practical bcz the SQLSRV table with the data contains over 3 million rows... too slow.

 

[cmmrfrds]

First of all use a direct ADO connection instead of ODBC. Here is what the code might look like.

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset
Dim param1 As Parameter, param2 As Parameter, param3 As Parameter, param4 As Parameter
begDate = #10/1/2001#
endDate = #10/31/2001#

' Connect
What a connection string looks like to sql server.
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=pubs;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cnn.ConnectionString = connString
cnn.Open connString
'- tek-tips puts in a little face for the letters L O L. So, the little face is actually those letters without space.

Set cmd.ActiveConnection = cnn
''Set param1 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param1
param1.Value = begDate
Set param2 = cmd.CreateParameter("Input", adDBDate, adParamInput)
cmd.Parameters.Append param2
param2.Value = endDate

' Set up a command object for the stored procedure.
cmd.CommandText = "dbo.sp_employeeRevenueProgress"
cmd.CommandType = adCmdStoredProc

' Execute command to run stored procedure
Set rst = cmd.Execute

You can now work with the recordset. The problem with Access 97 is that you can't equate the rst to the FORM recordset (there is none) since it is not available until Access 2000.

 

[Guest_imported]

Thanks cmms
But 1. Ive never used ADO 2. Im not a 'natural' VB programmer (more like a hack really ) 3. I guess I need to install the correct OLE DB driver on my WINNT system 4. That means a 2... no... 5 yr wait while our MIS dept process my 'request' (personally i'd rather have my teeth pulled without Novacaine!).

Was hoping to get an ODBC solution if possible since I can already successfully open the connection to my DB on SQLSVR. Just need the workarounds for passing the start/end date parameters to the SQLSVR stored proc and getting the results into Access (ACC97).

Does {Call myStoreProc (stDate,edDate)} mean anything? I can run this Call from the Query Analyser in SQLSVR. I thought about using it as SQL text in the Access VB code but not sure if a remote call like this will do the trick.
thanks

 

[cmmrfrds]

You may already have the mdac libraries used for ADO. Anyway it is a free download from Microsoft. You will need to use either DAO or ADO to execute the stored procedure from the access program.

The syntax is to use the execute method off the connection object in ADO.
Dim cn as new adodb.connection

cn.execute "dbo.mystoredprocedure" parm1, parm2

To check your MDAC providers try this on your NT pc.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the sql server provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.

If you found the sql server provider it is already installed on your PC.

Maybe somebody else can provide the syntax etc. using DAO and ODBC since I don't use that combination.

 

[Guest_imported]

Thanks.
Its the ADO references that have me *stumped* since the DAO syntax, while similar, is quite different.

Im not even sure if ADO was supported in ACC97, and if it was, what version.
cheers

 

[cmmrfrds]

I have used ADO version 2.6 in Access 97, which is also MDAC 2.6. I have used ADO to create recordsets in code but not as the data source for a report.

I am not sure if you can use a stored procedure as the record source for a Report in Access 97. In Access 2000 it is not a problem, but I believe 97 is missing some objects that make this possible.

The work around may be to store the records returned from the SP in an Access table and use the table as the record source for the report.

 

[Guest_imported]

Thnks cmmr. I checked the reference libraries and there is one for Microsoft ADO 2.6 (msado15.dll)... Is this the one you mean? Also, there is a similar one "Microsoft ADO Recordset 2.6 Library" (msador15.dll) in the list. Im [just] guessing both of these would be needed for ADO objects to work in ACC97?

Oh yeah I did think about doing the recordset to Access table transfer but its a speed thing. I've been told by a tech before that the SQLSVR SP to Access report linkage is possible, but its also likely he hasnt worked with any SQLSVR2K/ACC97 solutions.

Going the ADO way do you know any good online references? As I said Ive never used ADO until now.

 

[cmmrfrds]

This library should be good. Microsoft ADO 2.6 (msado15.dll)...

Unfortunately, Access 97 does not have a way to make a stored procedure the recordsource of a Form or Report. This is most easily done in an Access 2000 project (adp) where you can connect directly to the sql server database and have the stored procedure directly as the record source. It is a little more difficult in an Access 2000 MDB, where the stored procedure can be equated to the recordset of a Form but not a Report - no recordset object. You can execute a stored procedure in access 97 but the data would need to come back to a recordset. At this point, you could add the data to an access table, but there is no recordset object on an access 97 Form or Report. I am afraid your only option is to move the data to a local table that can be the record source of the Form or Report.

Do you have an option to upgrade to Access 2000? If so, this is the best way to go when working with an sql server database.

Another option is to store the results of the stored procedure in another sql server table. Then, link the table to your access 97 mdb. You can, if necessary, link the table through vba code. I am assuming the new table would be relatively small.

The steps. (steps 4 & 5 would be the loop in the code, steps 1,2 & 3 are to set up initially)
1. create the new table from the SP.
2. link the new table to the mdb.
3. use the table as the recordsource of the report.
4. In the OnOpen event of the Report repeat steps 1 & 2 only in vba code.
5. drop the new sql server table at end of Report or delete all the records - either would work but drop would be more efficient on a larger table.

this link has lots of good information on DAO to ADO.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp