Efficacy of MS ACCESS use of linking tables to SQL vs periodically updating SQL

I have a MS ACCESS DB (2007) created to quarterly capture “Performance” data on Public Health objectives/Measures to be statistically compared with a Target Number.
This is a relatively small DB.  –No more than 40 users periodically providing data on no more than 200 Measures.  The DB sits on a PH’ exclusively shared LAN’ server.
The main demand is to provide very sophisticated dashboards on the information provided.  In this regard we have decided that another product that we own, IBM’ COGNOS, can do the best job.
For COGNOS to use the data I am importing the ACCESS tables into SQL.  --Then from SQL to COGNOS.
Question #1:  should I just quarterly update the SQL file, or simply link the 25 ACCESS tables to SQL?  –Advantages/Disadvantages.
Question #2:  if the best decision is to link the tables, should I do the ACCESS “split” -tables --from Queries, Forms, Reports?
Question # 3:  After perhaps doing the above, is there a further advantage to giving the DB to the users as an “ACCDE” file?
willjxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric ShermanAccountant/DeveloperCommented:
I'm not clear as to your strategy above but if the overall objective is ...

>>> The main demand is to provide very sophisticated dashboards on the information provided. <<<<<

Then I would move the data tables to SQL Server and connect to an Access front-end to build your dashboards.  Very powerful combination IMO.

Since you are going to use IBM' COGNOS ... not clear as to why you still need to maintain Access.

ET
0
Jeffrey CoachmanMIS LiasonCommented:
Yes spanning 3 separate apps always presents challenges...

Just FWIW, ...COGNOS states that it can intergrate with MS Office
http://www-03.ibm.com/software/products/us/en/cognos-microsoft-office/
0
willjxAuthor Commented:
Unsatisfactory -Non-Expert- answers to the posed series of question.
0
Jeffrey CoachmanMIS LiasonCommented:
Ok, then I'll try again, as each of your questions requires quite a bit of explaining...


1. Question #1:  should I just quarterly update the SQL file, or simply link the 25 ACCESS tables to SQL?  –Advantages/Disadvantages.
A1: If you link the SQL tables into Access, it will be simpler to update the SQL tables with the desired quarter's data. This will probably just be an append query.
Something roughly like this:
INSERT INTO YourSQLTable
SELECT YourAccessTable.*
FROM YourAccessTable;
WHERE (Your date criteria here for the desired quarter)

The link(s) will use a certain amount of resources to refresh the data, but you can set the refresh interval to be very long.
These settings can be found under the Access Advanced Settings:
refresh settingsA split db will minimize whatever slight hit in resources this causes, (...see question two...)
Also, if you are only linking to one or a few SQL tables, this effect will be negligible...
(But this will also be determined by the size of the table, the number of fields, the network hardware and topology, ...etc, so this is a separate discussion)

2. Question #2:  if the best decision is to link the tables, should I do the ACCESS “split” -tables --from Queries, Forms, Reports?
A2 : Whether you have links or not, splitting the database is always a good idea.
The less activity you have on one database file the better.
If the DB is unsplit (a single db file with tables, forms, queries, and reports), a user at one location can crash the database, thus possibly corrupting the entire file.  
In a split architecture, if they crash, ...they will only crash on their machine or front end file, ...the data on the backend will probably remain intact.  
This would be the main concern for your system here, especially being tyat you are likning to
See here for more info on why splitting an Access database is a good idea:
http://www.techrepublic.com/blog/10-things/10-plus-reasons-to-split-an-access-database/

3. Question # 3:  After perhaps doing the above, is there a further advantage to giving the DB to the users as an “ACCDE” file?
If you do split the db, giving users an .accde file has 3 main advantages.
1. An .accde file will not allow the end user to make and design changes to the DB.
2. An accde file will also "compile" all the code, so the accde file will typically be smaller than the corresponding .accdb file
3. The code will not be visible, ...making this file more "secure" than an .accdb file.

I hope I have answered all of your question here succinctly.

JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
willjxAuthor Commented:
Thank you.
--really has a solid impact on my decision process.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.