Steps to Reproduce Behavior

1. Open a new Standard EXE project in Microsoft Visual Basic 6.0. Form1 is created by default.
2. Set a reference to Microsoft ActiveX Data Objects 2.X.
3. Copy and paste the following code into the code window of Form1:
   
   Note You must change User ID=<UID> and password=<strong password> to the correct values before you run this code. Make sure that <UID> has the appropriate permissions to perform this operation on the database.

   Private Sub Form_Load()
   
   Dim cn As New Connection
   Dim cn2 As New Connection
   Dim cn3 As New Connection
   
       cn.Open "Provider=sqloledb;Data source=scout333;" & _
        "User ID=<UID>;Password=<strong password>;initial catalog=northwind"
   On Error Resume Next
       cn.Execute "sp_revokedbaccess 'User1'"
       cn.Execute "sp_droplogin 'User1'"
       
   On Error GoTo 0
       cn.Execute "sp_addlogin 'User1', 'Test   ', 'Northwind'"
       cn.Execute "sp_adduser 'User1', 'User1'"
   
       cn2.Open "Provider=MSDASQL;Driver=sql server;" & _
        "server=scout333;UID=User1;PWD=Test   ;"
       MsgBox "Connection State: " & cn2.State
       cn3.Open "Provider=sqloledb;Data source=scout333;" & _
        "User ID=User1;Password=Test   ;"
   
   End Sub
   					

4. Be sure to change the connection string for the cn object so it correctly uses an existing SQL Server log on. Also, change the connection strings for the cn2 and cn3 objects so they correctly point to your SQL Server.
5. Run the code. When the connection that uses the OLE DB Provider for ODBC Drivers connects, you receive confirmation of this connection in a message box. This message box contains a connection state of 1 (adStateOpen) if the connection is made properly.
6. Click OK in the message box. The code attempts to make the connection by using the OLE DB Provider for SQL Server. This fails with the above-mentioned error message.

Back to the top