Excel to network SQL DB > VB Code help!

ModifiedVB

New Member
Joined
Jan 27, 2014
Messages
13
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
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

ModifiedVB

New Member
Joined
Jan 27, 2014
Messages
13
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

New Member
Joined
Jan 27, 2014
Messages
13
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,973
Messages
5,627,942
Members
416,282
Latest member
fchagas97

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top