I have a large task to undertake, or atleast I think I do. I have an access bar coding program that I wrote a number of years ago in Access 97 which consists of a front end (forms, reports, modules, etc) and a backend which consists of all data. The company would like the program transitioned into SQL 2000 of which I don't know much about. In the access program I did a lot of SQL statement writing. Couple of questions; can the entire program be trasitioned to SQL without the use of a front end such as Access, how good is this? Is it best to keep Access as the front end and access data from SQL? Should I convert from Access 97 to 2000 first then to SQL 2000? I am worried about speed issues as I don't know much about SQL. The current database has about 10-15 users, it isn't my idea to convert them but they are a large company and are requiring this. Any help would be greatly appreciated!! Thanks....
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Access 97 to SQL 2000 - Please HELP!!!
- Thread starter RichM1
- Start date
DazzaLondon
Programmer
Hi
Just starting to study SQL within a MCAD acred but can answer you on overall level, hopefully:
1)if it works, don't change it. Unless, course, there is a bigger picture of providing business-wide database solution to have many aspects of business databased.
2) there is a learning curve! if you know Access then that's great, but SQL requires lots more training than Access! there is a timescale here. Where are you based?
3) SQL is lots more stable, enterprise solution, but saying that if your user base is 10-15 then Access should be fine.
sorry cannot answer your specific questions re access>SQL
Daz
Just starting to study SQL within a MCAD acred but can answer you on overall level, hopefully:
1)if it works, don't change it. Unless, course, there is a bigger picture of providing business-wide database solution to have many aspects of business databased.
2) there is a learning curve! if you know Access then that's great, but SQL requires lots more training than Access! there is a timescale here. Where are you based?
3) SQL is lots more stable, enterprise solution, but saying that if your user base is 10-15 then Access should be fine.
sorry cannot answer your specific questions re access>SQL
Daz
With only 10-15 users and the system working correctly, I would not upgrade to SQL Server. If the system is working slowly because you have too many records for Access to handle effectively, then I would upgrade. The world being imperfect, however, means you'll probably have to do the upgrade anyway.
First to answer your questions. SQL Server has no front end, you'll have to create one or use the Access one you've already built (with tweaking of course). Not sure if you can link SQL tables in Access 97, but I know you can in 2000. Go ahead and experiment to see if you can link a SQL table before you decide on your upgrade strategy.
Speed issues will not be a problem as SQL is considerably faster than Access. That said, you may need to rewrite your queries into SQL stored procedures to gain the benefits of this speed.
Of bigger concern is the fact that SQL Server requires database administration whereas Access generally does very little of this. You'll need to create an effective backup strategy. In SQL backups sereve two purposes. The main one of course is to be able to revoer the database to a particular ppoint in time. But something people converting to Access often don't understand is that it also clears the transaction log. So they continue to rely on the network backup as they did with Access and then a few months later they are on this board asking how come their transaction log is now 15 gig and how to clear it out. You'll also need to learn about setting up user security if you weren't using it in Access (as many people did not). Another issue you will have to address is performance monitoring and tuning. Another issue for you is that Access SQL and T-SQL (SQL Server' version of SQL) and not the same and you will have to learn some things over again. At least you wrote SQL statements, many people converting from Access only used query designer and never even looked at the SQL statements before their conversion.
Good luck. Keep coming here for help as you move through this process and get yourself a couple of big SQL Server books to use as guides (You'll also have SQL Books on Line, but it is often less than helpful to the beginner user). Hey, the comapany is forcing this change, see if you can get them to send you to a training class or at least buy you some of the books. In your frustration during this change, just remember you are gaining a professional skill that is worth more on the job market than Access programmers can command. Go to to see what dba's in your local area make. You should be able to use this information to get a pay raise! In my area, dba's make about 30K more than I made as an Access programmer. (I didn't get this much when I converted, mind you, but I did just swing a 17% pay raise based on this information.)
First to answer your questions. SQL Server has no front end, you'll have to create one or use the Access one you've already built (with tweaking of course). Not sure if you can link SQL tables in Access 97, but I know you can in 2000. Go ahead and experiment to see if you can link a SQL table before you decide on your upgrade strategy.
Speed issues will not be a problem as SQL is considerably faster than Access. That said, you may need to rewrite your queries into SQL stored procedures to gain the benefits of this speed.
Of bigger concern is the fact that SQL Server requires database administration whereas Access generally does very little of this. You'll need to create an effective backup strategy. In SQL backups sereve two purposes. The main one of course is to be able to revoer the database to a particular ppoint in time. But something people converting to Access often don't understand is that it also clears the transaction log. So they continue to rely on the network backup as they did with Access and then a few months later they are on this board asking how come their transaction log is now 15 gig and how to clear it out. You'll also need to learn about setting up user security if you weren't using it in Access (as many people did not). Another issue you will have to address is performance monitoring and tuning. Another issue for you is that Access SQL and T-SQL (SQL Server' version of SQL) and not the same and you will have to learn some things over again. At least you wrote SQL statements, many people converting from Access only used query designer and never even looked at the SQL statements before their conversion.
Good luck. Keep coming here for help as you move through this process and get yourself a couple of big SQL Server books to use as guides (You'll also have SQL Books on Line, but it is often less than helpful to the beginner user). Hey, the comapany is forcing this change, see if you can get them to send you to a training class or at least buy you some of the books. In your frustration during this change, just remember you are gaining a professional skill that is worth more on the job market than Access programmers can command. Go to to see what dba's in your local area make. You should be able to use this information to get a pay raise! In my area, dba's make about 30K more than I made as an Access programmer. (I didn't get this much when I converted, mind you, but I did just swing a 17% pay raise based on this information.)
- Status
Similar threads
- Locked
- Question
- Locked
- Question
