OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not giv
I followed instruction to 'USING SQL SERV3ER MANAGEMNT STUDIO" to create a new link server, to store Xls to a Sql Table,
when i try to run the query i was prompted by this error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not give any information about the error.
here is the instruction;
1. select the OTHER DATA SOURCE option
2. in the provider list, click MICROSOFT JET 4.0 OLE DB PROVIDER
3. in the product name box type EXCEL for the name of the OLE DB DATA SOURCE
4. in the data source box, type the full path and file ame of the excel file
5. in the provider string box, type EXCEL 8.0 for an excel 2002,excel 2000 or excel 97 workbook
6 click OK
query:
SELECT * INTO tablename FROM linkservername...Sheet1$
even tried to do it using ADO and SQLOLEDB (opendatasource and openrowset over an open SQLOLEDB connection.
rgds
cyrus
Re: OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not
Here's a sample code to retrieve records from an Excel, have a look!
Code:
Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rsADO As ADODB.Recordset
Dim strSQL As String
Dim strPath As String
strPath = App.Path & "\Book1.xls "
Set cn = New ADODB.Connection
With cn
.Provider = "OLEDB"
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""Excel 8.0;HDR=No;IMEX=1"";"
.Open
End With
Set rsADO = New ADODB.Recordset
With rsADO
.Open "SELECT * FROM [Sheet1$]", cn, adOpenForwardOnly, adLockReadOnly
Do While Not .EOF
Debug.Print .Fields(0)
.MoveNext
Loop
.Close
End With
'clean-up
Set rsADO = Nothing
cn.Close
Set cn = Nothing
End Sub