Delink Backend for Copy?

[jimbrooking]

I have a split database. The path to the back end database is saved in a table. I want to let the user change the location of the back end so it can be moved, e.g., from its install directory to a LAN directory.

The back end tables are linked when the DB is opened and unlinked when it's closed, and all this seems to work fine.

I wanted to add to the "change back end" code the ability to make a copy of the current back end into the new location if it's not there already. I have written code to map a UNC path spec to a drive letter (if necessary), check that the target drive is valid, and build the specified subdirectories (if necessary).

At the point in the code where I know I have a valid destination directory, I do the delink procedure, which apparently works. (When I refresh the database window the linked files are gone.) But a FileCopy to copy the back end from its current location to the new one fails on an error 70 - permission denied.

When I check the existing/old back end directory I see an .ldb file for the back end. So something in Access's little brain thinks the back end is still in use. Does anyone have a clue about how to break this connection with the back end so I can copy it? Or an alternative suggestion about how to copy the back end?

I guess I could create an empty database in the new location and copy all the backend tables into it, but that seems like a lot of extra work.

Thanks,
Jim

 

[jimbrooking]

Guess I need to "fess up". One of the linked tables was being used as a recordsource to an open form, so even though I delinked the form, Access knew(?) thought(?) the table was still apt to be changed, so kept the .ldb file around. Turns out the form only needed one value from the linked table, and that only to display it, so I retrieved the value at form open, and cleared the recordsource for the form. With this change, the .ldb file went away after I delinked all the tables.

Thanks to all who may have scratched their heads about this. I guess user error would be the resolution.

Jim

 

[Oldsoftboss]

I've just posted a question on back end paths http://www.access-programmers.co.uk/forums/showthread.php?s=&postid=142995#post142995
and was interested to read that you keep the path to your backend in a table. How do you tell the front end to use this path to find the back end? If I could establish this, I think I can work the rest out
Tahnks in advance
Dave

 

[jimbrooking]

Hi Dave,

