Excel VBA connection string giving an error on my work machine but it works on my laptop

Posted on 2007-10-01
Last Modified: 2008-01-09
I have the following code in Excel VBA (2003):
    On Error GoTo ErrHandler
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    conn.ConnectionString = "Provider=SQLOLEDB.1;DataSource=NYDBTEST;Initial Catalog=CDOCash;Integrated Security=SSPI"

When I get to the conn.Open, the it triggers the following error:

                  "Invalid Connection String Attibute"

When I run it on my Laptop, this works fine.  But when I put it on my machine at work, I get the error.  I double checked the server and database.  They are the correct names.  My SQL Server both on my laptop and at work supports Windows Integrated Security.


Question by:techhound
    LVL 16

    Expert Comment

    Using VBA to Get At SQL Server Data
    If you're after a less manual process, the last method is a bit more complex, but even more powerful. Microsoft Excel, like all newer versions of Microsoft Office products, has a complete programming interface in the guise of Visual Basic for Applications (VBA). If you've got any programming experience at all, you can write code against a database.

    Again, all the previous warnings about locking apply. Additional warnings are warranted here, since with programming you can affect data in the database as well as reading from it.

    There are a few places you can use VBA in Excel, such as custom functions and macros, but we'll stick with macros for this example. The basic process is that you create a macro, edit it, and then run it.

    You edit the macro inside an editor, in which you type the code to connect, access, and close the connection to a database. Here's the process to create your own macro to connect to SQL Server programmatically:

    1.       Open Excel.

    2.       Click on Tools, then Macro, and then Macros...

    3.       Name the Macro, and then click Create.

    4.       In the editor window, type the following information, substituting the proper names for the server and the tables you want in between the "Sub xxxx" and "End Sub" tags:

    ' Declare the QueryTable object
    Dim qt As QueryTable
    ' Set up the SQL Statement
    sqlstring = "select au_fname, au_lname from authors"
    ' Set up the connection string, reference an ODBC connection
    ' There are several ways to do this
    ' Leave the name and password blank for NT authentication
    connstring = _
    ' Now implement the connection, run the query, and add
    ' the results to the spreadsheet starting at row A1
    With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
    End With


    Save and close the macro, and run it from the same menu you accessed in step 2.


    There are probably at least ten programmatic ways to do this exact same thing  and all of them are correct! If you're really interested in programming using VBA, you'll find several good books here at InformIT and also on Safari.

    Keeping The Database Connection Live
    We now move away from the "Transfer In and Out" category to the "Linked Data" category of methods. I'll describe the two main methods to link data, both of which link Excel data into a SQL Server query.

    There are ways to use Excel to manage data directly in SQL Server, but they involve a bit more programming, and I've found them to be a bit clumsy  especially for daily use. Excel simply doesn't handle locks and connectivity issues as well as you need for large-scale production solutions.

    You could also code a solution that accesses data stored in an Excel spreadsheet and update a SQL Server table as a result, but this is really event-driven and not a linked solution. (Again, there's a great deal of programming help in that vein here on InformIT.)

    So returning to the methods I've found easy to implement, there is a two-step process you can use to query data in an Excel spreadsheet.

    First, you'll need to create a linked server. Here's how to do that:

    1.       Open Query Analyzer.

    2.       Run the following code:

    -- Here we set up the linked server using the JET provider
    EXEC sp_addlinkedserver N'ExcelLink',
    @srvproduct = N'',
    @provider = N'Microsoft.Jet.OLEDB.4.0',
    @datasrc = N'C:\temp\test.xls',
    @provstr = N'Excel 8.0;'


    By doing so, you create a linked server, and give that connection a name. Notice also the name of the spreadsheet, which can also be a share location. You only have to do this once for each spreadsheet; if you're not going to access that spreadsheet again, it's a good idea to drop the linked server after you've used it.

    Now that we have a linked server, we can access the data. The process for this method is as follows:

    1.       In a query tool such as Query Analyzer, type the following:

    -- Setup the user connection for the spreadsheet
    EXEC sp_addlinkedsrvlogin 'ExcelLink', 'false'
    -- Get the spreadsheet data - SCHOOLSP is the tab name
    SELECT * FROM OPENQUERY(ExcelLink, 'select * from [SCHOOLSP$]')


    In this section, we've used the OPENQUERY function, which passes the query on to the provider. You'll find the query language is pretty limited at times, with Excel. If the queries aren't selective enough, set up another worksheet in the workbook with the data you want, and query that one.

    As you can see, you have several options open to integrate data between Microsoft Excel and SQL Server. Keep the limitations in mind, and get that data out there!

    LVL 85

    Accepted Solution

    Does it work if you use:
    conn.ConnectionString = "Provider=SQLOLEDB;Data Source=NYDBTEST;Initial Catalog=CDOCash;Integrated Security=SSPI"
    Also, are you sure your server on the work machine is setup exactly the same and that you don't need to specify an instance of the server in the Data Source name?

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now