Database "splitting"... some questions and concerns

[June7]

Let me summerize in steps.

1. Delete Misc module and Menu form.

2. Change code in Login form to:

        CreateObject("Scripting.FileSystemObject").CopyFile _
            "your folder path\AutoCopy\Frontend.accdb", "c:\Local", True

and

    'DoCmd.OpenForm "Menu", acNormal, , "UserInitials='" & Me.tbxUser & "'", , acWindowNormal
    MsgBox "Open Form Here"

3. Set Login form to open by default with database options.



4. Change version number in table to be different from Local copy label on Login form if you want to trigger copy action. Whenever db is revised the label caption must be changed in the master copy before copying to Autocopy folder. In other words, the table value and master copy label must agree, but the table must disagree with copy in Local folder to trigger update.

5. Run Compact & Repair, while pressing shift key to prevent execution of db settings and code when it reopens.

6. Copy master file into Autocopy and Local (just so it has the revised copy code, hopefully never again) folders.

Steps 3 through 6 most likely apply regardless of what auto update code and process you adopt.

[skydivetom]

June7 -- attached is the updated database. Maybe I just need more coffee or caffeine in general. Looking from the outside in, it's not working for me. Let me recap to ensure what I'd like to accomplish.

Setup:
1. Have "Frontend" on LAN (here simulated on C:\AutoCopy)
2. Have "Data_be" also on LAN
3. Copy "Frontend" to, e.g., user's desktop

Process:
- If both Frontend version are the same, user opens database (no message/warning)
- If system admininstrator update FE on server and changed version #, then message pops up indicating "Different Version" and then automatically downloads new version.
- New version is opened

Right now, if I merely change the version # of the server's FE, the local FE show me the number but that's it. It doesn't copy over the new file. That is I may have added a new form or new report in the server FE. Local copy that has those new objects.

Really like how "AutoUpdater" does the process but it doesn't work unless on LAN, so I can't test it right now. I'm open to using another process but it's pointless if it won't work for me.

Again, maybe I need more coffee... or a simple modified example file w/ your proposed mods would go a long way.

Thanks,
EEH

[June7]

Path to Autocopy looks incomplete. Why is there not even drive letter?

Crud! I forgot that code in Menu form removes value from ComputerName field in Users when that form closes. However, this should not really affect the login process. This feature is so I could identify what computers were connected to backend. Sometimes systems remained connected and then I could not edit backend or run C&R and I had to track down.

[skydivetom]

Isladogs - still hoping to receive feedback on AutoUpdater. Thank you in advance!

[June7]

Review step 4:

4. Change version number in table to be different from Local copy label on Login form if you want to trigger copy action. Whenever db is revised the label caption must be changed in the master copy before copying to Autocopy folder. In other words, the table value and master copy label must agree, but the table must disagree with copy in Local folder to trigger update.

[isladogs]

I won't have time to look at it. Sorry.
As the updater you supplied is fairly similar in function to mine and even has identical table names (tblLocalVariables), I suggest you try relinking my updater tables to your own app

[skydivetom]

isladogs -- thank you... I'll give it a try. Are all 11 modules required? If not, which modules are the ones needed for the copying process?

Tom

[isladogs]

IIRC all 11 modules are used...otherwise they wouldn't be there!

However you can disable some parts of the code and then remove the related modules if you wish.
For example if you don't want automatic form resizing, delete the line ResizeForm Me and remove modResizeForm.
If you don't want to save version info to the registry, disable that code and remove that module.

Suggest you try it out with all code intact however

[skydivetom]

Thanks... apologize for another follow-up. May I ask where I should set the file path for either LAN FE and BE?

[isladogs]

Thanks... apologize for another follow-up. May I ask where I should set the file path for either LAN FE and BE?

I think that was aimed at me but as you seem to also be working with June on her approach it may be best to be explicit in future

I hadn't checked the SDAUpdater for almost 3 years so this was a good opportunity to update it to v1.22
The attached version includes corrections to API declarations and improved functionality in modResizeForm but it now has an extra required module modNavPane. That's now 12 modules!
The code in the splash form hasn't changed at all

At some point I will probably create a proper demo version which is simple to adapt but unfortunately I don't have time now

In answer to your questions

1. The Updater needs to be placed in the same folder as the local FE and another copy in the network upgrade folder along with the network copy of the FE

2. You need to relink 2 tables
a) tblLocalVariables - in the local copy of the FE
b) UpgradetblUpdaterVersion -alias for tblUpdaterVersion which is in the copy of SDAUpdater you will need to place in the network Upgrade folder along with the network copy of the FE

3. Table tblLocalVariables contains the name and path of the local FE and the path of the network upgrade folder.
As the BE path is set in the FE when relinking tables, this isn't required by the Updater
The same table also includes several items not relevant to you such as school name

4. This utility was designed to update a FE application called School Data Analyser (SDA.accdb) in the same folder
Replace all references to SDA / School Data Analyser with your own application
You may also wish to replace the logo in the SDAUpdater utility with your own if you do end up using this

Do read the accompanying PDF as well
Hope that helps

[skydivetom]

isladogs -- thank you so much for the detailed follow-up. There was no attached file in the latest post though.

[isladogs]

Sorry about that. I've now added it to the previous post

[skydivetom]

Thanks for posting v1.2.2. Still trying to figure out the setup; however, when attempting to open tblLocalVariables (Step #2), I get an error (see attached) given the SDA only includes a link to the table but not the actual table. So, I cannot set the path to the local FE.

This thread/post has become extremely long and I'm still not making any progress on this topic... didn't anticipate this topic would become so frustrating.

[June7]

Step 2 is changing table links with Linked Table Manager or code (such as code in my original db). Then open the table link and edit data and also make any other appropriate code changes. Then copy the master frontend to Autocopy and Local folders. Now run the local copy.

BTW, need to also relink tables with my db. I did state in post 14 that table links have to be reset. This would be true for almost any split db you download. Some might have code in an install procedure to accomplish but usually it will be a manual task.

[isladogs]

As June said, you need to use the linked table manager to change the links