Solved

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

Posted on 2013-10-31
4
703 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

690 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