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.ActiveConnection = "Provider=SQLOLEDB;Data Source =
SqlServer1;Initial Catalog=Database1;UID=sa;PWD='"
cmd.CommandTimeout = 600 'seconds - 10 minutes
cmd.CommandType = adCmdStoredProc
cmd.CommanText = "stp_Tbl1Data"
cmd.Parameters("@startDate").Value = #1/1/04#
cmd.Parameters("@endDate").Value = #1/31/04#
Set RSado = cmd.Execute
Set RSdao = DB.OpenRecordset("AccessTbl1")
DoEvents
Do While Not RSado.EOF
RSdao.AddNew
For i = 0 to RSdao.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
Loop
RSdao.Close
RSado.Close
cmd.ActiveConnection.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!
