Here is an example where you read data from a stored procedure in Sql
Server using com based ADO (regular ADO). Say the stored procedure in
Sql Server looks like this:
Create Procedure [stp_Tbl1Data]
@startDate datetime,
@endDate datetime
As
Select * from tbl1 Where Datefld Between @startDate and @endDate
Go
This procedure's name is stp_Tbl1Data and has 2 parameters -- @startDate
and @endDate. Here is how you call it from Access with the parameters:
Dim cmd As New ADODB.Command, RSado As New ADODB.Recordset
Dim RSdao As DAO.Recordset, i As Integer
cmd.ActiveConne ction = "Provider=SQLOL EDB;Data Source =
SqlServer1;Init ial Catalog=Databas e1;UID=sa;PWD=' "
cmd.CommandTime out = 600 'seconds - 10 minutes
cmd.CommandType = adCmdStoredProc
cmd.CommanText = "stp_Tbl1Da ta"
cmd.Parameters( "@startDate").V alue = #1/1/04#
cmd.Parameters( "@endDate").Val ue = #1/31/04#
Set RSado = cmd.Execute
Set RSdao = DB.OpenRecordse t("AccessTbl1 ")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 to RSdao.Fields.Co unt - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
Loop
RSdao.Close
RSado.Close
cmd.ActiveConne ction.Close
This will retrieve thousands of records from Sql Server in a matter of
seconds. The flexibility of a routine like this is relative to your
proficiency with Sql Server and ADO. The only thing faster than this
would be ADO.Net where you don't even have to use a loop to stuff the
data into Access - just use an Insert statement on a data Adapter (sorry
for the jargon -- just FYI).
Rich
*** Sent via Developersdex
http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
