• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2062
  • Last Modified:

ADO from Excel 2010 to SQL Server 2008

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.

Thanks

-Jeremy
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
        .AddNew
        
        .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)
        
        .Update
        
        .Close
    End With
    
CleanUp:

    Set cn = Nothing
    Set rs = Nothing
    
End Sub

Open in new window

0
FamousMortimer
Asked:
FamousMortimer
  • 3
  • 3
1 Solution
 
leonstrykerCommented:
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.

Leon
0
 
Alpesh PatelAssistant ConsultantCommented:
Please refer connectionstring.com site
0
 
FamousMortimerAuthor Commented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
leonstrykerCommented:
Take a look for a connection string here:

http://carlprothman.net/Default.aspx?tabid=81

What id and password did you use in your DSN connection?
0
 
FamousMortimerAuthor Commented:
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.
0
 
FamousMortimerAuthor Commented:
Thanks... that's a great site to add to the bookmarks
0
 
leonstrykerCommented:
You are welcome. I would still advise against using ADO methods to update or insert data in the database.

Leon
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now