Access Alternative?

[jschlapi]

We have an Access DB shared by 4-5 people across 2 servers. If 1 person is using it, the performance is great; if 2+ people use it at the same time, the response time of data input really drags.

Is there any other medium we can use or some way we can alter our DB? (for reasons I don't want to get into, using a split database is not an option).

Thanks for your opinions.

 

[Gingertrees]

If split database isn't an option, this is a "ticking timebomb" situation. Unsplit DBs are designed for single-user set-ups. Or a single (well-informed) user at a time. After that, data integrity comes more into question. With the set-up you're talking about, full scale crash WILL happen - it's just a matter of time. But due to the set-up of Access, the worst part is YOU PROBABLY WON'T KNOW IT HAPPENED RIGHT AWAY!

Seriously, you gotta get a different set-up PRONTO.

 

[ranman256]

you may need an adjustment.
are users pulling large datasets from the tables?
If they are simply editing single records, then this should be done on THEIR side. (FE)
Viewing large datasets can only be done on the BE, but I cant see many users pulling data that often, and at the same time.
The method I adjusted was , if a user wants to edit (or create) 1 record, it 'downloads' to the frontend for editing.
for new, it is all entered on the FE.
Then SAVE is clicked, it either updates the backend (edit) or appends new records. (a microsecond) and does not drain the main db resources.

 

[xenou]

@jschlapi: How can an Access database be split across two servers? This sounds very suspicious. Also be aware that if you are accessing a remote server (not on your LAN) your problem may be more with networking than with Access per se. Also that would be dangerous because a lost connection could leave your database in a corrupt state. Not good.

As far as Access alternatives, there are numerous choices. Any good server database would be fine. Probably sql server express would work admirably here, as long as the storage limitations aren't a problem. Also Oracle Express, MySQL, MariaDB, sqlite, Firebird, PostGre, and I'm sure others I don't even know about. With the open source DB's you'd need be sure you get an ODBC driver to be able to handle the connections. For SQL server express and oracle express you could use ODBC or use (I think) providers that are created specifically for connections with those types of databases.

@gingertrees: I'd go one further and say all access databases should be split. Although I have rarely found a database to go corrupt (on my one-user databases -- count in 7 years of use is "once"), it is still better to protect your data by putting it in a backend, since if corruption does occur it would mostly like be in a front end object like a form or report. An exception might be if the data can easily be refreshed, or if you are okay with restoring from a backup in the event of a problem (as I often am so I don't actually follow my own advice here). And oh yes, we always have a good back plan, folks, don't we ...?

 

[Smitty]

As far as Access alternatives, there are numerous choices

I've had pretty good luck with Azure, which is essentially cloud-based SQL Server. It works great with Access, but it's cloud so a lot of IT dept's are hesitant to use it.

And oh yes, we always have a good back plan, folks, don't we ...?

Always!

 

[Gingertrees]

I'd go one further and say all access databases should be split. Although I have rarely found a database to go corrupt (on my one-user databases -- count in 7 years of use is "once"), it is still better to protect your data by putting it in a backend

Oh, splitting is definitely the smarter plan even in a one-user environment - then again, in a one-user environment, the user may have the awareness to not change relationships and/or inadvertently overwrite data (which of course Access doesn't bother to tell you when you do, if you haven't set up miles of fragile security!!). These sorts of problems with data integrity can be even more dangerous than a corrupted database, because of the assumption that everything's ok when it isn't.

IN SUM: With each additional user, risk of exponential entropy increases...

 

[HiTechCoach]

We have an Access DB shared by 4-5 people across 2 servers. If 1 person is using it, the performance is great; if 2+ people use it at the same time, the response time of data input really drags.

Is there any other medium we can use or some way we can alter our DB? (for reasons I don't want to get into, using a split database is not an option).

Thanks for your opinions.

As you can see everyone thinks the first thing to do is split the database.

I agree that splitting the database is a must. Even is the front end in the same folder as the back end on the server. I have never seen a reason to not split the a database.

In my experience, what is causing the slow down/drag in performance when the second user opens the database is normally due to poor design of the forms. You may need to recreate the slow forms as unbound forms.

Splitting the database still may not be enough if the forms sand other stuff are not properly designed. My suspension is there are design issues. I split every database from the start to ensure hat the database is designed for multiple users.

You may also want to consider using remoteapp or terminal services.