Call a SQL Server Procedure from Access

[chfran]

How do I call a SQL Server Stored Procedure from a form in Access?

Thanks in advance!

chfran

[DFS]

Create a pass-through query to your SQL system, fill in the ODBC info,
(optionally) set ReturnsRecords to No, and send the statement:

EXEC procName(args)

"chfran" <ch****@yahoo.com> wrote in message
news:9a**************************@posting.google.c om...

How do I call a SQL Server Stored Procedure from a form in Access?

Thanks in advance!

chfran

[Rich P]

You can use the ADO command object as follows:

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=S:" _
& "Initial Catalog=db1;UID=SA;PWD=;"
cmd.CommandTimeout = 600
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "yourSP"
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.AddNew
For i = 0 To RSado.Fields.Count - 1
RSdao(i) = RSado(i)
Next
RSdao.Update
RSado.MoveNext
Loop
RSdao.Close
RSado.Close
cmd.ActiveConnection.Close

I added parameters here (because my sp's all have params). I pull a
recordset from sql server and then copy it to a local DAO recordset
(tbl1). I do this in Access97 and it is very fast.

With ADO.net you don't even need a do loop. You can use Insert Into (or
select into) directly from the dataset object.

Rich

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

[Peter Doering]

On 9 Jan 2004 12:33:17 -0800, chfran wrote:

How do I call a SQL Server Stored Procedure from a form in Access?

As PassThrough from VBA:

Dim qrySP As QueryDef
Set qrySP = CurrentDb.CreateQueryDef("")
With qrySP
.Connect = "ODBC;Driver={SQL Server};Server=..."
.ReturnsRecords = False
.SQL = "YourProc"
.Execute dbSQLPassThrough
.Close
End With
Set qrySP = Nothing

Peter

--
No mails please.