Linking External Tables

[OldBirdman]

I have 2 computers:
#1 Desktop - Windows XP & Office 2000
#2 Laptop - Windows Vista & Office 2003
Laptop is NEW, and the hard-drive is partitioned C:\ and D:\ from factory, with Vista and ProgramFiles on C:\ and data on D:\

My Access programs share tables. If I change anything in the program (Front-end) on one computer, I don't know how to keep these computers in Sync.

The problem here is that if I copy the mdb file containing the forms, code, queries, etc, the links are invalid on the other computer. I wouldn't mind re-linking if it were easy, but it isn't. To relink, I have to write down the table names to link, delete the links, re-establish the links. Alternate is to re-establish links first, but Access creates a new name ("1" appended to name), so after I remove old links, I have to rename the new links.

This same problem occurs if I move an Access program within the file structure.

Is there some way to link tables automatically? I could put all in same folder, if that would solve problem.

[NeoPa]

Have you considered using UNC names for all files, even for those on the local computer?

This is what I do in similar circumstances & it works fine for me.

PS. UNC = Universal Naming Convention (\\ServerName\ShareName\Folder\etc).

[OldBirdman]

I see where you are going here. I Googled this, and see that apparently I don't need a drive specified (it can be anywhere).

But I don't see step #1 to impliment.

Computer #1 (Desktop) XP/Office2000
C:\My Documents\Access Programs\Xxx.mdb
C:\My Documents\Access Programs\Xxx-Tables.mdb

Computer #2 (Laptop) Vista/Office2003 - data is on D:\
Documents\Access Programs\Xxx.mdb
Documents\Access Programs\Xxx-Tables.mdb

How do I name these to UNC? How in Access Get External Data will they show?

[DonRayner]

Hey OB;

Assuming that you have the folder that the mdb is in shared out with the name MyShare, you would use the following. Depending on your setup you might even be able to use the hidden administration share D$ to access the D drive directly.

\\192.168.0.100\MyShare\Subfolders\MyDatabase.mdb as the path

Just changed the 192... to whatever the IP address is on the computer that you are linking to.

Don

[Denburt]

If you are looking for a VBA solution here is a good place to start:
http://support.microsoft.com/kb/291264
There is also a link on that page to utilize a dialog box so you can easily point it to the back end and off you go.

[OldBirdman]

I have 2 computers sitting on my desk, or one is in my lap. Occasionally I'm at my companions, who uses some of my programs. We copy to her computer with an ad-hoc computer-to-computer connection.

My laptop knows about 1 folder on my desktop. My desktop is stupid, and doesn't know about the new laptop. I still need to network with the old laptop, and am afraid to try to destroy the old network.

I'm so sorry, DonRayner, but I don't understand your response at all. I so completely don't understand that I am having trouble asking any questions about the answer.

I have files on computer #1

C:\My Documents\Access Programs\Xxx.mdb
C:\My Documents\Access Programs\Xxx-Tables.mdb

I copy these to computer #2 and the drive and primary folder names change

data is on D:\
Documents\Access Programs\Xxx.mdb
Documents\Access Programs\Xxx-Tables.mdb

After the copy, the programs won't run on computer #2, because the linked tables are to Drive\Folder = C:\My Documents

What do I rename to \\192.168.0.100\? This implies something other than a Home setup. Where do I get an IP Address for my home?

Denburt: I read that article several times, and to use it I would have to extensively modify it. It seems, from reading and not testing, that all the link tables are in one mdb file. Not the case here. Tables are split among multiple back-end files. This is so that I can change data on the laptop while simultaneously working on a different project on the desktop. Example: Where I went birding is in different file than my list of contacts. Overlap occurs only when I go birding with a leader not in my contact list, and must add them. Without this split-up, ANY change on either computer would cause any sync operation to fail.

The opening paragraph of your reference article:

In a run-time application, the front-end and back-end databases must be stored in the same folder. Even though you can change the default installation path of the back-end database to, for example, $AppPath\Folder1, there is no way for Access to refresh the links in the front-end to match the new installation location.

implies that if things are in the same folder, this will all work, and then says it won't work.

So I give up on this. I may put all my Access programs and files on C:\ and ignore the user folders "Documents". Then I can have identical Full-Path-Names, and everything will work.

[Denburt]

If your gonna use VBA it will likely take some work but hey. To create the links you need to set up a loop of your tables determine which are linked and which aren't. If your Back end db is in the same folder then use the BkEnd variable to hold the name of the back end you are linking. You could even extract that from the link that the table currently holds. The code they have there looks extensive and may seem a bit complicated but it sounds like your best alternative.

You can use the following strSearchPath to determine where the front end is then go from there as to linking it.

strSearchPath=Application.CurrentProject.path & "\" & BkEnd
 
if DoesFileExist(strSearchPath) then
 Link it here
