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!
