Really problematic db. I don't know where to start

[omegaleph]

Hi, I'm new here. I'm not sure if this is the right forum for this, so let me know if it isn't. I started a summer job in May, taking over as the database person here. The database is not in good shape.

There are extra 'garbage' tables, I've heard the code is not very good (I'm not that experienced in VBA) etc. Moreover, its a shared database over a network that is not split into a back and front end.

So there are problems weekly. For the past months, I've just been doing band-aid solutions. By this I mean going to back-ups or trying to find the error etc. Obviously, this is not optimal.

I was wondering what the best way is to "start fresh" without losing the data, forms and (for now) code. I tried splitting the database but got an error but maybe starting fresh can change that.

Just a related question: one thing I have to do a fair bit of (often in the context of going to backups) is importing Access tables to SAS and then exporting them back again to a different db. Would/Could this cause corruption?

Thanks for your help,
Greg

 

[RuralGuy]

Here's a good article on Splitting the db and it is the best way to protect the data in the tables. http://allenbrowne.com/ser-01.html
What error did you get when you tried splitting before?

 

[omegaleph]

Hmm. I tried again to see what the error message was and it worked this time.

So I now have the regular ____.mdb file and the ______ be.mdb file. The former is the program, and the latter is the data. I then ran a compact/repair on the program, and it reduced it from 60 to 11 Mb!.

So I had done this on my desktop with a copy of the database, for testing. For the real thing, the idea would be to put the back end/data on the shared drive and give everyone a copy of the program/front end on their computer? Or just a shortcut to it, so they are still accessing the network?

In terms of updating... if I make an edit to the program (i.e. forms), will that be transferred to everyone's copy? And if I wanted to change data (i.e. via SAS like I mentioned) I'd simply have to do this to the back end?

 

[MSAccessRookie]

In my short experience with Access Shared Databases, I have determined that it is safer to give everyone their own copy that is linked to the back end as opposed to sharing a single copy via Shortcuts on the Desktop. There have been issues with the users that had shortcuts.

Changing the data can be in two parts. Indeed you will load the data from the Back End and replace the existing Back End.. You may also be able to take advantage of SQL on the back end via SQL procedures /Functions (like T-SQL for MS Server), or shared views to the data.

 

[RuralGuy]

...For the real thing, the idea would be to put the back end/data on the shared drive and give everyone a copy of the program/front end on their computer? Or just a shortcut to it, so they are still accessing the network?...

Everyone *must* have their own copy of the FrontEnd; preferably located on their local computer.

...In terms of updating... if I make an edit to the program (i.e. forms), will that be transferred to everyone's copy? And if I wanted to change data (i.e. via SAS like I mentioned) I'd simply have to do this to the back end?

You will need to supply everyone with an updated copy of the FronEnd after you make the changes. Bob Larson, our Super Moderator and new Access MVP has a utility that makes this very easy.
http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx You may also want to look into a "Kick-'em-Off" routine to keep everyone out of the BackEnd while you work on it.
http://www.accessmvp.com/JConrad/accessjunkie/kickoff.html

 

[omegaleph]

Thanks very much. I'll work on implementing this once I find a bug-free version of the database.

 

[RuralGuy]

Great! Let us know if you need any further assistance.