Pushing Excel Data Into SQL Server with Excel VBA

[CJAI]

Why is this VBA Code generating this error message?
Run-time error -2147217904 (80040e10)
Automation error
Unspecified error

Sub Data_Push()

'This function pushes data from an Excel spreadsheet
'into a SQL Server database by pressing a button in Excel.
'Created/modified from various code samples

    '--------------------------------------------------------------------------------
    Dim OLEString As String
    OLEString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & _
                "Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"
    '...Maybe provider should be "sqloledb"?
        'OLEString = "Provider=sqloledb;"
    'MsgBox OLEString

    '...Maybe OLE string should be an ODBC string?
    Dim ODBCString As String
    ODBCString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;" & _
                 "Dbq=" & ActiveWorkbook.Path & "\" & ActiveWorkbook.Name & ";" & _
                 "DefaultDir=" & ActiveWorkbook.Path & ";"
    'MsgBox ODBCString

    '--------------------------------------------------------------------------------
    Dim SQLString As String
    SQLString = "INSERT INTO [ODBC;Driver={SQL Server};" & _
                "SERVER=ESSPITSRV01;DATABASE=Intranet;" & _
                "Trusted_Connection=yes;].TestTempTable (col1)" & _
                " VALUES ('test')"
    '...Test literal value first, then push Excel sheet data with select statement
        '" SELECT (col1) FROM [testpage$];"
    'MsgBox SQLString

    '--------------------------------------------------------------------------------
    Dim con As Object
    Set con = CreateObject("ADODB.Connection")
    con.Open OLEString
    con.Execute SQLString
    con.Close

End Sub

 

[PHV]

Why not playing with Recordset ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CJAI]

Please explain more, we are a bit new to this and we have a deadline this Wednesday.

How do you move a recordset to SQL Server? Any code samples?

Thanks much!

 

[PHV]

To have a correct connection string:

http://www.connectionstrings.com/

For Recordsetset examples, search your local drives for files named ado*.chm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CJAI]

Thanks.

I'm still a bit in the dark as to what sequence I should perform the activities, let alone the code syntax. Like I said I'm a newbie when it comes to this type of programming and any help is HIGHLY appreciated.

Thanks.

 

[CJAI]

We've changed the SQlString to

SQLString = "INSERT INTO [Provider=sqloledb;" & _
                "Data Source=ESSPITSRV01;Initial Catalog=Intranet;Integrated Security=SSPI;]" & _
                ".TestTempTable (col1)" & _
                " VALUES ('test')"

and now we're just getting the Automation error INSTEAD of the Automation & Unspecified error.

 

[PHV]

Your OLEString should be the connection string to the SQL server
Your SQLString should then be simply this:
"INSERT INTO TestTempTable (col1) VALUES ('test')"

(Provided the SQL server TestTempTable has no other mandatory columns than col1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fredericofonseca]

another example for you to have a look at

Sub putdata()
Dim wWork_sheet As Worksheet
Dim adoConn As ADODB.Connection
Dim adoComm As ADODB.Command
Dim strCmd As String
Dim i, x, y As Long

Dim sConn As String
Dim sUser As String
Dim sPass As String
Dim sSQL As String
sUser = InputBox("User name", vbOKOnly)
sPass = InputBox("Password", vbOKOnly)
Set adoConn = New ADODB.Connection

adoConn.Open "Provider=sqloledb;" & _
           "Data Source=systst;" & _
           "Initial Catalog=my_default_db;" & _
           "Uid=" & sUser & ";" & _
           "Pwd=" & sPass & ""
Dim aa As ADODB.Field
Set adoComm = New ADODB.Command
adoComm.CommandType = adCmdText
sSQL = ""
sSQL = sSQL & " INSERT INTO [target_ddb].[dbo].[HPI]"
sSQL = sSQL & " (ASTTYPE, ASSETMAK)"
sSQL = sSQL & " VALUES (?, ?)"
adoComm.Parameters.Append adoComm.CreateParameter("p1", adChar, adParamInput, 1)
adoComm.Parameters.Append adoComm.CreateParameter("p2", adChar, adParamInput, 15)


adoComm.CommandText = sSQL
adoComm.ActiveConnection = adoConn
adoConn.BeginTrans
y = 0
i = 2
Set wWork_sheet = Workbooks("Book3").Worksheets("Sheet1")
While Not wWork_sheet.Cells(i, 1).Value = ""
  With adoComm.Parameters
    .Item("p1").Value = wWork_sheet.Cells(i, 1).Text
    .Item("p2").Value = wWork_sheet.Cells(i, 2).Text

  End With
  adoComm.Execute , , adExecuteNoRecords
  y = y + 1
  i = i + 1
  If y = 10001 Then
    y = 1
    adoConn.CommitTrans
    adoConn.BeginTrans
  End If
Wend
adoConn.CommitTrans
End Sub

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com