else
 Prompt here
End if

Function DoesFileExist(strFileSpec As String) As Boolean
    'Return True if the file that is specified in the
    'strFilespec argument exists.
    'Return False if strFileSpec is not a valid
    'file or if strFileSpec is a directory.
    Const INVALID_ARGUMENT As Long = 53
'Auto Error Insert
    On Error GoTo DoesfileExist_Err
    If (GetAttr(strFileSpec) And vbDirectory) <> vbDirectory Then
        DoesFileExist = CBool(Len(Dir(strFileSpec)) > 0)
    Else
        DoesFileExist = False
    End If
DoesfileExist_End:
    Exit Function
DoesfileExist_Err:
    DoesFileExist = False
    Resume DoesfileExist_End
End Function

[Denburt]

After reviewing the article further I am pretty sure this would almost be a copy paste solution with only a very few minor changes. 1 you would need to set up something to get the file and files path. then simply call the procedure.

RefreshLinks()

Look for this in the refresh procedure and make a slight modification:

 If blnTablesNotLinked = False Then
                Exit Function
            Else

Change it to:

If blnTablesNotLinked = False Then
             strFullName = WheresMyFile()
objTbl.Properties("Jet OLEDB:Link Datasource") = strFullName

The only other thing would be to create the wheresMyFile routine which is basically calling the file dialog and returning the file name and path.

[OldBirdman]

The only other thing would be to create the wheresMyFile routine which is basically calling the file dialog and returning the file name and path.

I don't know how to call a dialog routine successfully. I've tried many times, and occasionally it works, for awhile, then fails because it can't find some reference. Continually figuring out references, and re-checking them is a headache I don't need.
If each link has to be restored with a file dialog, I might as well do it manually in Access. Better would be to have a table of table locations, and only have to link once. Or put that table in the root of C:\ and leave it there.

I think for now the solution is to put all Access, front & back ends, on the C:\ drive and use shortcuts on D:\ I'll come back to this in the future, as it is too much work now, much of which I don't understand.

Thanks for all the effort put into this.

[DonRayner]

Hey OB;

After re-reading your problem, I think that the answer that you are looking for is the linked table manager. You can find it at Tools - Database Utilities - Linked Table Manager.

When using it put a check in the box on the lower left corner entitled "Always prompt for new location" Then click the "select all" button followed by by "ok".

You will then be prompted to navigate to the location of the database containing the back end files.

[DonRayner]

I had assumed that your computers were networked and that the backend mdb was located on one of them. Thats why I said to use the IP address for the path in the table link.

If they are you can get the computers IP address by going to a command prompt and typing "ipconfig /all" In the list that shows your IP address for the computer will be there.

So
\\ComputerName\ShareName\FolderName\FolderName\xxx .mdb
would be the same as
\\IP Address\ShareName\FolderName\FolderName\xxx.mdb

