• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 713
  • Last Modified:

Exclude Excel file from re-linking in Access database with VBA

I have a MS Access front end which is linked to a back end database. I also link to one Excel.xls 2003 file. From time to time I want to relink to another back end Access database with similar tables, but I do not want to relink the Excel file.
My database is Access 2003, although I run it in Access 2010, all on my local C drive.
The Excel file is named LastLanded and the sheet that is linked is Named Sheet1. When I go to linked table manager, I see it is linked as "LastLanded  (C:\MeatSCL_CostingData\LastLanded.xls\Sheet1$)" Note the $ sign after the Sheet1.
I have VBA code like below. When I want to exclude an Access table from re-linking it works fine, but when I try it with the Excel file, I get a message:
"The Microsoft Access database engine could not find the object 'Sheet 1$'. Make sure the object exists and that you spell its name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator."
This also happens even if I add "\Sheet1" or "\Sheet1$" behind the "LastLanded" name.
Can a clever person help me out please.

VBA Code that I use
    Dim dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Set dbs = CurrentDb
    Set Tdfs = dbs.TableDefs
    'Loop through the tables collection
    DoCmd.Hourglass True

    For Each Tdf In Tdfs

        If Tdf.SourceTableName <> "LastLanded" Then

            If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
                Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
                Tdf.RefreshLink 'Refresh the link
            End If

        End If
Fritz Paul
Fritz Paul
  • 2
  • 2
1 Solution
Rey Obrero (Capricorn1)Commented:
create and run this query to see the correct properties of the linked tables

SELECT [name],[connect],Database,[type]
FROM msysobjects
WHERE [type]=6
Fritz PaulAuthor Commented:
Thanks, I've done it, but I don't know what to do with it. Below are my results. I also attach the results in Excel format. LastLanded is the file that I want to exclude. Any further advice?
name      connect      Database      type
LastLanded      Excel 8.0;HDR=YES;IMEX=2;      C:\MeatSCL_CostingData\LastLanded.xls      6
tblBlockTestDetail            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
tblBlockTests            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
tblCountry            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
tblGeneralInfo            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
tblIngredient            C:\MeatSCL_CostingData\MeatSCL_Costing_be.mdb      6
Rey Obrero (Capricorn1)Commented:
why are you using

 Tdf.SourceTableName ?

try replacing this part



Fritz PaulAuthor Commented:
!!!!!! You really are clever.
Now I just want to go on re-linking all the time.
Thanks a lot.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now