Linked Excel Table

[MitchPitt]

My company has a small access 97 DB that takes raw data from a linked excel file. In Access we can use this as a normal table to join in particular queries. But when I try to run an SQL using ADODB (Recordset,Connection,Etc.), it will say that it cannot find the linked excel table... Is there a specific way a linked table has to be present in the SQL statement?

Here's an example.

Linked Table:
-------------
[Raw Data]

SQL:
----
Select * from [Raw Data];

Then i'll get an error:
-----------------------
runtime error: 80040e37


Please Help!

 

[cmmrfrds]

Set up the connection directly to the excel spreadsheet.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim connString As String
Dim sql1 As String, sql2 As String
connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\ATestDir\myTest.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
cn.ConnectionString = connString
cn.Open connString
sql1 = "select * from yourtable"
rs.Open sql1, cn, 3, 3