link to the data source by creating a linked table

[Rick Watson]

Hello, we are using Access 2007 with ADPs. One of our people would like to
connect to Excel and/or text documents, not import them just use the "link
to the data source by creating a linked table" choice. However, in an ADP
under External Data->Excel (or text), the choice link to the data source by
creating a linked table is greyed out. Is this standard for ADPs? Is it
possible to link to excel from the SQL end and connect that way?

Thanks in advance,

Rick

 

[Robert Morley]

Hello, we are using Access 2007 with ADPs. One of our people would like
to connect to Excel and/or text documents, not import them just use the
"link to the data source by creating a linked table" choice. However,
in an ADP under External Data->Excel (or text), the choice link to the
data source by creating a linked table is greyed out. Is this standard
for ADPs? Is it possible to link to excel from the SQL end and connect
that way?

Thanks in advance,

Rick

Yes, that's the norm for ADPs. The table list is controlled strictly by
what's available on the SQL Server side. It seems they should've been able
to make it allow local/linked tables as well if they'd really wanted to, but
for whatever reason, Microsoft has never added that feature, leaving you to
choose between ADPs with no linking options and MDBs/ACCDBs with strictly
manual linking options.

I don't know if I've ever tried linking Excel into SQL Server and then into
an ADP, but I don't see any reason why it wouldn't work.


Rob

 

[Sylvain Lafontaine]

No: in an ADP project, there is no local table or ODBC linked table. Trying
to link to an ADP project from an Excel sheet or vice versa - ie. trying to
link an Excel Sheet to an ADP project) make no sense because the only thing
an ADP project can link to is toward a SQL-Server database.

What you should do would be to link the Excel document from the SQL-Server
itself and create a View. A second possibility would be to use an OPENQUERY
inside your Select statements to access the external spreadsheet.

 

[Vadim Rapp]

Yes, that's the norm for ADPs. The table list is controlled strictly by

what's available on the SQL Server side. It seems they should've been
able to make it allow local/linked tables as well if they'd really wanted
to, but for whatever reason, Microsoft has never added that feature,
leaving you to choose between ADPs with no linking options and MDBs/ACCDBs
with strictly manual linking options.

In Access 2003, I am able to link a datasource, including Excel. It severely
limits this by requiring that the ADP is connected to local sql server, but
it's certainly possible.

With remote sql server, still possible (but not exactly clean) by creating
linked server to oledb source on sql server.

Vadim Rapp