Access DBMS

[Dan]

I have an Access database and I just am not happy with using Access as a
backend. I really like the front end that I designed but it is such a pain
when it crashes. It seems that records are always being lost. I thought I
read a post about a version of ms sql that was included with access that is
designed for around 10 users or less. I tried to find this article but I
couldn't. I really want to find an an inexpensive alternative to using access
as the backend. Any help would be appreciated.

 

[Albert D.Kallal]

You can use virtually any commercial database engine for the back end.

There is a free desktop version of sql server included on every office cd.
(this is the so called MSDE). If you create what is called a ADP project,
then you can use ms-access to actually manage the tables, and the sql server
for you.

You can also continue to use a mdb file, and connect to any odbc database
engine (so, you can use the MSDE, but you would need something else to
manage the server, and for the MSDE, or sql server, that choice of tools is
the enterprise manager for sql server. (which you can get for free if you
don't have it).

And, if you might want to check out the express edition of sql server, as it
is also a free download.
you can find it here:

download:
http://lab.msdn.microsoft.com/express/sql/default.aspx

about:
http://www.microsoft.com/sql/express/

So, virtually any odbc database will work with ms-access. That includes
Sql-server, the above free editions, oracle, Sybase etc etc....

However, there does seem to be a "warning" point in your post, and that is
that you find JET (the default data engine) not reliable. I would try and
address this problem first, as simply changing the data engine will NOT
CHANGE your reliability. I find JET very reliable. I have many clients with
3-6 users, and in multi-user mode, the application runs for literally years
without ONE service call, or ANY problems. Mind you, this applies is fairly
small with only about 50 to 60 highly related tables, and the records in the
tables are generally very small (only about 75,000 records in a table). With
such low numbers of users, and such small tables, one would expect any
problems..but I do believe that these numbers are typical of average
ms-access applications.

Also, remember, many commercial products also use the JET data engine. For
example, the VERY popular accounting package called Simply Accounting uses
JET. This means you can actually open simply accounting files with
ms-access. This also means that if JET is reliable enough for a complex
accounting package, then it should suffice for you. So, while moving the
data engine to a server based will usually increase reliability, if you are
not getting reliable operation out of JET now, then likely some other reason
is the source of your problems (poor coding, poor data designs, poor use of
forms events etc.).

So, I would fix, and address those problems BEFORE you attempt to change
database engines.

 

[Dan]

How can I find out what is the source of my problems. The database is really
simple with 11 tables with 5,000 records in our main table. I do insert
records with my code through things like:

strSQL = "INSERT INTO [AccountsReceivable] ...
CurrentDb.Execute strSQL, dbFailOnError

or run action queries that are in my on form events.

Now what kind of events be considered poor use of forms events. I do have an
event that re-querying the form and that does hundreds of other commands that
are form events so I am guessing that this would be the most likely cause.

I guess that I kind of exaggerated when I say that it always looses records.
It probably does it only once every several months. (it never really looses
them it just changes the field values to random symbols.) I really want it to
be completely reliable and we are now to the point that we can't have it
loosing information so I thought it was time to transfer it over to a more
reliable DBMS.

 

[Albert D.Kallal]

Now what kind of events be considered poor use of forms events.

Well, a often seen example is people use the lost focus event of a control
to run code when the control is updated. However, using the lost focus is a
bad choice in this case, since the lost focus event will ALWAYS fire,
regardless if the data in the control was changed. Then, the user starts to
write all kinds of funny, and un-reliably code to work around this problem.
The correct solution (and correct event) is to use the before update, or
perhaps even the after update event of the control (which does not fire if
the user moves through the control).

My point here is that writing a bunch of code to work around using the wrong
event tends to make the code more complex, and the resulting application is
more "buggy". Another common mistake is seeing a form required to force a
update, when in fact the correct thing would be to use the me.refresh event
which forces a disk write...but does NOT loose your current record position.
I suppose I could write on and on for pages here, but suffice to say that a
lot of bad, and un-reliably applications are the result of using the wrong
approach, and the wrong events on a form.

I guess that I kind of exaggerated when I say that it always looses
records.
It probably does it only once every several months. (it never really
looses
them it just changes the field values to random symbols.)

The above may very well be the result of a network problem. Or, perhaps
users are just doing a ctrl-alt-del, and shutting down the application
incorrectly.

Also, is this application multi-user, and is it split? (if you are
multi-user, you need to split your application, provide EACH workstation a
copy of the front end - preferably a mde). If you don't split your
application, then you don't get reliable operation.