On my computer (It's called Bart on my home network) I have a folder shared out as Music, if i had a backend database called MyMusic.mdb the UNC path to it would be

\\192.168.0.106\Music\MyMusic.mdb
or
\\Bart\Music\MyMusic.mdb

If you use the same logon username and password on both computers you can also use the default administrative share to map to your directory with out having the directory shared out.

\\Bart\D$\Music\MyMusic.mdb

D would be the letter of the drive and the $ is what hides it from showing up when browsing the computer across the network. Thus it's a hidden share.

[OldBirdman]

You will then be prompted to navigate to the location of the database containing the back end files.

I didn't know about that, but it doesn't quite get there. If I have 40 backend tables, it wil prompt me for each, with a dialog with no memory. Each one has to start from scratch. There is not "They are all together" option.

What I really wanted was an option group to select that the tables were 1- In same directory as forms; 2- In sub-directory of forms; 3- In directory same level as forms named | |; or 4- Prompt me for directory. Not to be.

Although I have a home network = "Birdman", it is to share internet connection, printer, and sync files. Usually the laptop is not used unless I am away from home. In which case the desktop is off, the heat, light, and stereo are off, and the doors and windows locked.

As I said, I'm going to make both computers appear the same to Access. Then the Windows sync routines can copy these programs between the machines without any complications. You see, the data in the tables change, but I also "Improve" the forms and code as it pleases me.

Beyond this, I don't understand what you're trying to get at with the second post, starting

I had assumed that your computers were networked

I won't tell you what I don't understand, 'cause I don't want to know the answers. I don't think it applies to me.

Thank you again for your efforts.

[FishVal]

Linked Table Manager Macro

[OldBirdman]

I don't see much difference between this last link posted and what has been presented previously.

All of these ideas are for a one-time or infrequent re-establishing of the links. To sync a laptop used away from home with a desktop used at home is a different thing. Because one computer is running XP, one Vista, the default file structures are different. The new machine has 2 hard drives, so the drive letters are different. This means re-establishing many links maybe twice per week.

To completely automate, I would have to have an Access table of table names to be linked. This will have to be in a fixed, known location that exists on both machines. The front-end also, so I could update the table that tells me where the tables are. That there is sometimes a 3rd machine involved makes it worse.

Another possibility is an .INI file for each project. I have never worked with this type of file, but each project would need one, and they would have to pe kept up to date also.

So far, the easiest and probably the least prone to errors is to put all my tables in a location that exists on both machines. C:\_OldBirdman comes to mind. Nothing else will try to use that name, and the underscore will cause it to list first in the File Manager.

[NeoPa]

Do you have a master copy of both your FrontEnd & BackEnd databases (on one of your PCs)?

Do these databases get copied across to various other people's PCs?

If the answers to both of these questions are "Yes", then I suspect your own solution of storing these files in a consistent folder on all PCs is a thoroughly workable one.

There are various other (and more complicated) potential solutions, but these may not be for you. They do require a deeper interest than you appear to have in the networking side of things, and, although they match the original question, may not be required in your case.

I would recommend storing it in a folder off the root of your C: drive however. Cluttering up root directories is not generally a good plan. Something like C:\Access\ as an example.

Let us know if this suits.

[OldBirdman]

You are right, I have no real interest in networking at this time. I personally have 2 machines (maybe 3 if I count the one that is failing). My companion has one, and only uses a few of my programs.

Windows XP uses a different file structure than Vista, and the default user folders have different names. Anything in these folders can never match.

The new laptop intends that I do not use the C:\ drive, but that all my data be on D:\ but if I do that, nothing can match as D:\ on the other possible machines is the CD Drive.

So now I have a folder "_OldBirdman" on all machines. I've never used that name for anything else except forums, so no conflicts. Leading underscore(_) puts it first in file manager.

Vista Sync (to keep files syncronized) doesn't work between XP and Vista computers, but Microsoft SyncToy does. This program will copy any changed files to the other computer, if (and only if) both are not changed since the last Sync.

So, yes, I've gone as far as I want to with this, at this time.

[NeoPa]

@OldBirdman
Sounds good OB. If you're good then we're good :)

I'll just point out another item though, for the benefit of anyone else reading the thread.

Using Computer Management (found in Administrative Tools) you can select Storage / Disk Management and change the drive letter used for your CD or DVD drives.

Using SUBST, you can also make up another virtual drive from an existing folder.

These tools can give extra flexibility if required.

[ADezii]

I've just performed a cursory reading of this Thread so forgive me if I do not interpret this correctly. Couldn’t a Table exist in each Front End with the Table Names to be Linked as well as their Absolute Paths. It would then be a simple matter to programmatically Delete the existing Links, then re-create them reading the proper values from the Table. Am I oversimplifying, or have I missed the point altogether?.

[DonRayner]

@NeoPa
You can also get to Computer Management by right clicking on the My Computer Icon and selecting Manage.

[OldBirdman]

Couldn’t a Table exist in each Front End with the Table Names to be Linked as well as their Absolute Paths

Yes, it could, but that absolute path would have to be the same on all computers involved. If that were true, then the table would not be needed, because if the absolute paths were identical, Access would intepret the links as valid, so it would not have to be re-linked.

Using Computer Management (found in Administrative Tools) you can select Storage / Disk Management and change the drive letter used for your CD or DVD drives.

Using SUBST, you can also make up another virtual drive from an existing folder.

I see that I could move E: to F: then D: to E: so I could create a partition named D: which could exist on both of my machines. I could then put my back-end tables in a fixed location on D: and as long as I didn't use the Documents folder or other pre-defined folder on either machine, I could always have the tables at a fixed name, for example D:\OldBirdmanTables.
Is there an advantage of doing it this way, instead of putting the tables in the root directory of the C: drive of any machine, any configuration, any operating system?

[NeoPa]

@OldBirdman
As a system administrator, I avoid two things (related to this question).

1. Putting anything in root folders that aren't necessary there.
2. Putting data on the system drive if you have multiple drives.

1. The root folder of any drive is involved in every file-open access, or any access where the location of a file is sought or searches are done. Adding a single file won't make searching in the root appreciably slower, but when you consider how many operations it affects, then keeping things out of there starts to seem more sensible.
2. On similar lines, if you have multiple drives, then having a dedicated system drive can help with performance. The less data clutter you have in there slowing down accesses the system needs, the less performance degradation you will have (at least from that cause).

I don't know how important you would see these issues, but now you know what it's about, you can decide for yourself whether to take or leave.

I can certainly see that using the C: drive for your situation would be the most straightforward (although an OldBirdmanTables folder would make management easier too).

PS. Possibly irrelevant, but I always set any CD / DVD drives to letters starting from R: whenever I build a PC. That way the early drive letters are available for use when necessary.