none
How to establish connection between SqlLocalDB.exe database and Excel?

    Question

  • Hi,

    I have made a local database instance with SqlLocalDB.exe (SQL Server Express 2012):

    Name: 			myTest
    Version:			11.0.2318.0
    Owner:			INCENTIVE\np (me)
    State:			Running
    Instance pipe name:  	np:\\.\pipe\LOCALDB#2E46A0D4\tsql\query

    In SQL Server Management Studio 2012 (Express) I have made a database called dbQuestionnaries, which is saved in C:\dbQuestionnaries.mdf, where I have created some tables and stored procedures.

    Now I am trying to connect to this database through VBA in Excel or VBScript and run a stored procedure. I have tried a bunch of different things but without any luck so far.

    Right now my VBA code is

    Sub TestSub()
    
        Dim cmd, sp
    
        sp = "usp_test"
        Set cmd = CreateObject("ADODB.Command")
        With cmd
            .ActiveConnection = "Server=(localdb)\\myTest;Integrated Security=true;"
            .CommandType = 4
            .CommandText = sp
            .Execute
        End With
        
        Set cmd = Nothing
    
        
        MsgBox (sp & " has been executed successfully!")
    
    End Sub

    But I get an error saying Multiple-step OLE DB operation generated erros. Check each OLE DB status valie, if available. No work was done. 

    Any help is much appreciated, thank you.


    Wednesday, July 17, 2013 11:19 AM

Answers

  • Thanks, didn't work though.

    But I did find another way to do it. I set up an ODBC connection to the database called SqlLocalDB and then I connected to the database with the following VBA code

    Sub newTest()
        Dim rs As Recordset, strSQL As String, strConnectionString As String
        Set rs = New Recordset
        Dim strConnectionString As String
                
        strConnectionString = "DSN=SqlLocalDB" + _
                                ";Trusted_Connection=Yes" + _
                                ";DATABASE=dbQuestionnaires;"
                                
        strSQL = "exec usp_test"
            
        Call rs.Open(strSQL, strConnectionString)
    
        'Clean up objects
        If (rs.State And ObjectStateEnum.adStateOpen) Then rs.Close
        If Not rs Is Nothing Then Set rs = Nothing
    
    End Sub

    Thursday, July 18, 2013 7:27 AM

All replies

  • Hello Nicolai,

    You have to modify the connection string, see LocalDB Connection String Example .And you are using VBA, not C#, so no need to quote the backslash

    =>

    ActiveConnection = "Server=(localdb)\myTest;Integrated Security=true;AttachDbFileName=C:\dbQuestionnaries.mdf;Initial Catalog=dbQuestionnaries;"


    Olaf Helper

    Blog Xing

    Wednesday, July 17, 2013 11:46 AM
  • Hi Olaf,

    I still get the same error with the connection string you have provided. Note the link you have given me (It is a link to this post).

    Wednesday, July 17, 2013 12:18 PM
  • Thursday, July 18, 2013 7:00 AM
  • Thanks, didn't work though.

    But I did find another way to do it. I set up an ODBC connection to the database called SqlLocalDB and then I connected to the database with the following VBA code

    Sub newTest()
        Dim rs As Recordset, strSQL As String, strConnectionString As String
        Set rs = New Recordset
        Dim strConnectionString As String
                
        strConnectionString = "DSN=SqlLocalDB" + _
                                ";Trusted_Connection=Yes" + _
                                ";DATABASE=dbQuestionnaires;"
                                
        strSQL = "exec usp_test"
            
        Call rs.Open(strSQL, strConnectionString)
    
        'Clean up objects
        If (rs.State And ObjectStateEnum.adStateOpen) Then rs.Close
        If Not rs Is Nothing Then Set rs = Nothing
    
    End Sub

    Thursday, July 18, 2013 7:27 AM