Results 1 to 3 of 3

Thread: How to use ADODB on MS SQL Express in Access 2000 or 2016 VBA?

  1. #1

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    195

    How to use ADODB on MS SQL Express in Access 2000 or 2016 VBA?

    I have this connection string:

    Code:
    Dim cnnString as String = "Provider=SQLOLEDB;Data Source=myMachineName\SQLEXPRESS;" & _
    "Initial Catalog=MyDatabase;" & _
    "Integrated Security=SSPI;"
    And my code snippet:
    Code:
    Private Sub adodbQueryBtn_Click()
    
    On Error GoTo dbErrorLbl
                      
        Set dbConn = New ADODB.Connection
        dbConn.Open cnnString
        Set adoRST = dbConn.Execute("SELECT * FROM MyTable;")
        
    dbErrorLbl:
        Debug.Print "------------------------------------------------"
        Debug.Print "ADODB conn error: " & Err.Number & ", " & Err.Description & ", " & Err.Source & ", " & Err.HelpContext & ", " & Err.HelpFile
    
    End Sub
    I keep getting this error:

    Code:
    ADODB conn error: -2147467259, [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified, Microsoft OLE DB Provider for ODBC Drivers, 1000440, C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\1033\VbLR6.chm
    What do I do wrong?

  2. #2

    Thread Starter
    Addicted Member kutlesh's Avatar
    Join Date
    Jun 2018
    Location
    Skopje, Macedonia
    Posts
    195

    Re: How to use ADODB on MS SQL Express in Access 2000 or 2016 VBA?

    Turns out I have been sending empty string as conn. string to my connection object.
    It happens

    When tired get rest first

  3. #3
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,198

    Re: How to use ADODB on MS SQL Express in Access 2000 or 2016 VBA?

    Errr? Huh?
    Dim cnnString as String = "Provider=SQLOLEDB;Data Source=myMachineName\SQLEXPRESS;" & _
    "Initial Catalog=MyDatabase;" & _
    "Integrated Security=SSPI;"

    Declaring and assigning in the same line?
    Code:
    Dim cnnString As String
    cnnString = "Provider=SQLOLEDB;Data Source=myMachineName\SQLEXPRESS;Initial Catalog=MyDatabase;Integrated Security=SSPI;"
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width