multi user access

[boumbo]

I have followed this forum advice and i have split the database into FE and BE. Converted the FE into .mde

Now i have emailed the FE/.mde to all users of my department so that they can do data entry.

after distributing, i have modified a report/query. So i recreated the .mde and then i need to re-dsitribute the FE/.mde to my users. please note NOT all users on my network uses this database and I am not a network admin. so i dont have rights to push the file to my users.

what is the best way to do it? i.e update all the MDE on my network when i do a modification.

 

[SOS]

You can use a free auto updating tool. There's one here and another here. In this case, however, you would need to send the file to the users again. But if you set up the auto updater then you won't have to do that anymore.

 

[boumbo]

Is it the same if i do that?

if i create a lot of folders on the shared network

folder 1 => copy of user_FE.mde
folder 2 => copy of user_FE.mde
folder 3 => copy of user_FE.mde
folder 4 => copy of user_FE.mde
folder 5 => copy of user_FE.mde

and then i ask the user 1 to run the mde from folder 1, user 2 to run the mde from folder 2 etc....

Is it the same than running from their own computer/desktop? so if i have to update, i will just copy all the updates to folder 1,2,3,4,5 on the shared network.

I find it easier to update the FE instead using the autoupdate. it is too complicated to use.

 

[SOS]

I find it easier to update the FE instead using the autoupdate. it is too complicated to use.

What's too complicated? For example, you follow Bob Larson's directions step by step in his documentation and it will have you select the master location, the master database and the backend. You click a button and it adds tables where it should. You link the one from the backend to the frontend, then you add the code from the form he has by clicking the copy button and then go paste it in (I believe the form open event, but could be load - read the directions).

From then on all you have to do is keep a copy of your master somewhere for you to work on it. When it is ready you change the version numbers in the two tables and then create your MDE file. You place it in the master location and then at that point (after the initial distribution to users) if a user opens the database and the version numbers don't match, it will notify them, close itself, delete the old file, copy the new file and automatically reopen for them. Simple stuff really.

Is it the same if i do that?

if i create a lot of folders on the shared network

folder 1 => copy of user_FE.mde
folder 2 => copy of user_FE.mde
folder 3 => copy of user_FE.mde
folder 4 => copy of user_FE.mde
folder 5 => copy of user_FE.mde

and then i ask the user 1 to run the mde from folder 1, user 2 to run the mde from folder 2 etc....

Is it the same than running from their own computer/desktop? so if i have to update, i will just copy all the updates to folder 1,2,3,4,5 on the shared network.

Not a good idea. Running an Access database over the network means 100 times the risk of corruption. Just not good to do. Also it means much more network traffic which your network admins aren't going to like.

 

[The_Doc_Man]

OK, I take a different approach that isn't the same as the auto-updater, and it requires you to educate your users to actually READ what they see (admittedly, difficult...). But if you don't like the auto-updater approach and cannot do a push, this will work OK.

On my startup form, I check a version / generation number of the database. I have a FE that contains a generation number hard-coded in VBA in the Form_Load section of the startup form. I have a table in the BE that lists the generation number of the most recent published version. When I update the FE, I make a new table entry in the generation-number table in the BE.

When the startup form opens, I compare the two generation numbers in the Form_Load code. They are structured so that I can determine which generation is newer, if either.

If the BE table says your copy of the FE is obsolete, the startup form displays a big ugly banner (just a garishly-colored label box, really) that says "Please visit the shared folder and copy a new FE file to your local machine." If you have compatible FE & BE generation numbers, you are allowed to open the form without comment.

I have a flag in the versioning table that lets me decide whether the user must update or can run safely with an older FE. It is a simple query to find the most recent generation number for which the "update or die" flag is true. Therefore I have three critical facts - the generation number the BE thinks it should be running, the last generation number for which a new FE was mandatory, and the current generation number of the user's FE copy. Among those three items, I can decide what to tell the user about shutting down.

The decision for the "update or die" case is that if all I did in the most recent generation update was to add a couple of reports but no data structural changes occurred, then the old FE will run with the new BE. But if I made a structural change, then the FE stops the user from running with the old FE by forcing an Application.Quit after issuing an appropriate "Sorry, Buddy, time to update" message box.

To do a further safeguard, that same opening form looks at the database.name property to parse out the device and folder name. If you are running on the shared copy, I again lock you down, warn you to not directly use the DB, and let it Quit the application.

That should give you some ideas.

 

[boumbo]

Thanks.. i downloaded it and trying to use it.

In the documnetation there is only two open file buttons.. i dont understand the third button.

when you split you create a BE and FE... but why would you want to specify another folder where the master FE will be... should it not be in the same path as the BE and FE?? button 1 path should equal button 3 path right?

 

