Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SSMA for Access Vs ADP (Access Data Project )

Posted on 2009-05-20
5
Medium Priority
?
417 Views
Last Modified: 2013-11-29
I inherited an existing membership database for an association of doctors.  The database is comprised of a frontend and backend, both of which are Access 2003 MDBs. I want to update the backend to SQL and redesign/modernize the frontend.  The DB is quite large in the number of objects that it contains, as not only does it track membership, but it is also an events DB as the association puts on numerous CE event and meetings throughout the year. I have come to the conclusion that it might be better to start from scratch, and redesign it from the ground up.

I have both Access 2003 and 2007 on my PC which I can use to achieve this.

Question: Should I create both front and back ends as MDBs to the point that I am satisfied all is working well, and then use something like SSMA for Access to migrate to SQL (which I believe creates links from the frontend to sql), start with a mdb frontend and sql backend and work from there, or go the ADP route (which I have no experience with)?  Or is the a better solution that I have not listed?
0
Comment
Question by:CALAOMS
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 18

Accepted Solution

by:
jmoss111 earned 1500 total points
ID: 24435105
You can prototype in Access and move the backend to SQL but nornally I develope the backend in SQL. You can use SSMA which does a fair job with queries but won't do your updates. SSMA will create links to the backend but i don't use linked tables. You can use an Access front end with a SQL backend which is my preference, or you can use ADP's which are deprecated in 2007 and will probably go away in a release or two, IMHO. I prefer sticking to .mdb because you lose functionality with .adp and people seem to have problems getting up to speed with them.

Regards,

Jim
0
 

Author Comment

by:CALAOMS
ID: 24435161

Although I am capable of creating tables and working with sql by writing scripts, I prefer to use GUIs whenever possible, if it makes the job easier and saves time.  How do you go about creating your tables and working on the sql end of the DB when designing with Access.
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24437341
I use SSMS
0
 

Author Comment

by:CALAOMS
ID: 24443579
Jim,
I have not used SSMS except to play around with it, I guess i will have to get up to speed on it.  My experience has mostly been with cleaning up Access databases that were created by others and using SSMA to migrate them to sql, which has worked out quite well for me in the past.  
Also briefly, what is the draw back to using linked tables, and what is gained by using a direct connection from access frontend to sql backend? I am a little fuzzy on that as isnt that essentially what linking an Access frontend to tables in sql backend does.
Thanks, Steve
0
 
LVL 18

Expert Comment

by:jmoss111
ID: 24446742
It basically boils down to speed and bandwidth; with linked tables you might as well be running Access.
sqlservervsfileserver.gif
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question