February 6, 2012 at 2:24 pm
Hi all,
I'm not a DBA by any stretch of the imagination, but I've inherited an access 2007 DB at work. The access DB has an access FE linked to tables in the access BE which is stored on a network share. Yes, many issues with this setup, including the fact that now more than 3 people will be accessing the DB at the same time.
Using the Microsoft SQL Server Migration Tool I've migrated the BE tables to SQL Server 2008. The interesting thing is that when I link the access front end to the tables now in SQL a second set of tables are created in the access front end. From the little I could find on the subject, it appears that the tables in the front end are used to update the tables in the SQL server and vise versa. This bloats the front end quite a bit though.
I deleted the tables that were created in the front end and have been doing testing. So far it appears that any changes I make to data through forms on the front end are written to the tables on the back end. In short, it seems to work without the tables that were added, but I'm concerned that I may not be seeing something that isn't working.
Does anyone know the purpose of those tables that were created in the front end and if it's, or will be, an issue?
Thanks!
February 6, 2012 at 2:39 pm
pls confirm are the the FE tables now linking to SQL or the "old" BE (.after you made the table deletions) ?
out of interest...what are your "many issues"
concurrency?
performance?
data integrity?
iirc...a properly designed Access FE/BE should perform reasonably well with more than 3 users..
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 6, 2012 at 4:48 pm
J Livingston SQL (2/6/2012)
pls confirm are the the FE tables now linking to SQL or the "old" BE (.after you made the table deletions) ?out of interest...what are your "many issues"
concurrency?
performance?
data integrity?
iirc...a properly designed Access FE/BE should perform reasonably well with more than 3 users..
The Access FE (local) tables are no longer. They were deleted from the access FE and the access FE is now linked to tables on the SQL server BE.
Issues with the old setup:
Having a BE on a network share wasn't the greatest for performance or security, not to mention everyone had to have the share mapped to the same drive.
Articles I have read have stated that any more than 3 users and you start risking performance issues. We'll have closer to 10.
Better security over who can have what type of access to the tables in SQL vs. have all of the tables and their data sitting out on a share that can be accidentally deleted.
The original design wasn't the greatest, and part of my taking this over includes going through and normalizing the data.
February 6, 2012 at 6:54 pm
>>Does anyone know the purpose of those tables that were created in the front end and if it's, or will be, an issue?
I have never seen this and no I don't believe this is an issue.
>>Yes, many issues with this setup, including the fact that now more than 3 people will be accessing the DB at the same time.
You can create multiple fronts-ends. I will usually have a master and then a simple batch script that copies the master to user specific front-ends.
>>The original design wasn't the greatest, and part of my taking this over includes going through and normalizing the data.
Check out using DSN-less connectivity. It will allow you to run code within you app and 'relink' to your all SQL server tables. If you are making frequent changes to the server side tables you won't have to manually relink.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply