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\S hareName\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\Acces s Programs\Xxx.md b
C:\My Documents\Acces s Programs\Xxx-Tables.mdb

Computer #2 (Laptop) Vista/Office2003 - data is on D:\
Documents\Acces s Programs\Xxx.md b
Documents\Acces s 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\Subfol ders\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\Acces s Programs\Xxx.md b
C:\My Documents\Acces s Programs\Xxx-Tables.mdb

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

data is on D:\
Documents\Acces s Programs\Xxx.md b
Documents\Acces s 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\Folder 1, 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\Folde rName\FolderNam e\xxx.mdb
would be the same as
\\IP Address\ShareNa me\FolderName\F olderName\xxx.m db

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\My Music.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.