Porting from MS Access 97 to MS SQL server ?


I am currently using an MS Access 97 database as front end as well as back end and I would like to port the entire back end to SQL server with MS Access 97 as front end.I would like to know as to how smooth this transition phase would be and what all problems will be faced when porting to an SQL server.

My database supports mission critical applications related to internet and loss of any kind of data at this juncture due to porting would be unjustifiable.

Also, is it viable to have SQL server on an NT workstation or on an NT server.
I would appreciate help on this regard.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

As always you should backup the data before doing anything, just in case.  But, there is no reason to expect any data loss.  I suppose you are using the upsizing wizard in Access.  This just places all the table data into similar SQL server tables.

As to the NT Server versus Workstation.  It will fuction on either system.  This is really an NT issue.  I believe NT Server is configured to perform better as a server, while Workstation is not.  For example, the Workstation will give more memory to desktop applications, whereas the Server will boost performance for the server running in the background.  You should check with the NT experts on this for more detail.
The upsizing wizard while also make the SQL Server tables into linked Access tables.  So, as far as Access is concerned nothing has really changed.  The tables are just linked now instead of residing in the Access database.

However, this is not an optimal setup.  If you need Access to remain as a front end it would be optimal to change any queries you have into Pass Through queries that run SQL Server stored procedures.  In those stored procedures is where the actual query SQL should be.

Now this is an opinionated comment, though I think there are many who will agree.

SQL Server has the advantage of being able to run the queries on the server machine, while Access runs them on the client machine.  Since, the tables are now LINKED this means that data will be transferred back and forth accross your connection while the queries do what they do.  On the other hand, SQL Server stored procedures do it all on the Server system and sent the results to the client system over the connection.  This is much faster.  For one thing, with linked tables you are retrieving all the columns, where using a pass through query you retrieve just what you queried for.

Of course, if you're not ready to program the stored procedures (which the upsizing wizard does not do) then leaving the Access queries with linked tables will work, it's just not optimum.

Follow this steps and u r through.
All microsoft products goes very much hand to hand.
If u r using all microsoft products u will never face any problem.
First backup .mdb file somewhere and then oepn your enterprise manager and use the import wizard to import access to SQL-Server.
The problem u can face after importing is in datatypes (for e.g. if in access if datatype is yes/no then in SQL-Server it will be binary so change it to char(1) )
So only care u will have to take is check all the datatypes after importing and change appropiately.
The queries to be executed will totally depend on SQL-Server and not according to access standards.

If any problem feel free to contact.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Please note that if someone else has entered in detailed comments to not lock the question with your own "answer". I think you will find that over time  you will be pissing off a lot of experts if you continue to do this (that is, claiming that your answer is better than all previously posted comments).

explorer007Author Commented:
Highmarks ,
The problem is not only of porting the entire MS Access 97 database to SQL - Server but also porting the front end which has been developed using MS Access.It would take a lot of front end changes to do the same.

Porting the backend will probably will not be tedious, but porting link tables from MS Access to SQL Server would cause some hassles.

I will be doing the porting in a days time and will then evaluate the answer.
In any case.. the answer presented is not fully furnished with overall details
Thanx anyway
Normally when someone refers to a backend, I think of this as the data tables.  So, I'm not sure what you mean by porting link tables.  SQL Server can, and should, store all your tables and your queries.  But if you have any forms or reports in Access they will have to remain in Access, unless you want to program some other front end app.

So, you have some decisions to make.  What goes in the front end and what goes in the back end.  Since, you are using the internet as the location of some of your front end, I don't see a need for Access there.  You can link to SQL Server directly form ASP or Java pages.  I hope you're not going to link from your internet front end to Access, which in turn must link to SQL Server.  Everytime you make a link, you are causing your users valuable connection time (how long will these apps take to load?).  There are different ways to do this, so it's hard to give you details if you don't give us details on exactly what you want.  

Not that I'm being defensive.  But, I think we've given you as much detail as we can based on the information.  Most of the how-to stuff is in Access and SQL Server help or Books Online (and ASP, Java, HTML..), and the volume of that information is extensive.  Just as this note is in trying to cover a few more of the bases.  So, I see our role as pointing you in a direction, or offering some alternatives as far as a basic structure or methods.  However, if you find you need more specific advice after you get started, this is a good place to get it too.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.