?
Solved

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

Posted on 2013-10-31
4
Medium Priority
?
708 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 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

649 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