OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The provider did not giv

[Cyrus]

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

[dee-u]

Here's a sample code to retrieve records from an Excel, have a look!

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