Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Converting MS Access to SQL server

Converting MS Access to SQL server

Converting MS Access to SQL server

I have a back end database in MS Access but i need to convert it to SQL server.

What are the things that i need to consider before i start converting it. I have a total of 75 tables in MS Access

RE: Converting MS Access to SQL server

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

RE: Converting MS Access to SQL server

Thanks SQLSISITER. It really a great help.

RE: Converting MS Access to SQL server

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close