SQL Server / Access commands/recordsets

[zachster17]

Hey everyone,

I wasn't sure if this should go under SQL Server or Access since it is a hybrid or both.

I'm having a problem using the ADODB.Command and Recordset objects in Access to connect to SQL Server commands. I'm used to writing code to conenct to SQL in VBA (in Access) a certain way, and I recently changed to a new place where they use a different SQL Server among other things.

Anyways, the way I used to write code using the ADODB.Command and ADODB.Recordset isn't working...

I have a stored procedure that has 1 parameter and I'm calling it this way:

    Set cmdNetwork = New ADODB.Command
    With cmdNetwork
        .CommandType = adCmdStoredProc
        .CommandText = "sp_DIS_ListNetworks"
        .Parameters.Append .CreateParameter("@NetworkID", adInteger, adParamInput)
        .Parameters("@NetworkID").Value = CInt(lstNetworks.Value)
        .ActiveConnection = CurrentProject.Connection
    End With
 
    'setup recordset
    Set rsNetwork = New ADODB.Recordset
    rsNetwork.Open cmdNetwork.Execute, CurrentProject.Connection, adOpenDynamic, adLockReadOnly
 

Before, I didn't have the .Parameters.Append .CreateParameter, but it was saying that the parameter couldn't be found when I didn't have it. Before, I cold just put .Parameters("@ParemeterName") = Value with no problems but now it doesn't work.

Also, when it I go to open the recordset (rsNetwork.Open) I get an error (Arguments of the wrong type, are out of acceptable range, or are in conflict with one another.) no matter what I set the cursor and lock method to. I don't get an error when I leave off all arguments after .Execute.

Would anyone be able to refresh me on why these errors are happening?

Thank you!

Zach

[ck9663]

You need to test if your connection is working first.

This is more of an Access problem than a SQL problem.

Try running your script up to the connection portion. Test the connection (use a prompt or messagebox). Then close the connection. If you can properly connect, then test your recordset by executing a simple select and storing it (and accessing it) on a recordset variable. Then next step is try your recordset with your stored proc.

By the time you're on the last portion, you'll know what your problems are. It's called isolation.

-- CK