Solved

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

Posted on 2013-10-31
4
694 Views
Last Modified: 2013-10-31
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
0
Comment
Question by:Fritz Paul
  • 2
  • 2
4 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39614591
create and run this query to see the correct properties of the linked tables


SELECT [name],[connect],Database,[type]
FROM msysobjects
WHERE [type]=6
0
 

Author Comment

by:Fritz Paul
ID: 39614781
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?
Please.
Results:
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
qryNamesOfTables.xlsx
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39614840
why are you using

 Tdf.SourceTableName ?

try replacing this part

Tdf.SourceTableName

with

Tdf.Name
0
 

Author Closing Comment

by:Fritz Paul
ID: 39614930
!!!!!! You really are clever.
Now I just want to go on re-linking all the time.
Thanks a lot.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now