I'm running Access 2000 with SQL Server and have run into problems using DAO. I have decided to switch to ADO but cannot figure out how to pass a parameter from VBA code to a SQL Stored Procedure. Also, not clear on whether I need to Set Comm1 once or each time I open a recordset. Please help.. Thanks.
My code so far:
Dim Comm1 As New ADODB.Connection
Dim Rst1 As New ADODB.Recordset
Dim Cmd1 As New ADODB.Command
Set Comm1 = Application.CurrentProject.Connection
Set Cmd1 = New ADODB.Command
Cmd1.ActiveConnection = Comm1
Cmd1.CommandText = "sp_Test"
Cmd1.CommandType = adCmdStoredProc
'? Cmd1.Parameters(1).Value = XForID
Set Rst1 = Cmd1.Execute()
Set Rst1 = Nothing