ADO from Excel 2010 to SQL Server 2008

Posted on 2011-05-03
Last Modified: 2012-08-14
Hey Experts,

First off, I am fairly new to SQL Server.  I've migrated an Access database to to the SQL server.  The Access db is populated by an Excel program that performs queries, and calculations on those queries then writes the calculated data to the Access db, but now I need it to write to SQL.

I have been running into several different errors when trying to connect to SQL through VBA.  I am able to use Microsoft Query to view the SQL database, and my dsn tests successfully.

Here is the Excel VBA code that writes to the Access DB.  Basically all i need is this to write to SQL instead of Access.

Here's some info:
ServerName: SRVSQL01
InstanceName: MSSQL
Database Name: EfficiencyData
TrustedConnection: yes

Let me know if you need more information or clarification.


Sub WriteToDB(rng As Range)
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim mbrOverwrite As VbMsgBoxResult
    Dim dte As Date
    dte = Worksheets(rng.Offset(-1, 0) & "_Data").Range("O1").Value
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Open ("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbName & ";Persist Security Info=False;")
    rs.Open "SELECT Main.* FROM Main WHERE (((Main.ID)=" & Chr(34) & rng.Offset(-1, 0) & Format(shtMain.Range("B2").Value, "yyyymmdd") & Chr(34) & "));", cn, adOpenKeyset, adLockOptimistic
    With rs
        .Fields("ID") = rng.Offset(-1, 0) & Format(shtMain.Range("B2"), "yyyymmdd")
        .Fields("AnalysisDate") = shtMain.Range("B2")
        .Fields("PressName") = rng.Offset(-1, 0)
        .Fields("S1_MR") = rng.Offset(1, 1)
        .Fields("S1_RN") = rng.Offset(2, 1)
        .Fields("S1_WU") = rng.Offset(3, 1)
        .Fields("S1_DN") = rng.Offset(4, 1)
        .Fields("S1_BillableTime") = rng.Offset(6, 1)
        .Fields("S1_BillableTimePct") = rng.Offset(7, 1)
        .Fields("S1_ActualShiftLength") = rng.Offset(9, 1)
        .Fields("S1_ProductionTime") = rng.Offset(10, 1)
        .Fields("S1_Efficiency") = rng.Offset(12, 1)
        .Fields("S2_MR") = rng.Offset(1, 2)
        .Fields("S2_RN") = rng.Offset(2, 2)
        .Fields("S2_WU") = rng.Offset(3, 2)
        .Fields("S2_DN") = rng.Offset(4, 2)
        .Fields("S2_BillableTime") = rng.Offset(6, 2)
        .Fields("S2_BillableTimePct") = rng.Offset(7, 2)
        .Fields("S2_ActualShiftLength") = rng.Offset(9, 2)
        .Fields("S2_ProductionTime") = rng.Offset(10, 2)
        .Fields("S2_Efficiency") = rng.Offset(12, 2)
        .Fields("S3_MR") = rng.Offset(1, 3)
        .Fields("S3_RN") = rng.Offset(2, 3)
        .Fields("S3_WU") = rng.Offset(3, 3)
        .Fields("S3_DN") = rng.Offset(4, 3)
        .Fields("S3_BillableTime") = rng.Offset(6, 3)
        .Fields("S3_BillableTimePct") = rng.Offset(7, 3)
        .Fields("S3_ActualShiftLength") = rng.Offset(9, 3)
        .Fields("S3_ProductionTime") = rng.Offset(10, 3)
        .Fields("S3_Efficiency") = rng.Offset(12, 3)
    End With

    Set cn = Nothing
    Set rs = Nothing
End Sub

Open in new window

Question by:FamousMortimer
    LVL 29

    Expert Comment

    Try this as a connection string:

    "Provider=sqloledb;Data Source=SRVSQL01;Initial Catalog=EfficiencyData;Integrated Security=SSPI"

    BTW, I would suggest you use SQL INSERT as opposed to ADO Update method.

    LVL 21

    Expert Comment

    by:Alpesh Patel
    Please refer site
    LVL 10

    Author Comment

    Thanks for the replies.  I tried your connection string and received an error...

    Run-time error
    '-2147467259 (80004005)':
    [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

    I am wondering if it is a SQL configuration error.  My login credentials have sysadmin access and as i said in the first post, my DSN test successfully and i can connect with MS Query.
    LVL 29

    Accepted Solution

    Take a look for a connection string here:

    What id and password did you use in your DSN connection?
    LVL 10

    Author Comment

    I set up SQL to use Windows Authentication (Tursted Connection) so it should pass my logon credentials to the server.

    Using the link you sent, i had the connection prompt for a username and password so it created the connection string for me which logged in successfully.  I did a debug.print cn.ConnectionString and this was the result:

    "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=EfficiencyData;Data Source=SRVSQL1\MSSQL;Current Language=us_english;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=LBLROBW233;Use Encryption for Data=False;Tag with column collation when possible=False;"

    Open in new window

    Using that string and removing default values, i was able to successfully establish the connection.

    Thanks a lot for your guidance.
    LVL 10

    Author Closing Comment

    Thanks... that's a great site to add to the bookmarks
    LVL 29

    Expert Comment

    You are welcome. I would still advise against using ADO methods to update or insert data in the database.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    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.

    761 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

    11 Experts available now in Live!

    Get 1:1 Help Now