Hi to all the experts:D
I have the following Data Source in ADODB.Connection that is fully functional:
Code:
"Data Source=D:\Book1.xls;" & _
"Extended Properties=Excel 8.0"
In the following ADODB.Connection:
Code:
Sub DB_con1()
Dim cn As ADODB.Connection
Dim strSQL As String
Dim lngRecsAff As Long
On Error Goto test_Error
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Book1.xls;" & _
"Extended Properties=Excel 8.0"
'Import by using Jet Provider.
strSQL = "Insert INTO [odbc;Driver={SQL Server};" & _
"Server=mydbserver;Database=DEV;" & _
"UID=sa;PWD=Welcome1@].abk_import " & _
"Select * FROM [Sheet1$]"
Debug.Print strSQL
cn.Execute strSQL, lngRecsAff ', adExecuteNoRecords
Debug.Print "Records affected: " & lngRecsAff
cn.Close
Set cn = Nothing
On Error Goto 0
Exit Sub
test_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure test of VBA Document ThisWorkbook"
End Sub
''''''''''''''''''''''''''''''''''''''''''''
To avoid putting the full path in Data Source for the excel file, I changed :
Code:
"Data Source=D:\Book1.xls;" & _
"Extended Properties=Excel 8.0"
to
Code:
"Data Source=" & App.Path & "\ Book1.xls;" & _
"Extended Properties=Excel 8.0"
Then, when I ran the code, I get the following error:
Code:
Error 42(Object required) in procedure test of VBA Document ThisWorkbook.
I have been unfortunate to find an answer for this problem.
Has anyone seen this error and find any resolution for it?
Thanks for any help.
Abrahim
VBA Data Source error
