Hello,

I have SQL Server and a VBA Access application. I also have a ODBC System DSN data source named LAVA. The connection test is good. However, when I use ADODB to connect, connection appears to be opened, but I get a "Operation is not allowed when object is closed". Any suggestons? Here is the code:

   Dim rst As ADODB.Recordset
   Dim cnn As ADODB.Connection

    Set cnn = New ADODB.Connection
    cnn.Open "DSN=LAVA;"

    Set rst = New ADODB.Recordset
    rst.Open "GIS_Data", cnn, adOpenForwardOnly, adLockReadOnly

    Dim fld As ADODB.Field
    For Each fld In rst.Fields
        Debug.Print fld.Name
    Next

    cnn.Close

Look up the ConnectionString article in VBA Help. I don't think you are using the correct syntax. It is odd that SQL Server did not throw you an error:

cnn.ConnectionString = "Data Source=LAVA;"
cnn.Open



mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks, I should mention that this DB is in SQL Server Express. I am using Windows Authenication mode.

"Provider=sqloledb;" & _
  "Server=localhost;" & _
  "Database=LAVA;" & _
  "Trusted_Connection=true"

I get: "The user is not associated with a trusted SQL Server connection."

How do I configure my SQL Server Express to allow me in.

My problem is I don't know how to set my SQL Server correctly.

I see lots information on the connection string, but what I don't see is information for configuring SQL Server and Active Directory and other things that are needed for a VB application to connect to a Database. Where would I find this?