D
DennisB
I want to automate sending Excel source data into a SQL Server database.
Here is the code that I wrote but I'm wondering if there is an easier way. I
have an ADO connection to SQL Server with a database called dbbudget.
Sub testCall()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Dim sFile As String
'==============================================
'this procedure is to load data from an Excel
'file into an SQL Server table
'this results in an ODBC call failed message
'==============================================
sFile = "D:\dbrininger\1MyWork\Projects\Development\FXS
Work\Transactions\YTD_Invoices.xls"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0"
'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=172.28.20.204;Database=dbbudget;" & _
"UID=dbrininger;PWD=test123].ImportTest " & _
"FROM [Invoices$]"
cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub
Here is the code that I wrote but I'm wondering if there is an easier way. I
have an ADO connection to SQL Server with a database called dbbudget.
Sub testCall()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
Dim sFile As String
'==============================================
'this procedure is to load data from an Excel
'file into an SQL Server table
'this results in an ODBC call failed message
'==============================================
sFile = "D:\dbrininger\1MyWork\Projects\Development\FXS
Work\Transactions\YTD_Invoices.xls"
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sFile & ";" & _
"Extended Properties=Excel 8.0"
'Import by using Jet Provider.
strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
"Server=172.28.20.204;Database=dbbudget;" & _
"UID=dbrininger;PWD=test123].ImportTest " & _
"FROM [Invoices$]"
cn.Execute strSQL
cn.Close
Set cn = Nothing
End Sub