Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    151
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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.

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Usually you an you avoid this problem by using a full unc path rather than using drive letters.
    e.g. \\servername\fullpath etc
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    151
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.

    [attachment=86802:unc.gif]
    Attached Images Attached Images
    Regards
    John



  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Mark, can you please explain the procedure(s) you use to store the path to the backend in the registry?

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by grovelli View Post
    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.
    Francois

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    I'm a bit late in thinking of this, but this free Data Source utility might help you understand some of the concepts.
    Wendell

  9. #9
    New Lounger
    Join Date
    Dec 2009
    Location
    Redmond WA
    Posts
    14
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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: \\MyServer\Databases\SampleData.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
    Willie McClure
    www.datarim.com

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Jerusalem, Israel
    Posts
    708
    Thanks
    0
    Thanked 1 Time in 1 Post
    here is some code from Dev Avish, that I modified to use a registry setting. I always appreciate his code and am very grateful
    Attached Files Attached Files

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    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.

  13. #13
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by John Hutchison View Post
    Usually you an you avoid this problem by using a full unc path rather than using drive letters.
    e.g. \\servername\fullpath etc
    Which will tend to slow your data access down, perhaps quite dramatically!

  14. #14
    2 Star Lounger
    Join Date
    Jan 2009
    Location
    Rye, Victoria, Australia
    Posts
    151
    Thanks
    8
    Thanked 0 Times in 0 Posts
    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

  15. #15
    New Lounger
    Join Date
    Dec 2009
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •