Access 2007-Splitting a Database
in [General]
|
Prev: Is It Possible to Control Access Warning Pop-Up Message Boxes?
Next: Filter/Search button not working in Access 2007
From: SueW on 15 Apr 2010 12:42 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
From: Daryl S on 15 Apr 2010 13:03 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! -- Daryl S "SueW" wrote: > 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
From: Tony Toews [MVP] on 15 Apr 2010 15:09
SueW <SueW(a)discussions.microsoft.com> wrote: >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/ |