Stored Procedure To Return A Recordset

In a nutshell, I am trying to set a combobox's row source using a stored procedure. Surely there is an easy way to do that.

I am working with SQL 2000 as my back-end, and my front-end is an Access Project. The stored procedure I am trying to run is on a different database then the one my project is connected to, but from what I can see in my de-bugging efforts, that is not the problem.

The Stored Procedure;

CREATE PROCEDURE dbo.sp_eeLinksByName
@EmployerNum char(6)

AS

SELECT dbo.TIMS_eeLinksByName.eeLink, dbo.TIMS_eeLinksByName.Employee FROM dbo.TIMS_eeLinksByName
WHERE (dbo.TIMS_eeLinksByName.eeErNum = @EmployerNum)
ORDER BY dbo.TIMS_eeLinksByName.Employee

returns 169 records when I run it directly from the MS Visual Studio environment.


However whe I try to run it from VBA with the following code;

Dim sp_eeLinksByName As String
Dim ConnectionString As String
Const DSeeLinksByName = "SOS-1"
Const DBeeLinksByName = "Insync"
Const DPeeLinksByName = "SQLOLEDB"

Dim objeeLinksByNameConn As New ADODB.Connection
Dim objeeLinksByNameRs As New ADODB.Recordset
Dim objeeLinksByNameComm As New ADODB.Command

ConnectionString = "Provider=" & DPeeLinksByName & _
";Data Source=" & DSeeLinksByName & _
";Initial Catalog=" & DBeeLinksByName & _
";Integrated Security=SSPI;"

' Connect to the data source.
objeeLinksByNameConn.Open ConnectionString

' Set a stored procedure
objeeLinksByNameComm.CommandText = sp_eeLinksByName
objeeLinksByNameComm.CommandType = adCmdStoredProc
Set objeeLinksByNameComm.ActiveConnection = objeeLinksByNameConn

' Execute the stored procedure on
' the active connection object...
' "CurrTSCalendar" is the required input parameter,
' objRs is the resultant output variable.
objeeLinksByNameConn.sp_eeLinksByName CurrTSEmployer, objeeLinksByNameRs

' Display the result.
'Debug.Print "Results returned from sp_CustOrdersOrders for ALFKI: "
Select Case objeeLinksByNameRs.RecordCount
Case 0
'Do Nothing
Case Is > 0
'Get the Employee List
objeeLinksByNameRs.MoveFirst
Do While Not objeeLinksByNameRs.EOF
MyControl.AddItem (objeeLinksByNameRs.Fields("eeLink") & ";" & objeeLinksByNameRs.Fields("Employee"))
objeeLinksByNameRs.MoveNext
Loop
End Select

'Clean up.
'objRs.Close
objeeLinksByNameConn.Close
Set objeeLinksByNameRs = Nothing
Set objeeLinksByNameConn = Nothing
Set objeeLinksByNameComm = Nothing

I get an "Object Variable or With Blick Vraiable not Set"...... for the life of me I do not know why? Does anyone have any thoughts?