Results 1 to 7 of 7

How does one recognize in whether a table is local or linked

  1. #1
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,083

    How does one recognize in whether a table is local or linked

    How does one recognize in whether a table is local or linked? I assume one loops through the Tables Collection to get a list of tables but is there a property that one can examine to determine if the table is local to the host mdb or is linked to a different mdb?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,176
    Are we to assume that simply looking at the objects list in the nav pane isn't something you can do?
    Or are you wanting code to iterate over the tables in a project to see if they're linked or not? If yes, they have a connection string if linked. See something like
    https://superuser.com/questions/1139...arate-database
    - "doesn't work" is no help. What's happening? Error messages? Where??
    - Use code tags for code/sql. Implement changes in copies of your database.
    Irregardless, ain't no such word as "reoccur".

  3. #3
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,083
    From code: PERFECT!
    Code:
    Public Sub LoopThroughTables()
        Dim db As DAO.Database
        Dim tdf As DAO.TableDef
        Set db = CurrentDb
        For Each tdf In db.TableDefs
            ' ignore system and temporary tables
            If Not (tdf.Name Like "MSys*" Or tdf.Name Like "~*") Then
                Debug.Print tdf.Name, tdf.Connect
            End If
        Next
        Set tdf = Nothing
        Set db = Nothing
    End Sub

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,176
    That's great, but I wasn't sure if that was what you were after.
    I'm curious. What will you do with the information? Or is this just a learning exercise?

  5. #5
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,083
    I have an app with back-ends containing cash-flow & budgeting tables primarily for single purpose disciplines, sometimes for personal finances and sometimes business. I have a new user that has needs for both so I'll augment the app to support switching between two back-ends, one personal and one business. I will add code and form options where the user can suspend working with one discipline and switch to the other. Single purpose users will be unaware of the feature unless changes are made in the app's settings where an alternate back-end mdb file can be specified. When the user wants to switch, I will re-link a half-dozen tables so I wanted to insure that the table properties were well understood and the necessary precautions taken lest I inadvertently delete a local table.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    4,176
    I thought it might have been a method of preventing others from copying the Be or FE to their local drive, i.e., you'd check the current connection against table records of authorized connections. I did this once because it wasn't unheard of for users to think they could make their life easier by doing copying the production db. One thing you might want to consider is the possibility that if any BE can be concurrently shared, relinking tables would be a problem for somebody who's using them. A logged in user count could prevent this. While concurrent users may not be on your immediate horizon, future developments sometimes overlook the ramifications of functionalities implemented long ago.

  7. #7
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,083
    It's doubtful that the new user I referenced would encounter the situation you describe. However, in view of the fact that in this case the switching activity could involve the BE on a NAS drive I'll add a field to the settings table (tblSettings") and store the Windows username in that field as the user takes charge of it. A few lines of well placed code and I'll not have to even think about it going forward.

    Thanks for the heads-up

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-07-2018, 06:08 AM
  2. Replies: 3
    Last Post: 10-08-2015, 12:02 PM
  3. Replies: 1
    Last Post: 07-28-2015, 01:04 PM
  4. Replies: 3
    Last Post: 01-01-2015, 04:25 PM
  5. How to Create local copy of linked Table
    By behnam in forum Programming
    Replies: 3
    Last Post: 11-20-2014, 04:49 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums