May 7, 2013 at 11:24 am
are these existing Access dbs that you want to upgrade to SQL?
you say you have six weeks experience in MS Access...what SQL experience?
ever designed a front end app before?
I have no SQL experience and am trying to learn on the fly... With the mentione of lockups in SQL my concern would be multiple users trying to access the front end and getting locked up. Would that be common with using SQL server as the back end and Access at the front end app?
May 7, 2013 at 11:44 am
todd.ayers (5/7/2013)
are these existing Access dbs that you want to upgrade to SQL?
you say you have six weeks experience in MS Access...what SQL experience?
ever designed a front end app before?
I have no SQL experience and am trying to learn on the fly... With the mentione of lockups in SQL my concern would be multiple users trying to access the front end and getting locked up. Would that be common with using SQL server as the back end and Access at the front end app?
ok...
minimal Access experience / No SQL experience and I am going to assume no app dev skills either...correct?
you talk about existing databases...what platform are these currently on...who currently manages them...could those people assist you?
if your app is mainly read only / reports then I wouldn't get too hung up about "locks"...
if it going to be mainly data entry (create/amend/delete) then you will need a reasonable level of experience in any front end on how you intend the app to manage situations where possible locking may occur...and manage it gracefully so that the user understands what is happening.
Todd...I do not wish to dissuade you from your pursuit...only to point out that there is no easy way and a steep learning curve ahead;..whatever your preferred option.
Good luck.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 7, 2013 at 11:52 am
ok...
minimal Access experience / No SQL experience and I am going to assume no app dev skills either...correct?
you talk about existing databases...what platform are these currently on...who currently manages them...could those people assist you?
if your app is mainly read only / reports then I wouldn't get too hung up about "locks"...
if it going to be mainly data entry (create/amend/delete) then you will need a reasonable level of experience in any front end on how you intend the app to manage situations where possible locking may occur...and manage it gracefully so that the user understands what is happening.
Todd...I do not wish to dissuade you from your pursuit...only to point out that there is no easy way and a steep learning curve ahead;..whatever your preferred option.
Good luck.
Unfortunately no app dev skills either. I have an IT background and a degree in computer science but nothing concerning RDBMS. The current databases are on the Access 2007 platform and not being currently administered. I was given this position because I already worked for the company and the plant manager knew I had the closest background available to address issues that are arising.
Concerning the Databases themselves there will be 50/50 relationship between entering data within the databases and retrieving reports and other data from those databases.
May 7, 2013 at 11:57 am
todd.ayers (5/7/2013)
ok...
minimal Access experience / No SQL experience and I am going to assume no app dev skills either...correct?
you talk about existing databases...what platform are these currently on...who currently manages them...could those people assist you?
if your app is mainly read only / reports then I wouldn't get too hung up about "locks"...
if it going to be mainly data entry (create/amend/delete) then you will need a reasonable level of experience in any front end on how you intend the app to manage situations where possible locking may occur...and manage it gracefully so that the user understands what is happening.
Todd...I do not wish to dissuade you from your pursuit...only to point out that there is no easy way and a steep learning curve ahead;..whatever your preferred option.
Good luck.
Unfortunately no app dev skills either. I have an IT background and a degree in computer science but nothing concerning RDBMS. The current databases are on the Access 2007 platform and not being currently administered. I was given this position because I already worked for the company and the plant manager knew I had the closest background available to address issues that are arising.
Concerning the Databases themselves there will be 50/50 relationship between entering data within the databases and retrieving reports and other data from those databases.
Welcome to the wild, wild world of the data professional. Keep your hands and feet inside the vehicle at all times and avoid shrinking databases and using no_lock hints.
But seriously, you're stepping off into some deep weeds here. I'm not saying don't do it. Absolutely. Go for it. Access is actually a good way to learn the basics of both development and database management. But it does have some gotchas. I'd strongly recommend doing some research to track down a book specifically on Access as a front-end development software. They're out there. That will be your best friend going forward. If you get stuck on SQL Server points, always stop by here. If you get stuck on Access stuff, I'm not sure where to send you. Maybe we have an Access forum too. If not here, then try Microsoft Developers Network (MSDN) because I'm sure they do.
Best of luck. Go slow. Learn how to take backups of the SQL Server database before you do anything else.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 7, 2013 at 11:58 am
as a start...worth reading
http://msdn.microsoft.com/en-us/library/hh313039.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 7, 2013 at 12:29 pm
Todd...if you go down the Access route then here is a good resource
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 7, 2013 at 12:55 pm
I have done this using an older version of Access and SQL Server. Table locking was an issue, but for small numbers of users it does not occur too often and can be managed.
The scenario I dealt with was upgrading and the eventual replacing of applications developed in MS Access.
Number of Users: 67 - different departments and business functions.
Stage one:
Getting the Access applications to function properly using SQL Server tables linked in.
1. Importing the tables into SQL Server was not a big problem, but one must ensure that constraints, defaults, keys and Identity columns remain intact once imported.
2. Apply index based on how the data is used.
3. Test insert, update, delete functionality at the table level thoroughly.
4. Create ODBC entries on your development work station to connect to the SQL Server database.
5. Have two working copies of the application in development. One will be used, in it's original state, to test the modified version.
6. Remove all the tables from the development version of the application and link the SQL Server versions back. Note the tables names will be different - so you may choose to change the names to match the old names. Cumbersome. This is good in the testing phase as it will not require changes to the existing application logic. But it is cumbersome.
7. Test the application thoroughly. Fix issues - which will mainly come from naming of the new tables. Do not forget to run all reports on the "untouched" and modified versions of the application to test out behavioral differences.
Stage Two:
1. Work with web developer to create a front end that will work with your new database. I chose moving all logic to stored procedures when possible. This allowed for me, the database guy, to manage data issues and leave the front end development deal with presentation issues.
Good reasons to use Access:
1. Quick turn around from concept to reality.
2. Business users may be familiar with Access and can prototype the type of application they need.
3. Report generation can be done very quickly and often times by the business user themselves.
The ugly
1. Often times applications written by business users can quickly clutter data.
2. Convoluted reports and application functionality can make it difficult to migrate away from Access later on (though not impossible).
3. Giving up Access means giving up control for many business users - and this can cause stress for them and you.
I know I have not mentioned anything that people have not said before. I will add that quite often times its much easier to completely replace an application written in Access than attempt to migrate it. You can always import the data into the final solution.
In my case the migration was necessary to ease the user base into the changes (manufacturing company).
Hope this helps.
May 7, 2013 at 1:46 pm
Oh Joy!!!! We too are a manufacturing company!!
May 7, 2013 at 5:10 pm
I'm a recovering Access developer, so my opinions may help you (and may well not).
Anyway, Access is good for prototyping and front ends (if you cannot use ASP.NET or VB.NET or C#). One thing that a .NET/SSRS solution would buy you is more control. You can allow users to create their own reports using Report Builder, which is a big help. (So you don't have to do build all the reports yourself). You can also expose only the objects you want them to see/use. And what you can do in Reporting Services is so much better than what you can do in Access, it's just not even funny.
I would go to UtterAccess.com and look around there and see what people say. I remember 2003 and prior versions had terrible security and a max file size of about 2GB, which is nothing these days. You might want to check the specs on your version of Access, though.
One way that might work is to do the data entry GUI in Access and then use Reporting Services (if it's available on your version of SQL Server). You can create report models etc so that your users can use a simplified view of the data(base) to build the reports that they need. So it somewhat depends on how savvy your users are.
May 8, 2013 at 11:11 am
>>One way that might work is to do the data entry GUI in Access and then use Reporting Services
I concur.
If you can't use Reporting Services and must or want to use Access as your reporting tool I would recommend using SQL Server stored procedures and then call those stored procedures using pass-through queries. I would not encapsulate your reporting logic in access queries, if you do you'll have a lot less flexibility in the future.
May 8, 2013 at 2:27 pm
interesting comment here
http://www.sqlservercentral.com/Forums/FindPost1450729.aspx
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply