Access 2007-Splitting a Database

  • Thread starter Thread starter SueW
  • Start date Start date
S

SueW

Newbie to this. I have built a database that multiple users will use
throughout the work day. I have read the database should be split -- tables
on the back end and forms, queries, reports on the front. I'm not a
programmer and my network administrator has asked that I look at SQL Express
to use to split the database.

Would someone please lead me to education to understand all of this?

Thank you all in advance.

Sue
 
Sue -

Splitting the database means separate the data from the application. The
back-end is where the data is stored. This can be a Microsoft Access
database or a SQL Server database (as in SQL Express), or another database.
The front-end is the application - the forms, queries, reports, etc. that
make up the user interface. Access is a great back-end for smaller databases
that don't have very rigorous security needs, and is used on a LAN. Other
back-end database like SQL Server or Oracle are much more robust databases
with better security and that can handle much more data and traffic. There
are other great features of these back-end databases including views and
stored procedures for those who need them. You often need a DBA to manage
these databases - you may already have SQL Express at your company, and the
DBA may be able to help you with getting things set up.

You can find a lot of information on SQL Server Express on the Microsoft web
sites. Access even has some tools to help split the database, which will
create two databases from the one you created. BACK UP before you do
anything!!! The back-end will be in Access. Access also has tools to help
you upsize to SQL Server, but there are a lot of caveats. This only works
with certain combinations of Access and SQL Server (e.g. Access 2003 won't
upsize to SQL Server 2008, but Access 2007 will). If your database contains
table or field names with special characters (#, space, etc.) or reserved
words (Date, Name, etc.), then you will have a much more difficult time
upsizing.

Check out the Microsoft web sites, and find out what your company already
has for databases.

Good luck!
 
SueW said:
Newbie to this. I have built a database that multiple users will use
throughout the work day. I have read the database should be split -- tables
on the back end and forms, queries, reports on the front. I'm not a
programmer and my network administrator has asked that I look at SQL Express
to use to split the database.

To add to Daryl's very good response. Split first. Get that
working. Then consider upsizing the data, ie the backend database
file, so SQL Server Express. Note that the SSMA is a better tool
than the upsizing wizard included in Access.

Also see my Random Thoughts on SQL Server Upsizing from Microsoft
Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm

There is a tool from the SQL Server group.
SQL Server Migration Assistant for Access (SSMA Access)
http://www.microsoft.com/Sqlserver/2005/en/us/migration-access.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/
 
Back
Top