SSMA for Access Vs ADP (Access Data Project )
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I use SSMS
ASKER
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
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
It basically boils down to speed and bandwidth; with linked tables you might as well be running Access.
sqlservervsfileserver.gif
sqlservervsfileserver.gif
ASKER
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.