Access 2007, Need to distribute a form while keeping the database on a server.

views:

answers:

Access 2007, Need to distribute a form while keeping the database on a server.

So here is the gig, I have this inventory access database that I am trying to seperate the form logic from the database itself; having the database on the server while the form is distributed to users.

I know how to seperate the database to a "front end" and a "back end" but my question becomes " is that all I need to do? Send the users the front end with the form and call it done?"

How do I ensure the database can still talk to all instances of the form?

Help, or at least a point in the right direction, would be great! Been trying to figure this out for the past week.

That is basically it. You will probably need some code to check the linked back-end tables and re-link them if any are missing. I like to keep a small set-up table that lists the expected tables and location and a start-up form. The start-up form checks a few things and either loads the menu form, if all is well, or a form to find the back-end database is all is not well.

Remou 2010-09-14 23:01:31

+1 A:

See the "Splitting your app into a front end and back end Tips" page for more info. See the free, for basic use, Auto FE Updater utility to make the distribution of new FEs relatively painless

Tony Toews 2010-09-14 23:12:54

Thank you for the links. I can never have enough info. =) First time using S.O. and I am glad it wasn't what I was expecting.

syntaxrigger 2010-09-16 13:13:15

I don't want to sound like I'm here just for the points because I'm not. But do be sure to upvote the answers of those who have helpd you.

Tony Toews 2010-09-16 21:41:30

+1 A:

Based on your question "How do I ensure the database can still talk to all instances of the form?" it seems to me that you fundamentally don't understand how Access/Jet/ACE works.

The database (back end) doesn't do any "talking" -- it's just a file. Each user's PC does all the data manipulation, and as long as the linked tables in each user's front end has the right connect string, it will load the data from the shared file on the file server.

It's all "pull" -- no "push" as there's no server process running on the server to send anything back to the users.

David-W-Fenton 2010-09-16 05:16:07

I agree with you, I don not fundamentally understand how Access/Jet/ACE works. Thanks for the input. So when I put the back end on the server I just have to make sure the file is shared on each computer that has the front end?

syntaxrigger 2010-09-16 13:10:50

Not quite. You need to ensure that each of the users has read/write/create/delete permissions to the share on the server. The create/delete permissions are required becomse of the LDB/LACCDB locking file that the Access database engine (Jet or ACE) requires.

Tony Toews 2010-09-16 21:40:40

"make sure the file is shared on each computer" -- no, you share it only on the file server, and any workstation that can access the file server will be able to access your back end.

David-W-Fenton 2010-09-16 22:56:44

@Tony: DELETE is not *required*, though many consider it desirable. If you remove DELETE permission on the folder for users, then none of them can accidentally delete your back-end data file. This means the LDB files are never deleted, which returns you to the way Jet 2.x worked, i.e., leaving the LDB behind when the file was exited. The only time that causes a problem is if the LDB file gets corrupted. I usually handle that by having a few users be in an NTFS adminstrators group, which has DELETE permission. Those users are trusted to not delete the back end!

David-W-Fenton 2010-09-16 22:58:24

...adding, you can't remove delete permission on the back-end data file, because those permissions will be lost the first time it's compacted, since compact always starts by creating a new file, and then deleting the original file and renaming the new one. With no delete permission on the file, you'll never be able to compact. But if you have an admin user who has delete permission on the file, the new compacted file won't inherit the permissions of the original, so you'll be back to square one. This is why removing delete permission of the folder is the best protection.

David-W-Fenton 2010-09-16 23:00:55

ansaurus

tags:

views:

answers:

Access 2007, Need to distribute a form while keeping the database on a server.

related questions