Conditionally open Access Database based on path verification?

[Xarkath]

Hello,

I have a split database and people seemingly unable to follow instructions. Normally I would email the user end of the document and hide the original but that is currently frowned upon at my company as they insist that we use links not attachments. And I do not have the access/rights to setup a distribution program. This leaves me with sending a link to the user form with instruction to copy the document and use thier individual copies. This seems too complicated for people as they continue to use the original so I am looking for alternatives that will force them to make a copy.

Is it possible to set an OnOpen or OnLoad event to check the path and if the path is equal to the path of the original document to have a message box open to state that the form can only be opened after a copy is made. Then have the "OK" button close the form entirely?

Or does anyone have a creative solution that does not include firing everyone? (I don't have the authority to do that either...)

Thanks

[June7]

Not clear to me what you are doing nor what you need. Copy what 'document'? Use original what? Hide what? Why would path change?

If you are trying to make sure users work with latest version of frontend, what I do is have code in the database that checks for a version number and if the version has changed, the code copies the database to user's local C:\ then closes itself and reopens new version.

[Dal Jeanis]

If you are talking about a "document", for example a word document, that is located on a LAN somewhere, and people using your database are clicking on something that opens the document, thus accidentally saving their modifications to the original, then you can code some VBA to copy the template.... actually I believe you could change the document type to template and your problems would resolve themselves.

On the other hand, if you are using an Access database, and people are opening a form, and using the form puts data into your database, that's just the way that Access works. You could change your form to create a temporary table, and delete the table at the end of use, but that brings up the question of why you're using a database tool for that kind of function in the first place.

If you explain more carefully what the database / document is being used for, then we could give more useful advice.

[Xarkath]

In my frustration it seems that I was not very clear. I have a split database, the back end houses the actual tables and the front end (the "user form") has a buch of stuff related to using and/or updating those tables. The users are suposed to make a copy of the user form (or the front end database) then they are to use the copy they made. So lets say the original(or master copy) is located at x:\databases. The user needs to copy the master to c:\User\desktop. What I was thinking was that maybe I could use an on event to check the path and if it equals "x:\databases" then it would pop a message box stating something like "Copy this to your workstation before opening it" and then have the "OK" button of that message box set to close out of database. But if the path equals anything but x:\databases then it would open normally.

Hope that makes more sense.

Thanks

[June7]

Why on earth would users copy a form???????

You really want them to work with a copy of the db on their workstation. This is the conventional approach for a split design.

As explained in my previous post, I have code in db that will copy revisions to the user C:\ location. I do have to do a manual copy of the db to that location the first time (new computers), after that the code handles the revision copy.

Yes, code in database could pull the path and use that to check against a value. Then it could possibly copy the database to their desktop, and open it for them. I think this will require a login procedure to get the user identity and therefore the paste destination. I have never tried to copy to desktop, but if that's what you really need, I suppose it is possible.

Or you can just open a message that 'slaps' their hands and close the database.

But if you do whatever you can to reduce user frustration everyone should be happier because they outnumber you and who wants to face a mob brandishing 'torches and pitchforks'?

[Xarkath]

I think the word "form" is causing some of the confusion. It is an Access database but I refer to it as the "User Form" because it is designed to look and feel like a form when used. Also, the people I work with understand it better that way. So basically what they see is a database and then the " user form" by which they access and use the database. It is in reality the front end portion of a split database.

As far as where they place a copy, I don't care as long as it is in a place where only that user is likely to access it. I was just using the "desktop" as an example.

Making the database copy itself to the designated user directory would work but A: they would probably be totally oblivious to it doing that and continue to open the master copy every time and B: depending on what permissions are required to make that happen I may not be able to do it. I would be willing to give it a try but I think a slap on the hand with a reminder followed by closing the database will end up being my most viable option.

Thanks

[June7]

Can get the database path with: CurrentProject.Path

[Dal Jeanis]

I don't understand the purpose, though. If they are not supposed to store informatoin, why are you using a database? If they are, why do you need them to create a copy? Something just sounds wonky.

[BleedsOrange]

I think I understand what is happening here. For whatever reason he is unable to distribute the front-end to users directly. The users want to click on a link instead. He instructs the user to follow the link, copy the front-end to their desktop and then open it but the users don't listen and instead open the front-end directly.

Question for the Xarkath: Would placing th front-end within a zip file named soemthing like "COPY ME TO YOUR DESKTOP FIRST" help? Seriously, this can be a simple way to avoid the problem.

From a coding perspective you can definately include a check.... something like:

Dim strCurrentPath As String

strCurrentPath = CurrentProject.Path

If strCurrentPath = "put path to check here" Then

            MsgBox "Please follow the instructions that have" & vbCrLf & _
            "been sent to you.  You need to make a copy of this" & vbCrLf & _
            "file and place it on your local computer before" & vbCrLf & _
            "trying to open it.", vbCritical, "INSTRUCTIONS NOT FOLLOWED"
                    
            DoCmd.Quit
End If

[Xarkath]

I don't understand the purpose, though. If they are not supposed to store informatoin, why are you using a database? If they are, why do you need them to create a copy? Something just sounds wonky.

If they simaltaneously use the same copy they will be competing with each other in some instances. There are some sections that hold tempory data specific to the user until they reach the end of the process at which time any updates are permanently applied to the backend database. I don't know how to explain it clearly but to keep it simple just think of it as sharing = record locks. It is more complicated than that but that is the gist of it. Also, I want the master copy readily availible to me should I need to make revisions.

[Xarkath]

I think I understand what is happening here. For whatever reason he is unable to distribute the front-end to users directly. The users want to click on a link instead. He instructs the user to follow the link, copy the front-end to their desktop and then open it but the users don't listen and instead open the front-end directly.[/CODE]

That is exactly it. I am glad someone can understand my demented thought process... And Thanks for the code! I will post the results once I can try it out.

[June7]

I see your options as:

1. code by BleedsOranage in post # 9

2. procedure I describe in posts # 2 and 5

[Xarkath]

The code from BleedsOrange works perfectly. It was exactly what I had envisioned. I am intrested in the code that can copy new versions to the user's c: drive as it would help to keep the "torches and pitchforks" in the tool shed where they belong. However, I do not have direct access to other user's drives so I would still be reliant on them making a copy and putting into the correct destination for the kickoff.

Thanks for all the help and advice.

[June7]

This thread has my code https://www.accessforums.net/program...ile-34624.html

The code is in Load event of form that opens by default when db opens. There is a label on the form with version number. I have a table with a single record that has the latest version number. If value in table does not match the label then there is a new version of db that needs to be copied to user work station. So the copy action happens from the user side.

I keep my development master frontend in a folder that only I have permission to. I copy edited file into a folder called Install. This is the copy that gets pasted to the user workstation. I change the version number on label and in table just before I run Compact & Repair and then copy the edited db into Install folder.

With this approach, you only need to send new users the email link once. They copy the file to workstation and future updates are automatic by the code.

I physically go to user's workstation to do initial install because I have to change AccessOptions to Enable All Macros and it's easier for me to do than try to instruct users.

The db is saved at C:\ because network limitations won't allow code to copy into subdirectories.

[Xarkath]

Thanks June7, I am going to give this a try.

In my case no less than 50% of my department are remote workers and That 50% makes up at least 90% of the staff using this database. In short, I am not going to do all that driving just to install this for each individual, they are just going to have to follow instructions. Or go back to doing everything manually in a 100+ different Excel workbooks. *Evil Grin* Thier choice.