none
Connecting to 2012 localDB from VBA

    Question

  • I have been having a very frustrating time trying to connect to a localDB database from Excel VBA.

    I have created a database using Visual Studio 2012 Express and in the IDE the database seems fine. I can create data and query it without problem.

    In Visual Studio the Connection Properties windows shows:

    Name:                     D:\FISKDEV_EXCEL\FISKDBSOLN\FISKDBSOLN\FISKDB.MDF

    Connection String:    Data Source=(LocalDB)\v11.0;AttachDbFilename=D:\FiskDev_Excel\FiskDBSoln\FiskDBSoln\FiskDB.mdf;Integrated Security=True

    Provider:                 .NET Framework Data Provider for SQL Server

    Primary File Path:     D:\FiskDev_Excel\FiskDBSoln\FiskDBSoln\FiskDB.mdf

    I then try to connect to the automatic instance with the following code:

     Dim connStr As String
        Dim conn As ADODB.Connection
        
        connStr = "Server=(localDB)\v11.0;AttachDBFilename=D:\FiskDev_Excel\FiskDBSoln\FiskDBSoln\FiskDB.mdf;Integrated Security=True"
        
        Set conn = New ADODB.Connection
        conn.ConnectionString = connStr
        conn.Open

    However the Open fails with the following message:

    "Run-time Error  '-2147217887(80040e21)'

    Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

    Any pointer to what I am doing wrong would be greatfully received.

    Monday, August 05, 2013 9:35 PM

Answers

  • I've finally stumbled across a provider / connection string couple that allows connection to a LocalDB instance from a VBA program. The following code works.

         Dim conn As ADODB.Connection
         Dim rs As ADODB.Recordset
         Dim sqlStr As String

         connStr = "Driver={SQL Server native Client 11.0};Server=(LocalDB)\v11.0;AttachDBFileName=c:\ExcelTrial\tsetDB.mdf;Database=tsetDB;Trusted_Connection=Yes"
         sqlStr = "Select * from Customers"

         Set conn = New ADODB.Connection
         conn.ConnectionString = connStr
         conn.Open

         Set rs = conn.Execute(sqlStr)

         Do While Not rs.EOF
            Debug.Print rs!CompanyName
            rs.MoveNext
         Loop

         rs.Close
         Set rs = Nothing

                            
    • Marked as answer by CatFelix Monday, August 19, 2013 10:04 PM
    Monday, August 19, 2013 10:04 PM

All replies

  • Hello,

    AFAIK the old OleDB don't support the usage of SQL Server user instances / the parameter "AttachdBFilename"; see Microsoft OLE DB Provider for SQL Server


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, August 06, 2013 4:33 AM
  • Hi Olaf,

    I've changed the connection code to the following

        Dim connStr As String
        Dim conn As ADODB.Connection
        
        'connStr = "Server=(localDB)\v11.0;AttachDBFilename=D:\FiskDev_Excel\FiskDBSoln\FiskDBSoln\FiskDB.mdf;Integrated Security=True"
        
        connStr = "Provider=SQLOLEDB;Data Source=(localDB)\v11.0;Initial Catalog=FiskDB;Integrated Security=SSPI"
        
        
        Set conn = New ADODB.Connection
        conn.ConnectionString = connStr
        conn.Open
        

    and now get the following Message:

    Run-Time Error '-2147467259 (80004005)

    [DBNETLIB][ConnectionOpen (Connect().]SQL Server does not exist or Access denied.

    All my installations have been done under a User Account with Admin privileges and I have not explicitly set any rights at all. So I presume all the defaults were applied and since I am executing the above code under the same user account I also presume that the 'access denied' does not apply. So what is that that this code can't find - the localDB instance? ( to which I thought it attached automatically). Are there any suggestions on what I might try?

    I've also run the above with

    Initial Catalog=D:\FiskDev_Excel\FiskDBSoln\FiskDBSoln\FiskDB.mdf

    but got the same result

    Tuesday, August 06, 2013 7:00 AM
  • Hi,

    Update the provider to latest one: http://www.microsoft.com/en-us/download/details.aspx?id=29065

    "Microsoft® SQL Server® 2012 Native Client"

    And try use "SQLNCLI11.1" as the provider.


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Friday, August 09, 2013 9:35 AM
  • I downloaded 2012 Native Client Provider and executed the following connection string

    connStr = "Provider=SQLNCLI11.1;Server=(LocalDB)\v11.0;Database=tsetDB;Integrated Security=SSPI"

    This produced a message saying "Cannot open tsetDB requested by login."

    Note that the database here has changed from the previous discussions above. This is because this was executed on a different computer. (The other one broke and I am currently awaiting delivery of new machine.). I tested the tsetDB data base from a VB.net program using the .NET framework Data Provider for SQL Server and the following connection string:

    Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\ExcelTrial\tsetDB.mdf;Integrated Security=True;Connect Timeout=30

    This worked without problem.

    Sunday, August 18, 2013 9:04 PM
  • I've finally stumbled across a provider / connection string couple that allows connection to a LocalDB instance from a VBA program. The following code works.

         Dim conn As ADODB.Connection
         Dim rs As ADODB.Recordset
         Dim sqlStr As String

         connStr = "Driver={SQL Server native Client 11.0};Server=(LocalDB)\v11.0;AttachDBFileName=c:\ExcelTrial\tsetDB.mdf;Database=tsetDB;Trusted_Connection=Yes"
         sqlStr = "Select * from Customers"

         Set conn = New ADODB.Connection
         conn.ConnectionString = connStr
         conn.Open

         Set rs = conn.Execute(sqlStr)

         Do While Not rs.EOF
            Debug.Print rs!CompanyName
            rs.MoveNext
         Loop

         rs.Close
         Set rs = Nothing

                            
    • Marked as answer by CatFelix Monday, August 19, 2013 10:04 PM
    Monday, August 19, 2013 10:04 PM