Excel to network SQL DB > VB Code help!

[ModifiedVB]

Hello All!

Long time reader first time poster!
I have become stuck with some vb code for upload an entire sheet (18 rows) A>AC directly into a an SQL table which has matching columns.

I have been stuck on this code now for a while and its driving me crazy so hence i have joined up and decide to post my problem


Sub CopyFromExcelToSQLServer()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Set cn = New ADODB.Connection

cn.Open "Provider=SQLOLEDB.1;Data Source=8KSQL02<SVR-8KSQL02>;Initial Catalog=guideprice<GUIDEPRICE>;User ID=modified<MROSS>;Password=Winter13<WINTER14>"


strSQL = "SELECT * INTO GuidePrice_QuoteItems FROM 'C:\Modifed\EstimateTemplateV17.xlsm'.Sheet1 "

Debug.Print strSQL
cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing

End Sub



The code says "running" which i understand that its making the connection and the error im getting is -

Run-time error '-2147467259 (80004005)':
Automation error
Unspecified error



If anyone can shine some help on this would be amazing!

ModifiedVB

 

[ModifiedVB]

I seems as tho the issue is around this part of my code

cn.Open sConn
sSQL = "SELECT * INTO GuidePrice_QuoteItems FROM OPENROWSET('Microsoft.JET.OLEDB.12.0'," & _
"'Excel 12.0 Xml;HDR=YES;Database=C:\temp\SQL1.xls'," & _
"'select * from [Sheet1$]');"

I can get the connection, but pushing the data across is not playing ball!

 

[ModifiedVB]

Ok! Code Change and it works!!! Score! BUT its taking values from the current open sheet, the sheet which will have all the data a standard user will not have access to so how would i define a sheet with this code?

Sub ADOFromExcelToSQL()
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, myconn


Set cn = New ADODB.Connection ' connect to the SQL database'
cn.ConnectionString = "Provider=SQLOLEDB;Server=SVR-8KSQL02;Database=guideprice;Integrated Security=SSPI"
cn.Open


Set rs = New ADODB.Recordset ' open a recordset'
rs.Open "GuidePrice_QuoteItems", cn, adOpenKeyset, adLockOptimistic, adCmdTable
With rs
.AddNew ' create a new record'
.Fields("QuIt_LineItemID") = Range("B2").Value
.Update ' stores the new record'
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub