Different virtual locations for backend database

Topic

New Reply

Hello again, Colleagues All !

Here is the scenario for a little problem I am wrestling with.

The client has a LAN where the backend database sits on a server and each user has a PC where the server appears as drive F:. I do development work remotely and run a copy of the system where the backend is loaded on C:. Two of the managers connect remotely and on their laptops, the server appears as drive Z:. What I need is a VBA editor for the linked tables databases which will automatically amend the drive component of the link depending upon the machine.

I propose to have a table on the front end with a one-field record holding the drive name as text. Then, on startup, the autoexec() subroutine would read the name, modify (or refresh as the case may be) the links and then hand over control to the user. I have some code for refreshing known links, but cannot find anything to actually change thelink specifications.

The front ends distributed to the users are all .mde files, but at present I have to give the managers a .mdb file and then allow them general access to relink the tables themselves. This is, of course, a fairly substantial security issue (not to mention the possibility that, not being techies, they could compromise the system by making mistakes).

Can anybody advise me, please.

Reply | Quote

Replies

Usually you an you avoid this problem by using a full unc path rather than using drive letters.
e.g. \servernamefullpath etc

Reply | Quote

Usually you an you avoid this problem by using a full unc path rather than using drive letters.
e.g. \servernamefullpath etc

Which will tend to slow your data access down, perhaps quite dramatically!

Reply | Quote

Thanks, John,

I have already received similar advice in another forum. However, what I need to know next is how to populate the links database with the UNC strings. The manual linking method only seems to expose the drive name automatically, and I was looking either for an alternative to enter the UNC or a library for doing it in VBA. Certainly, having the program interrogate the system to see how it has defined the UNC is attractive, because I would not need to maintain computer-specific front-ends.
When I log into my client’s system, i do it through rdp, which takes over a local PC which is already sharing, so it is not terribly visible (though I ssuppose I can look the data up in the admin tools). The problem still remains though, is how to make it visible to the Access link manager

Regards,

Jim.

Reply | Quote

I regularly do this manually using the linked table manager.
In the File Name box just type \ then keep typing the server name. If you have done it before it will autocomplete.

Reply | Quote

Let me explain how I do it.

Basically, I do what John advised you to do; use the UNC! This gets by all the problems you’ve encountered.

When I send a new frontend update to a client, it goes to their server. I then open the db at which time a relinking form appears. It asks for the path to the Backend db (it is stored in registry for automatic recall the next time), then does the relinking. At this time, I set a date/time stamp in a field in a backend table. When a user goes to open their frontend, it compares it to the data/time stamp; if not the same, it won’t allow the user in and tells the user to copy-down the new version (I usually have a .bat file in Startup which does this automatically). When the user copies the new Frontend down to their local drive, it is already linked.

Reply | Quote

Hi Mark, can you please explain the procedure(s) you use to store the path to the backend in the registry?

Reply | Quote

Hi Mark, can you please explain the procedure(s) you use to store the path to the backend in the registry?

Giorgio,
To write in the registry you use the SaveSetting statement
To read in the registry you use the GetSetting statement.
See the help file.

Reply | Quote

I’m a bit late in thinking of this, but this free Data Source utility might help you understand some of the concepts.

Reply | Quote

Jim,

I just joined and found this question. I think you are asking if there is an automated way to link tables.
There are many code samples of routines that check the table links and relinks if necessary.
Typically you would have the back end on a server (say SampleData.mdb).
The front end (say Sample.mdb) would be copied to the local workstation along with a file containing the back end location (Sample.ini).
The sample.ini file would have a line like: \MyServerDatabasesSampleData.mdb
The procedure (mine is called LinkTables) would read this file and check all linked tables connection strings – and reconnect if necessary.
Let me know if this is what you are looking for.

We at Puget Sound Microsoft Access User Group are working on a new location for code samples.

Willie McClure

Cheers!!
Willie McClure
“We are trying to build a gentler, kinder society, and if we all pitch in just a little bit, we are going to get there.” Alex Trebek

Reply | Quote

Hi Willie,
Can you post the code of the LinkTables procedure? Interested to see how you go about reading the ini file and check all linked tables connection strings – and reconnect if necessary.
What’s the url of the Puget Sound Microsoft Access User Group?

Reply | Quote

here is some code from Dev Avish, that I modified to use a registry setting. I always appreciate his code and am very grateful

Reply | Quote

I have a form which opens when the database opens, this form relinks all the linked tables. The linked table names are held in a special table holding the database paths.

This table can be used to hold multiple backend entries. So multiple backends can be linked to and they can be all on different servers.

The table also holds a field that contains which system the record(s) are for, eg it’s a P if it’s to my system (developer), L if its’ the live system.

The point of this field is that depending where the database is opened it will link to the appropriate backends.

In this way i can setup the live systems backend(s) on my laptop as well as my testing backends and i can link to “Live” backend(s) or “Test” backend(s) on my laptop.
I do this for all databases i develop.

Reply | Quote

The volume of interesting advice emanating from this site never fails to amaze me and I am gratified that quite a bit of it originates in my home town (Melbourne, Australia) !

I have tried using the UNC method and it is an elegant solution, but I also think that Paul has a point in that it can affect performance. I suppose the problem is that if you set up the network connection and assign a drive name to the root on the server, the ordinary Windows disk access procedures come into play, whereas if you use the UNC, the network management procedures have to be accessed every time. The system I am working on is pretty slow anyway (I use rdp to connect, so it is a two-stage process to access the database) but it seems to be even slower when using a UNC.

I think that Patt has zeroed in on what I was looking for, where I can store a list of table pathnames in a local table in the front-end, each list identified by the name of the disk drive where the backend database will be found relative to the user’s machine. I presume that looping through the paths and using them as arguments to the VBA DoCmd.TransferDatabase method will be sufficient. If there is an alternative method or there is something else I need to look out for, I would appreciate the advice.

The attractive thing about Patt’s method is that it is actually a pretty good security feature. For instance, one can link the drive names to the user name, so that each user only sees the linking mechanism that is applicable to his/her machines. Also, the front-ends can be distributed without any external links at all, so that if it fell into the wrong hands, the software IP could be misappropriated, but the backend data would be safe.

Thanks also to you WendellB for pointing me to the UIAccess site. I have downloaded their utility program to look at, but as a freelancer doing casual work remotely for a few clients, I think it is more useful for on-site sysadmins, so I will forward the link to the appropriate people. For the same reason (my casual involvements) I would be very wary of editing the registry on client systems, though I am sure that it is another elegant solution and right in the spirit of Windows management.

I will report back if I have any useful insights.

Jim

Reply | Quote

In order to speed up access to back-end databases located on a network, there is a very useful trick you might care to try:
When the front-end application starts, have it open a table on (or query against) the backend database and keep that connection open until the application ends, when you can close the connection. This often helps speed up access to the entire back-end database!

Reply | Quote

When the front-end application starts, have it open a table on (or query against) the backend database and keep that connection open until the application ends

Yes, that is a very good tip. That way, Access won’t have to create, delete or update the .ldb file for the backend as long as you keep the frontend open, and this improves performance.

Reply | Quote

I had a similar issue with a database I was developing. The back end could theoretically be located anywhere, and I would have no way of knowing where it was.

Helen Feddema wrote an Access Archon article for Woody’s Access Watch back in July 2000 which addressed this very issue. Issues 2.13 and 2.14 provide code samples to link the back end wherever it happens to be, and provides a function to delete and re-link the tables if necessary.

Helen has made the column available, with code samples, at her web site. Download Access Archon #63, Working with Linked Tables. I further modified the code to provide a warning on startup if the back end was not found, and force the user to re-link the tables.

I also recently designed an in-house project for our photo processing department, which required the same functionality. As this was only being used internally, and I was short on time, I used the JStreet Access Relinker, which can check for and re-link all tables programmatically. It was able to link two backends (one for data, one for attachments) to the same frontend without issue.

Hope this helps.

Reply | Quote