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
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