p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Access VBA (http://p2p.wrox.com/forumdisplay.php?f=80)
-   -   Bad Connection String (http://p2p.wrox.com/showthread.php?t=67986)

paulcbyrum May 8th, 2008 07:16 PM

Bad Connection String

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


mmcdonal May 9th, 2008 10:18 AM

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;"


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

paulcbyrum May 9th, 2008 07:36 PM

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

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

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.

paulcbyrum May 9th, 2008 09:19 PM

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?

All times are GMT -4. The time now is 06:17 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.