MSysObjects and/or Path to Linked tables (1 Viewer)

[jamie_b]

Hi all,

(I have searched and found many articles on this, but none of the solutions fits my needs, unless I am misunderstanding a few)

Due to software licensing restrictions, my (Access 2007) development pc is NOT (may not be) connected to the network.

The user pc's are all connected to the network.

The network pc's only gets Access 2007 Runtime.

The Front-end accde will be located at C:\mydbFE\theFE.accde on each user's pc.

The Back-end will be located at X:\mydbBE\theBE.accdb (network share).

Currently (during development) the Front-end and Back-end is at C:\mydbDEV\FE and C:\mydbDEV\BE respectively - on my development pc, and the linked tables are pointing to C:\mydebDEV\BE.

Now that I'm ready to distribute the database to the user pc's, I obviously need the Front-end's Linked Tables to point to X:\mydbBE\theBE.accdb

Where does Access2007 store the path to the Linked-Tables ?
Is there a way I can change this path manually before making the accde ?

I noticed that the path to the linked tables appear in the (hidden) MSysObjects table, but I do not want to mess with it until I know what the solution is.

Any thoughts from you on this issue is welcome.

Thank you kindly.
Jamie.

 

[CJ_London]

you are safer using the linked table manager but if you need to modify using vba then you would modify* the tabledefs rather than msysobjects although you can use the latter to determine which tables need relinking.

*Modify actually means
copy tabledef to new tabledef
change path in new tabledef
delete oldtabledef
save new tabledef

 

[jamie_b]

you are safer using the linked table manager

So what you are saying is, IF I was to use the linked table manager function, then I can only do so from my development pc, and choose the option "always prompt for new location".

This means the user will be prompted for the backend location, right ?

This is exactly what I DO NOT want. I do not want the user to be prompted for the location, so it has to be an automated function.

Am I assuming correctly that I should be looking at using VBA code to link the tables at start-up of the front-end ?

And that there is no other way to manually change the path before I make the accde ?

Thank you.

 

[spikepl]

Make a partition on your pc and call it X. Put your backend there, link to it. The end.

 

[jamie_b]

Make a partition on your pc and call it X. Put your backend there, link to it. The end.

Or just a usb drive with it's drive letter changed to X: - and the appropriate folders.

So easy.

Absolutely Brilliant mate ! Thank you big time.

This is all I required. I tested spikepl's solution on network and it works as expected.

Thanks again.
Kind regards,
Jamie.

 

[CJ_London]

Spikes solution will work but just a warning about using mapped drive letters - sometimes they can be lost or changed. you would be better to use a server path name like

\\myserver\mydirectory\...\mydbBE\theBE.accdb

Otherwise your only option is to use VBA.

Here are some links that will help - since you know where the BE is located you can take out the prompts so the user is unaware

http://access.mvps.org/access/tables/tbl0009.htm
http://blogs.office.com/b/microsoft...atically-relink-microsoft-access-tables-.aspx

 

[Galaxiom]

if you need to modify using vba then you would modify* the tabledefs

*Modify actually means
copy tabledef to new tabledef
change path in new tabledef
delete oldtabledef
save new tabledef

Why do you feel there is a need to copy the tabledef first?

It is quite easy to change the existing tabledef links. Loop through the TableDefs collection and modify the Connect property string with Replace() after testing for the appropriate tables. Skip those named starting with MSys.

Then run TableDefs.Refresh and RefreshDatabaseWindow.

 

[CJ_London]

Why do you feel there is a need to copy the tabledef first?

Just the way I've always done it - I picked up some code years ago and never felt the need to review whether or not it was the best way

 

[jamie_b]

Hi,

I have tested this http://access.mvps.org/access/tables/tbl0009.htm and with some changes to suit my needs, it works as it should.

The above is obviously the better (and fail-safe) option, as pointed out by CJ_London:

a warning about using mapped drive letters - sometimes they can be lost or changed

However, on my network, in the users' Logon Script, it will always map the particular network share to X: - and I have not had issues with this in 10years.

In the event that X: would ever change to Y: for some reason, then I would be the first to know. The user's permissions does not allow them to remap network shares.

So, to summarize, I would go with this:

CJ_London is correct - and I would advise other developers to go with this 'fail-safe' option of relinking with code behind a "startup-splash-screen".

The pure simplicity, albeit not 'elegant', solution from Spike is simple, direct, (faster?) and works for MY needs in an environment where I have administrative control over everything.

I hope this thread can help someone else with similar scenarios.

Jamie.