ActiveConnection Property

[mesagreg]

I have created a DLL that contains a Data Environment that in turn holds a connection that points to a development database. I have added commands to the data environment for each of the stored procedures in my database.

I am using the following code to create a new ado connection that points to a production database and sets the ActiveConnection property of the command objects equal to the new connection:

----------------------------------------------
Private Sub SetDatabaseProperties(ServerName As String, UserID As String, Provider As Integer, DatabaseName As String)


Dim objConnection As ADODB.Connection
Set objConnection = New ADODB.Connection
With DataEnvironment2
objConnection.ConnectionString = "Provider=SQLOLEDB.1" & ";Persist Security Info=False;User ID=" & UserID & ";Initial Catalog=" & DatabaseName & ";Data Source=" & ServerName & ""

Dim tempCommand As ADODB.Command
If objConnection.State = 1 Then
objConnection.Close
End If

objConnection.Open objConnection.ConnectionString, UserID, ""

For Each tempCommand In .Commands
Set tempCommand.ActiveConnection = Nothing
Set tempCommand.ActiveConnection = objConnection
Next tempCommand

dim lngID As Long
Dim lngID2 As Long

.dbo_sp_insert_facility_1 lngID, "Greg's 4/3/2001 Test Facility", "Greg's Test Address", "555-1212", 1

End With
------------------------------------------------------------

When I try to use this commands to execute a stored procedure, an error is returned stating that the stored procedure '.dbo_sp_insert_facility_1' cannot be located.
The same error occurs for all my commands.


Can anyone tell me what I am doing wrong here?

Thanks in advance,

Greg