I've run into a problem I can't solve.
I'm performing my first connection with ADO through OLEDB, not ODBC. I
wanted to perform a simple procedure call to sp_databases. No parameters, no
weirdness. I connected to the database just fine. I created a command with
sp_databases as the CommandText and adCmdStoredProc as the CommandType.
Everything looked great. Then I tried to execute the command into a
recordset. I kept getting a recordset not open error. I tried all kinds of
different parameters, changing the cursor from client to server and back
again, setting the connection mode...
Anyway, nothing worked. Then I changed my connection from using the SQLOLEDB
provider to using the default. I set up an ODBC DSN and tried running the
proc again. This time it worked just fine. Wonderful. Now I can use ODBC to
make calls to the database. I haven't moved forward at all. I might as well
stick to RDO (or ODBC API calls in a pinch).
What am I doing wrong?
Here's the sample code:
Set gconDB = New ADODB.Connection
gconDB.Provider = DBPROVIDER
.ConnectionString = "Data Source=" & txtDataSource.Text & _
";User ID=" & txtUserID.Text & ";Password=" & txtPassword.Text
'ODBC Connection String
'.ConnectionString = "Data Source=CTEDEV;User ID=" & _
txtUserID.Text & ";Password=" & txtPassword.Text
Set comDBList = New ADODB.Command
.ActiveConnection = gconDB
.CommandText = "sp_databases"
.CommandType = adCmdStoredProc
Set rsDBList = comDBList.Execute