Newbie now ready to start the show - autoexec suggestions - ?

[robsworld78]

If you split the database and have it on a server or move the backend file this is handy.



1. Create a new module and call it "settings" or whatever, doesn't matter and add the following code.

Public Function backendfiles()
On Error GoTo backendfiles
DoCmd.OpenForm "YourMainMenuForm", acNormal
Exit Function

backendfiles:
DoCmd.RunCommand acCmdLinkedTableManager

End Function

2. Create a macro and call it "AutoExec", select "RunCode" and type "BackEndFiles()" as function name.

Now when you open the database if the form you have in the code doesn't open it will popup the table link manager so you can browse to where the file is on the computer or server. Just make sure the form that gets open when the autoexec runs has a record source and is looking at the backend file. This is what causes the error to happen, the form opens looks for data in the backend, can't find the data so it causes the error which will open the table manager.

Hope that helps

[David618]

Thanks for sharing all of that - I appreciate it! Good advice. I'm not quite at that stage yet (to "go public") but am getting closer. I imagine there will be occasions to either clean something up or make an "enhancement." I assume I would do that in the developer copy and replace what's out there? What about the updated data from the old copy? Would I simply import those tables into the new and improved version?

[June7]

A split database means the data is in one file and user interface is in another. There is no 'replacing' or importing data. Only the frontend is on each user machine and only the frontend is replaced to update for design changes.

[robsworld78]

No problem, and yeah as June7 mentioned you only need to change the front end. The backend files only has tables in it, the frontend file only has queries, forms and reports. When you link the backend to the frontend the tables show up in the frontend with a + sign beside them.

If you ever add a new table to the backend you need to make sure you link that new table as well. If you make changes to the tables in the backend you don't need to relink them as long as you don't change the name of the table.

There's many reasons you should split the database and one is for updating purposes. If the data was in the frontend you would have to stop all operations until you made the update, plus i find it hard to import/export the data in the backend. Sometimes it comes up with new id numbers and if tables are linked using them it can cause issues.

Always remember to keep your developer copy seperate, I find it helps to have it named different than the working file. When you make enhancements always make a copy of it and then do those steps to make it look pro. Once I accidentally did those steps on my developer copy and had to revert back to a backup as I couldn't get back in.

One other tip for security is when your in the vba editor go to tools, then properties, click protection tab and check "lock project for viewing" and add a password. Now if someone is able to get past everything else that still can't get to your vba code to make changes.

You can also convert the frontend to an executable file so nothing can ever be changed including form design. I have noticed issues using the runtime version of access with an executable only file though.

[June7]

Actually, the linked tables show an arrow symbol to the left.

[robsworld78]

Actually, the linked tables show an arrow symbol to the left.

Oops, my bad, I checked but on my big screen tv from 20 feet away it looked like a +