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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Converting MS Access to SQL server
- Thread starter Chrisma
- Start date
Good question.
First, you need to consider the data types, the wizard will select some for you but they aren't necessarily the best ones. For instance varch fields can take 8000 characters which is much more than an access text data type can take. So often longer data that would easily fit into the varchar data type is converted from memo to text rather than varchar by the wizard. If your memo fields aren;t that long you may want to make them varchar instead.
security is an issue in SQL server than many Access people are not used to. You need to learn about how to set up users and set the security using roles. What ever else you do, make sure sa (the system login) has a password and that no one is accessing the databse from the user interface using the sa user. YOu really need to set specific rights for each type of user.
Books online is an important thing to know about. This is the SQL Server help file and will answer many questions for you.
One differntvce between Acces and SQl Server is that Access is both a database engine and a user interface. SQl Server is only a database, you must create the user interface yourself. YOu can link your table to the access interface and use that but you will probably need to change some code if you want to see a performance imporvement. Instead of using Access queries (which will still work if you use linked tables inthe mdb database vice creating an adp project) you will want to use stored procedures instead. Stored procs are faster than views generally and allow the use of input parameters which views do not.
Some functions are differnt and somethigns are done completely differently. IIF is not a recognized keyword in SQL Server's T-SQl language. To fix any queries that have this use the Case statment instead. Any conversion problems you come to with you code, just go to the SQL Server programming forum and we will help you find the new syntax.
Questions about posting. See faq183-874
Click here to help with Hurricane Relief
First, you need to consider the data types, the wizard will select some for you but they aren't necessarily the best ones. For instance varch fields can take 8000 characters which is much more than an access text data type can take. So often longer data that would easily fit into the varchar data type is converted from memo to text rather than varchar by the wizard. If your memo fields aren;t that long you may want to make them varchar instead.
security is an issue in SQL server than many Access people are not used to. You need to learn about how to set up users and set the security using roles. What ever else you do, make sure sa (the system login) has a password and that no one is accessing the databse from the user interface using the sa user. YOu really need to set specific rights for each type of user.
Books online is an important thing to know about. This is the SQL Server help file and will answer many questions for you.
One differntvce between Acces and SQl Server is that Access is both a database engine and a user interface. SQl Server is only a database, you must create the user interface yourself. YOu can link your table to the access interface and use that but you will probably need to change some code if you want to see a performance imporvement. Instead of using Access queries (which will still work if you use linked tables inthe mdb database vice creating an adp project) you will want to use stored procedures instead. Stored procs are faster than views generally and allow the use of input parameters which views do not.
Some functions are differnt and somethigns are done completely differently. IIF is not a recognized keyword in SQL Server's T-SQl language. To fix any queries that have this use the Case statment instead. Any conversion problems you come to with you code, just go to the SQL Server programming forum and we will help you find the new syntax.
Questions about posting. See faq183-874
Click here to help with Hurricane Relief
- Thread starter
- #3
I thought SQL server is a kind of database engine not an actual database. If you run an SQL server you have to have a Database first. Many applications can use SQL Server to access a database. For instance you might be able to have an IBM database and use SQL Server to manipulate the database. Or you could build a UI front end and then use SQL Between the application or UI and the Database.
We are running an IBM Unidata Database. It is not completely relational but it is a fairly simple database. We purchased it in conjunction with a package for managing education software and it came with A user Interface to manage the database and let you see different views of the database system. We also have some 3rd party applications for scheduling appointments and managing different rooms and venues which we sometimes rent out to local community groups and private companies, and even for weddings and School/Union Events. The software uses SQL Server to communicate with the database. The database is on one server, the SQL server is on another server and the applications are on a third server. We have a fourth server that we are using for reporting purposes that we are developing that uses views and makes querries and delivers the reports through use of EXCEL. There are many things you can do with databases and many ways to deliver the data to your customers. Even though we purchased a canned product so to speak we chose some different products to go along with that.
If you do not like my post feel free to point out your opinion or my errors.
We are running an IBM Unidata Database. It is not completely relational but it is a fairly simple database. We purchased it in conjunction with a package for managing education software and it came with A user Interface to manage the database and let you see different views of the database system. We also have some 3rd party applications for scheduling appointments and managing different rooms and venues which we sometimes rent out to local community groups and private companies, and even for weddings and School/Union Events. The software uses SQL Server to communicate with the database. The database is on one server, the SQL server is on another server and the applications are on a third server. We have a fourth server that we are using for reporting purposes that we are developing that uses views and makes querries and delivers the reports through use of EXCEL. There are many things you can do with databases and many ways to deliver the data to your customers. Even though we purchased a canned product so to speak we chose some different products to go along with that.
If you do not like my post feel free to point out your opinion or my errors.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
