RE: database options with SBS 2003 R2
- From: dave d. <daved@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Mon, 12 Nov 2007 10:33:02 -0800
Wow, these are are helpful answers. Thank you all.
This answer is the most helpful and relates to my situation with helping a
private school and a medium sized non profit. And it sounds like this
respondent has experience with this.
It was doable for the customer to design their own access database in a peer
to peer environment - but now I must come up to speed with helping them
properly migrate their database to a client server environment. The goals
include stability and reliability, data integrity , ease of backup. improved
network performance. etc.
Yet, they "saved money" and purchase the standard version of SBS.
Question: How does "MSDE" come into play in this discussion? Is this also an
upgrade option in this scenario? i.e. jet vs MSDE?
I like the idea of taking things in small steps starting with working with
what they already have and improving it in increments.
Thank you.
"501c3help" wrote:
MS Access includes an Upsizing Wizard that will let you migrate the data up.
into SQL Server and then reconfigure the Access MDB to use the SQL tables via
ODBC for data rather than the previous native Access tables.
If you don't want to deal with a version of SQL Server for the data, you
should consider splitting your Access solution into separate data and front
end MDB files.
In this configuration, all your data is in one file, and all of your forms,
reports, queries, etc, are in another. This lets you segment the data into a
file of its own that can be more easily managed and protected, and also sets
up a configuration whereby user interface pieces (forms, reports, etc) can be
added and modified without having to touch the data file.
1. Make a backup copy of your Access MDB.
2. Create a new MDB (call it data.mdb) and then copy the tables from your
current MDB (call it frontend.mdb) into data.mdb.
3. Delete the data tables from frontend.mdb
4. Place data.mdb on the network drive from which it will be accessed.
5. Open frontend.mdb and go through the Import data menus, selecting
data.mdb as the source and Attach Tables... (rather than import) as the
method. Be sure that your are accessing data.mdb via the same drive path
that your users will use as this path will be saved in frontend.mdb and
that's where frontend.mdb will be looking for the data.
6. The tables will now be listed in the Table object browser of
frontend.mdb, but keep in mind that these are just pointers. The data is
still actually sitting in data.mdb.
7. Unless the users typically have direct access to the data tables (which
hopefully they don't in a well designed app), right click each table name in
the frontend.mdb Tables object browser and select "Hide". This will keep
users from directly opening the tables and dinking with data while by-passing
the application logic in the forms.
8. Finally, under Database Tools, select "Make MDE". This will create a
file called frontend.mde. The MDE file is a fully functioning access
application file but locks down all of the design features so that users
cannot go into the table definitions, form and report designers, etc and
change things. They can create queries however so that they can run custom
queries as needed.
9. Put frontend.mde out on the network for your users to access.
"dave d." wrote:
Hello all,
I have a new custumer that has purchased a new server with SBS 2003 R2
standard version.
Their primary lob application is an Access 2003 database. the database is
smallish and sql server is probably overkill.
What options can I suggest or consider for integrating their existing
database into the SBS 2003 R2 standard platform / environment? Ofcourse one
of the important goals would be to have the data stored on SBS for backup
purposes.
Please advise.
Thank you
- Follow-Ups:
- Re: database options with SBS 2003 R2
- From: bass_player [SBS-MVP]
- Re: database options with SBS 2003 R2
- Prev by Date: Re: Undo Application Data Redirection?
- Next by Date: Re: smtp virtual server questions
- Previous by thread: Re: database options with SBS 2003 R2
- Next by thread: Re: database options with SBS 2003 R2
- Index(es):
Relevant Pages
|