The back end contains only the tables the user could modify, not all the tables. The database is a "commercial" product for organizations to do a self-assessment of the "in progress" American regulations regarding the privacy and security of medical records (see http://www.nchica.org/HIPAAResources/EV/ for background). The back end was created manually, not by the Access wizard. One of the functions in the database is a "reset" function that clears all the user-entered tables to the as-distributed state. Any table that is reset got put in the back end. There are, of course, many other tables used by the database for managing the internal operation, documenting, and supporting the user interface, but these can't be changed by the user, so are retained within the front end.

So the table containing the path to the back end is actually one of the tables retained in the front end. (Sounds more like a discussion of farm animals, doesn't it? )

If you email me directly I'll send you the code behind the form that actually checks the user-entered path. I learned a lot from this exercise!

HTH,
Jim

 

[sambo]

Jim,
I am interested in your method of "re-setting" the table links in a front end database through code.

Much like you, I will be releasing a project for use outside of my own building. I do expect, however, to have to make revisions after the initial rollout. For this reason, I would like to be able to quickly and easily be able to make "front end" revisions and subsequently "re-connect" the links as they should be.

Example..
I have 9 linked tables in my front end database (by definition these receive their data from the back end database located @ \\SomeServer\SomeSubDirectory\backEnd.mdb

I also have about 15 reference tables that will be included in the "front end".

If a situation arises where I need to do some development and rollout a revision change, I will need to be able to quickly and easily redistribute the "front end" to all users and have the linked tables "re-connect" upon first use of the new Revision.

So, when I develop the Revision changes, the front end will be linked to a back end server in my building, but... I would like the tables to be linked to a back end server as defined by a table value (or something similar).

Psuedo...

For Each tbl In TableDefs
  If tbl.doLinkBoolean = TRUE Then
     Link tbl.Name To  \\Server\SubDir\backEnd.mdb;Table=tbl.Name
  End If
Next tbl

How would I go about doing this?

 

[jimbrooking]

I'm attaching a 77KB zipped database that has (I think) all the code for linking and delinking tables, and for setting and checking a new back-end data file location. I extracted it from our production system, and unfortunately it doesn't quite work "out of the box". (It "Works" if frmGreeting causes all the tables to be linked, then opens frmMainMenu.) However, you should be able to read the code and see what should be happening.

Basically:

--frmGreeting is supposed to look in table tblLANLoc to see where the back end is, and link to the tables listed in tblLANtbls
--frmMainMenu just "has to be there" to open frmDataLocations. Also, frmMainMenu unlinks all the data files in its OnClose event
--frmDataLocations accepts a new location for the data file and does a lot of checking to be sure the given link is valid.
--modAttch has most of the code to link tables, map network drive, etc., and StringFunctions is for a dhReverse function.

Sorry the demo doesn't work, but I've been staring at it for an hour and must get on to other things.

Also, please see my comments in response to Dave's posting, linked above, regarding "the next release".

Jim

 

[Oldsoftboss]

Since I posted I have done a little more research. I saw in another post that there was an example in the solutions Db (A2000). Managed to strip down and am in the process of getting it to work.
Thanks for the reply and the sample Db
Dave

 

[Oldsoftboss]

Sambo, this is where I'm at. By no means complete or debugged but might give you a start.
I intend to call the code from an autoexec macro eventually, but for the purpose of testing I just have a command button.
You will see in the tables one linked table. You will have to replace this with one of your linked tables (Just import).
In the Module under Public Function CheckLinks() you will have to edit the line
Set rst = dbs.OpenRecordset("AssetNo")
with the name of your linked table.
HTH
Dave

 

[Oldsoftboss]

Did everything except include the zip

 

[jimbrooking]

I'm reposting the demo database I sent a couple of posts above - think it works as it should now. From looking Dave's, it appears that we have taken a slightly different tack, but both seem to be workable, far as I can see. I think the distinctive feature in my approach is that if the back-end file is on a drive the user doesn't normally have access to, e.g., one that requires a userID and password, my code will attach the back-end and link it to a local drive letter for the duration of the session, then de-link the back end tables and unmap the drive letter when the session ends, i.e., the Main Menu is closed.

Dave - your Dim statements of rs as DAO.Recordset (instead of just "Dim as Recordset") cured the problem I was having last night - thanks.

Sambo - you now have a choice!

Jim

 

[sambo]

Thanks for all the help...
I like the idea of firing in an autoexec macro everytime the db is opened.

Glad to see I wasn't the only one losing sleep over this.

Here is my take

 

[Oldsoftboss]

As you can see, my code is just hacked up code from the Solutions/Northwind example databases
Have tidied them up a bit more since that post ...
Dave

 

[Cosmos75]

As you can see, my code is just hacked up code from the Solutions/Northwind example databases
Have tidied them up a bit more since that post ...
Dave

Olssoftboss,

Will the tidied up code work in Access 97? I noticed everyone posted an Access 2000 version.

 

[Pat Hartman]

Giving the user a means to move or find a manually moved back end is a good thing but I don't understand what you are attempting to accomplish by linking and unlinking the tables with every open and close. The link to a table is only "live" as long as a recordset for that table is open so linking/unlinking the tables would seem to serve no purpose.

 

[Cosmos75]

As you can see, my code is just hacked up code from the Solutions/Northwind example databases
Have tidied them up a bit more since that post ...
Dave

I would like for the hourglass cursor to appear while it's testing out the link.

Public Function CheckLinks() As Boolean
' Check links to the Northwind database; returns True if links are OK.
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
    
    Set dbs = CurrentDb

    'Set cursor to hourglass
    DoCmd.Hourglass True

    ' Open linked table to see if connection information is correct.
    On Error Resume Next
    Set rst = dbs.OpenRecordset("tblUsage")

    ' If there's no error, return True.
    If Err = 0 Then
        CheckLinks = True
    Else
        CheckLinks = False
    End If

    'Set cursor back to normal
    DoCmd.Hourglass False
    
End Function

Would this cause any problems?

 

[Cosmos75]

I just tried it and didn't see an hourglass appear even though it takes about 20 seconds to come back and tell me the link is OK. Should I add those lines somewhere else?

 

[Oldsoftboss]

Would it make any difference if you called the hour glass before the function

DoCmd.Hourglass True

Call CheckLinks

DoCmd.Hourglass False

????
Just a thought
Dave

 

[Cosmos75]

Oldsoftboss,

Tried your suggestion. It works OK if the Front and Back end are linked correctly, but doesn't work when the link is incorrect.


Back to the drawing board!