Re-link an existing xls linked table to a different excel file

[Jim Franklin]

Hi,

I have a Access 2003 application with a linked table pointing at an Excel
spreadsheet. I would like to be able to switch the link so that it points at
a different spreadsheet. All excel spreadsheets are in exactly the same
format (basically one for each sales person, and I need the ability to
switch between sales people.)

I thought this would be simple & tried doing this using tabledef.connect but
could not get it to work. Do I need to delete the linked table object and
create a new one each time I want to switch?

Can anyone tell me the simplest way of doing this?

Many thanks,

Jim

 

[Jeff Boyce]

Jim

A bit more information, please...

Is this a one-time requirement, or are you looking for a way to
regularly/periodically link from Access to a collection of data stored now
in Excel spreadsheets?

How are those spreadsheets named (all the same name?, each with it's own
name?)?

Who will be doing this? Is this something a Access expert would be doing
"behind the curtain", are you looking to provide something an Access novice
could accomplish (remember "Easy ... is HARD!"), or do you want to end up
with something that Access does automatically, without human intervention?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Jim Franklin]

Hi Jeff,

This needs to be a regular link. The user will be selecting a salesperson
from a combo box on a form. The spreadsheets are originally created in
Access, and have a common naming convention containing the salesperson's id
(available in Access.)

The re-linking needs to be done programatically, as users will not be Access
experts and will just be using standard forms as a UI.

I assumed it would be easy to change the .Connect property for the linked
table object, as I would for a Jet db linked table, but the Refreshlink
method doesn't seem to apply to linked Excel tables and without it nothing
happens.

Thanks again for your help,

Jim

 

[Jim Franklin]

I think I have sorted this. I found an article by Ken Snell about the
various ways to import data from Excel (see link below) and have created a
query which links to the spreadsheet. By changing the query SQL in vba I can
change which spreadsheet it points to.

The code I have written is:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strPATH As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qry_SalesRepList_XL_Import")

strPATH = GetBEPath & "Sales Rep Lists - IN\"

strSQL = "SELECT SPLIST.* " & _
"FROM [Excel 8.0;HDR=YES;IMEX=1;Database=" & strPATH & "Sales Rep
List - " & Me.cboSalesRep & ".xls].[qry_SalesRep_Export$] as SPLIST;"

qdf.SQL = strSQL

(GetBEPath is a function to return the Path of the BackEnd.)

Does anyone know of any reason why I should NOT do it this way?

For anyone else looking for guidance on importing/linking from Excel, here's
the article from Ken Snell

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#DataTypeErr

Cheers,
Jim

 

[Ken Snell]

That method should work. You also could use the TransferSpreadsheet method
to relink EXCEL spreadsheets. My web page shows examples of how to import
the data from EXCEL using TransferSpreadsheet (with acImport argument); you
just change acImport to acLink in the argument list.

--

Ken Snell
http://www.accessmvp.com/KDSnell/

I think I have sorted this. I found an article by Ken Snell about the
various ways to import data from Excel (see link below) and have created a
query which links to the spreadsheet. By changing the query SQL in vba I
can change which spreadsheet it points to.

The code I have written is:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strPATH As String

Set db = CurrentDb
Set qdf = db.QueryDefs("qry_SalesRepList_XL_Import")

strPATH = GetBEPath & "Sales Rep Lists - IN\"

strSQL = "SELECT SPLIST.* " & _
"FROM [Excel 8.0;HDR=YES;IMEX=1;Database=" & strPATH & "Sales Rep
List - " & Me.cboSalesRep & ".xls].[qry_SalesRep_Export$] as SPLIST;"

qdf.SQL = strSQL

(GetBEPath is a function to return the Path of the BackEnd.)

Does anyone know of any reason why I should NOT do it this way?

For anyone else looking for guidance on importing/linking from Excel,
here's the article from Ken Snell

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#DataTypeErr

Cheers,
Jim

Hi Jeff,

This needs to be a regular link. The user will be selecting a salesperson
from a combo box on a form. The spreadsheets are originally created in
Access, and have a common naming convention containing the salesperson's
id (available in Access.)

The re-linking needs to be done programatically, as users will not be
Access experts and will just be using standard forms as a UI.

I assumed it would be easy to change the .Connect property for the linked
table object, as I would for a Jet db linked table, but the Refreshlink
method doesn't seem to apply to linked Excel tables and without it
nothing happens.

Thanks again for your help,

Jim