Need archtectural advice. DB server or File server.

[swingingming]

Hi, for 5 weeks, I finished my mdb project. Thanks to all you guys.
Now, I would like to put it on a server then 5-6 people can share it. I
heard about the splitting back-end database, put it on a file server or
DB server. Then install the front-end on every client machine. And
clients must be able to connect to the backend remotely. Should I use
something like SQL server/MSDE or just a file server?

If I use MSDE, will I rewrite all the code from DAO to ADO? That is the
thing I would not like to do. Maybe it's poor design in the first
place. But what options do I have now?

I really don't have any clue in this. Any advice will be appreciated.
And I assume there will be other technical problems such as the network
setup, going through router, ...

Thank you.

[Rick Brandt]

"swingingming" <mi******@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hi, for 5 weeks, I finished my mdb project. Thanks to all you guys.
Now, I would like to put it on a server then 5-6 people can share it. I
heard about the splitting back-end database, put it on a file server or
DB server. Then install the front-end on every client machine. And
clients must be able to connect to the backend remotely. Should I use
something like SQL server/MSDE or just a file server?
The reasons to move to SQL Server are scalability, security, less chance of data
corruption, and the need for a VERY large database. If none of these are
particularly worrysome for you then a file-shared MDB should be fine for the
number of users you are describing.

If I use MSDE, will I rewrite all the code from DAO to ADO? That is the
thing I would not like to do. Maybe it's poor design in the first
place. But what options do I have now?

Moving to a server back end does not mean re-writing in ADO. At its most basic
you replace your local table with links to server tables and the app should
(mostly) work as is. Further design changes would be necessary to fully take
advantage of the server database engine, but that can be done on an on-going as
needed basis.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[swingingming]

Thank you for your reply, Rick. I have more questions because I am
still new in the network world. Please bear with me :)
1. In a file server, file locations will be something like
"\\servername\dbname\aa.mdb", right? I will do a research on how to do
that. Now, say 2 people want to access the same backend data, will
there a conflict if they both write to the database at the same time?

2. In MSDE, the above problem should be handled by the server database
engine, I assume. And do I link to the server tables almost the same
way as a file server?

I think I probably will choose option 2.

Thank you Rick.

[Larry Linson]

"swingingming" wrote

1. In a file server, file locations will be
something like "\\servername\dbname\aa.mdb",
right? I will do a research on how to do
that. Now, say 2 people want to access the
same backend data, will there a conflict if
they both write to the database at the same time?
You will link the tables in the shared back-end database, and Jet database
engine's locking mechanisms will handle the locks... generally, the nature
of applications is such that rarely are two users trying to update the same
record at the same time. If you use page-level locking, the chances are
higher, but I've never encountered any serious difficulties using the lowest
level of locking ("No Locks" or "Edited Record").
2. In MSDE, the above problem should
be handled by the server database
engine, I assume. And do I link to the
server tables almost the same way as
a file server?
MSDE is a capacity and processing-limited version of Microsoft SQL Server,
it must be separately installed, and does not contain the very easy-to-use
administrative tools that Microsoft SQL Server does. At the very least, for
your development you should license the Developer Edition of SQL Server,
which does include the adminstrative tools.

Yes, once you have somehow set up the tables in MSDE, linking is similar,
but the Connect String will be different, of course, and in earlier versions
of Access, we found it necessary to programmatically re-create the TableDefs
for Linked Tables to change them, rather than use the RefreshLink method.
I think I probably will choose option 2.

Unless there is some good reason you have not explained, I think you would
be letting yourself in for a great deal of extra work and stress, if you do.
You will still have to know Access and how to use it, but you (or some
database administrator) will have to know not only SQL Server, but also the
MSDE limitations to SQL Server.

Larry Linson
Microsoft Access MVP