[SOS]

Thanks.. i downloaded it and trying to use it.

In the documnetation there is only two open file buttons.. i dont understand the third button.

when you split you create a BE and FE... but why would you want to specify another folder where the master FE will be... should it not be in the same path as the BE and FE?? button 1 path should equal button 3 path right?

Okay, explanations -

First Button is where you select the Frontend FILE (the mdb or accdb file)

Second Button is where you select the BACKEND file (the mdb or accdb file)

The THIRD button is where you select the FOLDER where you are going to store the copy of the new MDE/ACCDE file whenever you make a new one. It shouldn't really be near your master mdb/accdb file because the MDE/ACCDE file is the compiled file which will get distributed.

 

[boumbo]

can the FE file and BE file be in the SAME folder??

i am nearly there...

When you change the frontend file.. do you need to re-do the mde??

because the file you provide is mdb not mde...


i think there is syntax error

the debug stop

strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")

Okay, explanations -

First Button is where you select the Frontend FILE (the mdb or accdb file)

Second Button is where you select the BACKEND file (the mdb or accdb file)

The THIRD button is where you select the FOLDER where you are going to store the copy of the new MDE/ACCDE file whenever you make a new one. It shouldn't really be near your master mdb/accdb file because the MDE/ACCDE file is the compiled file which will get distributed.

 

[SOS]

can the FE file and BE file be in the SAME folder??

If you mean the FE MDE file that you are going to be distributing then yes, it can.

When you change the frontend file.. do you need to re-do the mde??

because the file you provide is mdb not mde...

Yes, you need to change the version numbers and then create the new MDE which you then put in the master location.

The file has to be the exact same name and extension as is on the user's computer or else everything will bomb. So if the user has an MDE file you need to place the mde file in the place you specified in the Third browse button on the enabling form.

 

[boumbo]

i am still getting this debug error

the debug stop at

strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")

 

[SOS]

Did you remember to go into the frontend file (the mdb file) and go to FILE > GET EXTERNAL DATA > LINK TABLES

and then link to the table that gets copied to the backend?

 

[boumbo]

i debug and only the first line[one in red] does not work.. the one in green works..

' looks up the version of the front-end as listed in the backend
strFEMaster = DLookup("fe_version_number", "tbl-version_fe_master")
' looks up the version of the front-end on the front-end
strFE = DLookup("fe_version_number", "tbl-fe_version")
' looks up the location of the front-end master file
strMasterLocation = DLookup("s_masterlocation", "tbl-version_master_location")

in my backend, there are the following tables:

mytable ==>which is linked
tbl-version_fe_master

in my front end, there are the following tables:

my table ==> which is linked
tbl-fe_version
tbl-version_master_location

i dont understand why the VBA cant locate the tbl_version_fe_master in the backend table...

 

[SOS]

It should work, what value do you have in that table?

 

[SOS]

in my backend, there are the following tables:

mytable ==>which is linked
tbl-version_fe_master

in my front end, there are the following tables:

my table ==> which is linked
tbl-fe_version
tbl-version_master_location

in the frontend you should also have

tbl-version_fe_master

as a linked table

 

[boumbo]

It should work, what value do you have in that table?

1.03 is the value in the tbl-version_fe_master

when i run the front end mdb.. i get this error

run time rror 3078

the microsoft jet database engine cannot find the input table or query 'tbl-version_fe_master'. make sure it exists and its name is spelled correcntly

 

[SOS]

1.03 is the value in the tbl-version_fe_master

when i run the front end mdb.. i get this error

run time rror 3078

the microsoft jet database engine cannot find the input table or query 'tbl-version_fe_master'. make sure it exists and its name is spelled correcntly

So is this table listed in your frontend as a linked table? If not, it needs to be.

 

[boumbo]

in the frontend you should also have

tbl-version_fe_master

as a linked table

ahhh.. i dont have this in my front end .. which step did i miss from the instructions?? i did follow the instruction line by line

how do i link it now....but also i want to know which step i missed.

 

[SOS]

ahhh.. i dont have this in my front end .. which step did i miss from the instructions?? i did follow the instruction line by line

how do i link it now....but also i want to know which step i missed.

To add it to the frontend just go to

FILE > GET EXTERNAL DATA > LINK TABLE

and then browse to your backend and select that table and click OK.

 

[SOS]

The instructions were here:

 

[boumbo]

thanks a lot it works.. i tried it on two pc.. the instruction in red was overlooked...it was not red enough....

i have a last question.. now i am going to distribute it....

the FE_master is stored on a shared network which has been mapped to

G:\Accessdatabase\

but if one used has it mapped on a different drive letter, will it still work?

lets say it is for him/her

F:\Accessdatabase\