I made a .MDE file, but now I want to change some code--how?

[ruthcali]

i am using Access97 in a Novell environment.

All the users (about 20) are using the database via our Novell network. (the databases are NOT on the users C: drive).

I was getting many locking errors, (couldn't save, currently locked by user admin on PC..) so i converted the database to a MDE file.

it solved all my locking errors (so far, so good at least). but i need an easier way to make changes to that MDE database.

i read about creating a back end database that stores all the tables.

my main question is: how much will it slow down my already kind of slow database?

i know the perfect solution would be to put the front end on the users's hard drives and keep the back end table database on the server.

the problem with that is that i am making code and design changes to the database at least twice a week. so i would constantly have to bug the users to get the newest copy of the database to download to their C: drive.

what do you gurus recommend in my situation?

in summary:
i am happy not having locking errors.
i am NOT happy having to copy and paste tables from the MDE database to my MDB database every time i want to make code or form changes.
i am NOT happy slowing my database down further by separating my tables onto a back end database.
i am NOT happy bothering my users twice a week to get the newest MDE copy every time i make a change (if the front end in on their C: drives.)

thanks for your opinions.
ruth

 

[Durkin]

I'll answer your summary:

i am happy not having locking errors.
I'm happy for you. LOL
i am NOT happy having to copy and paste tables from the MDE database to my MDB database every time i want to make code or form changes.
If you separate your data from your code this won't need to be done.
i am NOT happy slowing my database down further by separating my tables onto a back end database.
I'm not sure how much of a performance hit this will cause as I have always done this. There are a couple things to consider. Firstly, if you put all the code on the client machines you are freeing up space and processor time for the backend to use and secondly, it is a better design, especially if, as you say, you change the code regularly. If your database is slow you may need to compact it (regularly) or optimise the queries you are using.
i am NOT happy bothering my users twice a week to get the newest MDE copy every time i make a change (if the front end in on their C: drives.)
There is a dos command called xcopy which has a /d switch. In other words you use it like this: xcopy filename1 filename2 /d. This copies filename1 to filename2 only if the date of the source is greater than the date of the destination. If you put this command in the users' login script it will do a download every time you change the code without them even noticing. I'm not sure if you can put dos commands in Novell login scripts but even if you can't I'm sure there's some way you can do it.

Anyway, there's almost always a way around things, it just takes a bit of trying things out.
Durkin
alandurkin@bigpond.com

 

[ruthcali]

Hi,
thanks for your complete response. i appreciate your advice.

when you write: "If you put this command in the users' login script it will do a download every time you change the code without them even noticing."

which login script do you mean? the Novell login script? or when they log into the Access database?

Until i get the courage to mess with Novell, do you have any statistics on how slow it will make my database if i create a back end database and leave all three databases (the .mde, the original .mdb and the _be.mdb) on the network drive?

i compact my database every morning. Because every morning, i delete and then import 5 new tables, so if i didn't compact, my database would be huge! It is 11MB now.

As for optimizing my queries, what do you mean?

thanks!
ruth