Access Front End Stored Procedure Advice

[Neil]

Hello,
I have a SQL Server 2000 database with an Access 97 front end.
I want to run stored procedures, (not nessessarily ones which return
records either - action type queries for adding new records etc), from
access and retreive some result from the procedure which would be used
programatically in VBA so that users don't get nasty looking ODBC
errors. Could anyone suggest what I need to do? I'm aware I could use
a pass through query to run my stored procedure all the time if i need
a result returned, but this seems overkill for the sake of getting
some form of return value from the procedure, surely there must be a
more sensible way? Or is there a good website which tutors this kind
of stuff? I've trawelled the net and I can't see anything which will
help me so far and feel sure that this is such a normal thing to need
to do...

Also, I'm a bit scared of ADO (probably irrationally) - just used DAO
so far and feel nervy and unsure about how (or why) I should be using
ADO, so DAO stuff would be nice! (or some nice pointers or examples on
ADO to calm my nerves!)

Cheers,
Neil

[Rich P]

I have the same setup you have at my workplace. ADO is the way to use
stored procedures from Access (the only way that I know anyways). Com
ADO is what you use for Access97. To use Com ADO (as opposed to
ADO.Net) make sure you have mdac2.5 and mdac2.6 installed on the
workstations that would use your application. You can get mdac2.5 and
mdac2.6 at msdn. Actually, I just checked and can't find it (them). I
believe mdac 2.5 was the last version that contained the jet engine.
Someone please correct me if I'm wrong. But I had problems if I did not
also load mdac 2.6. The thing is that this is all legacy stuff,
Access97 is sort of legacy (although robust as heck and still kicking
like no tommorrow) so support will probably be limited and hard to find
(try a google search). Anyway, assuming you have all this loaded, here
is the code for using com ADO. First, make sure you have a reference to
Microsoft ActiveX data Objects 2.6 (or higher) in Tools/References (from
any code module). Here's the code:

Dim cmd As New ADODB.Command
Dim RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset
...
'here we return records

cmd.ActiveConnection = "Provider=SQLOLEDB;Data
Source=yourSqlServerName;" _
& "Initial Catalog=yourSqlDB;UID=SA;PWD=tiger;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "stp_yourStoredProc"
sDate = #1/1/04# 'date parameters - assume you have params
eDate = #1/31/04# 'called @bDate and @eDate defined in Sqls
cmd.Parameters("@bDate").Value = sDate
cmd.Parameters("@eDate").Value = eDate
Set RSado = cmd.Execute
Set RSdao = CurrentDB.OpenRecordset("tbl1")
Do While Not RSado.EOF
'RSdao and RSado have same fields
RSdao.AddNew
For i=0 to RSado.Fields.Count-1: RSdao(i)=RSado(i):Next
RSdao.Update
RSado.MoveNext
Loop

To execute an Update or Insert Query (stored Procedure) just do all of
the above except instead of
Set RSado = cmd.Execute
it is just
cmd.Execute
and you are done.

AS for returning records, I have experimented with a sql Server method
called OpenRowset to return bulk data directly into Access97 instead of
looping, but that only works at the server, couldn't get it to work on a
workstation (remotely). You need ADO.Net for non-looping stuff (very
sweet and way more efficient than com ADO except that you have to write
your own .Net dlls to work it with Acc97 - been there - not too bad but
not as easy as com ado).
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!