Using an access database from another machine

[gregorg]

hi i have written a database for someone in access. They now want to have another user on the same wireless hub use the database at the *same time* as the first user ?

I don't know how to do it or how it can if it can be done ? I also thought of writing some forms in visual baseic (.net) to access the access database from the other computer. I don't know if you can do this either ?

I'm guessing that I have to rewrite the database in SQL server and visual basic, If i want to have two users access the database at the same time. ?



gregorg.

 

[DCrake]

Have you split the data base? This is the first thing to do then give each user a copy of the front end. However I would warn you that using Access databases over a wireless connection can be fraught with corruption issues. I Access looses its connection for a split second whilst performing a write action there is a liklehood that the table will get corrupted. There is more discussion on this subject on the forum.

David

 

[gregorg]

Thanks

Gregorg

 

[Max D]

Btw, it is not obvious, that several users can use Access database simultaniously. Period.

No locks, no special setup, no client-server. Just put it in shared network folder.

Possibilities of database corruptions? It depends of wireless link quality. Usually it doesn't hurt much. But it would be nice to make backups time to time.

 

[gregorg]

but you still have to do linked tables ? so they are using the same data, or do you just have 2 users using the same program file ?

 

[Max D]

You don't need to do anything, just let 2 users use the same Access database file from network share.

 

[boblarson]

You don't need to do anything, just let 2 users use the same Access database file from network share.

That is BAD, BAD, BAD advice. gregorg do NOT listen to that. If you do, you will be playing Russian Roulette with your frontend. You can expect corruption to happen (NOT IF, BUT WHEN).

Max_D - Advice like this will get you removed from the forum, as it is VERY, VERY, VERY BAD PRACTICE.

gregorg - see here for more about split databases.

 

[Max D]

Boblarson, I have one question for you. Whats the difference?

What will prevent data corruption if you split database?

 

[boblarson]

Boblarson, I have one question for you. Whats the difference?

What will prevent data corruption if you split database?

Corruption of the data will occur if you do not split the database.

Corruption of the frontend will occur if you have multiple users over a network using the same mdb/mde/accdb/accde file, AND data corruption is possible too, but less frequent.

For example, when I went to work for a large healthcare company, they were using a split database with a shared frontend (100 users) from a network location. They were suffering frontend corruption once to twice a week. When I got there they asked me to look into it. I made them put a copy of the FE on each user's computer and they have not had a corruption issue since. And that was 3 years ago.

 

[Max D]

Frontend is not the most valueable part.
I mean what will protect the backend, which holds the data?

 

[boblarson]

Frontend is not the most valueable part.
I mean what will protect the backend, which holds the data?

True the Frontend may not be the most valuable part. BUT, keeping corruption from happening at the frontend IS extremely important too because it can cause

1. Downtime (costly)

2. Extra Support costs (costly)

and more.

If each user has their own copy of the frontend then if one user's FE corrupts, it doesn't bring down the whole system. If you are using one shared FE, and it corrupts, then all of a sudden NOBODY can do their work. I would say that's pretty valuable.

 

[Max D]

So what will protect the backend in this case?

 

[boblarson]

So what will protect the backend in this case?

The backend is on the server and is as protected as an Access database backend can because it is not included in the frontend. So, just the fact it is split and has nothing but pure JET objects (tables) in it is the protection.

Each user's frontend copy can be linked to the backend and it normally will suffer no ill effect. If you have a single shared frontend linked to a backend, it will not normally corrupt the data, but a single dropped packet while a user has the shared frontend open can corrupt the FRONT END and potentially be irrecoverable.

With each user having a copy of the frontend, then if a single user suffers a network disruption, then their copy of the frontend might be hosed. But that will be easily fixed by copying a new one from the master location. Other users, in the meantime, can continue to work without ever knowing that the other user had a problem.

That is why running a SINGLE shared frontend from a server location is a bad idea. It can be costly, in terms of downtime, developer time in supporting the troubleshooting, and then also in replacing the frontend. A single user having a problem is better than 100 users all suffering from a problem at once.

 

[Max D]

So, just the fact it is split and has nothing but pure JET objects (tables) in it is the protection.

Here is the core problem. It is not the complete protection - there still is the chance dropped packet will hurt tables when writing occurs.

Indeed, database splitting decreases the chances - lets say to 1/4 or so, but doesn't eliminates the risks completely. In order to keep data protected one need to use SQL Server or terminal servers solution. Is it worth to touch this for small database 2 people will use? Surely no.

So why you blamed me in all the sins as if I proposed someone to wash PC in a bath?

 

[boblarson]

So why you blamed me in all the sins as if I proposed someone to wash PC in a bath?

Because you said it was okay to run a single access FE from a server for multiuser use and that simply is NOT the case; period. Yes, it will WORK to do so, but it is NOT something anyone in their right mind should do. ALWAYS when used in a networked situation, a COPY of the frontend should be provided as a SEPARATE FILE to them. They should not open the same mdb/mde/accdb/accde file. I keep saying this and it doesn't seem to get through to you. Your statement

You don't need to do anything, just let 2 users use the same Access database file from network share.

is just simply WRONG.

 

[Banana]

Bob already gave you a link about splitting database, but I think this link gives more explanation on how things get corrupted.

As the author asks, nobody would think to share a single Word application on a shared folder, yet for some reasons, there's that idea that it doesn't apply to Access.

Furthermore, just because it didn't eliminate the risks doesn't mean it is waste of time. Splitting and using one's own files does wonders in *minimizing* the impact should things go horribly wrong. Instead of taking down the whole thing down the crapper, the corruption can be constrained and that I think is the case for needing to split the database and distribute individual copies of front-end to each users.

Finally, you know the saying: Just because you can, doesn't mean you should.

 

[Max D]

Mkay, Gregorg now has enough info to count pros and cons of each approach and make a decision.

 

[MSAccessRookie]

Mkay, Gregorg now has enough info to count pros and cons of each approach and make a decision.

I hope that he realizes that most of the pros lie in the splitting of a Database, and not in sharing a single Database file on a server.

 

[boblarson]

I hope that he realizes that most of the pros lie in the splitting of a Database, and not in sharing a single Database file on a server.

Well, if not then it will be the school of "hard knocks" as I think eventually, if you have worked with them enough, you either "get it" before you have problems, or you end up doing it the right way because you had problems. Kind of like the old FRAM oil filter commercials - "You can pay me now, or pay me later." Meaning you can either do the preventative thing, which costs a LOT less up front, or you can pay the big bucks when it all comes crashing down.

 

[Max D]

nobody would think to share a single Word application on a shared folder

A lot of people would like to, but its impossible. Most apps doesn't allow several people to edit 1 file simultaniously.

I hope that he realizes that most of the pros lie in the splitting of a Database.

Most of the pros lie in using SQL Server.