Migration to SQL server

[Doc94]

I have an extensive database I would like to convert to SQL server. I created it in Access 97 and have upgraded to access 2000 (no further as all of the computers on the network do not support Access beyond 2000). I have a front end on each cmputer that uses it and a backend residing on one of the hospital servers.
I have tried converting the backend to SQL and the connecting the .mdb front end to it without success. Anyone that could give me or point me to step by step instructions would be appreciated. I have tried the suport.micrsoft site and have not found their info detailed enough.

 

[Catadmin]

The best thing I've ever found for moving an Access 97 db to SQL Server 2000 is to use the DTS Import Wizard. The upsizing tool in Access never worked for me.

You'll want to go through all your tables on the tab where it lists them, hit the ellipses button (...) to transform your data and make sure nothing funky is happening with your data types. We had a problem of Access text going to nvarchar instead of varchar in our DB, for instance. But once you have the transformation set, you can save it for a later time or run it immediately (or both). Then you're set and everything should port over into SQL just fine.

Let me know if you have additional questions.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[Doc94]

Where is the DTS import tool located?
Also, I understand Access front ends and back ends but I am ignorant to SQL. Will I need to create a new SQL backend? Shuld I conovert my Access backend to SQL and attach the access front end to it? Will the server I use need to have SQL running on it or can I use SQL running on my desktop.
Thanks

 

[Catadmin]

Also, I understand Access front ends and back ends but I am ignorant to SQL.

Time to learn, then. I'd advise getting some books (SQLSister has a great FAQ on the admin forum for books) and going through Books Online.

As far as using the DTS Import Wizard, this is a basic and fairly simple tool. Here's the brief skinny:

First, install SQL Server on your Server. If the server can see the machine with the Access DB, then great, you can work from there. If not, I recommend taking a copy of the access .mdb file and moving it to the server by any means at your disposal.

Open up Enterprise Manager and the following groups.
Microsoft SQL Server -> SQL Server Groups -> <ServerName>

Right click the Databases folder & navigate to All Tasks -> Import Data.

Look up DTS Import / Export Wizard in Books Online for more details on how it works, but if you read the screens carefully, it's pretty user friendly. You have your source, which you'll choose as Access and point to the .mdb file, the next page is destination, the third page allows you to do a direct transfer (default) or query transfer (etc.), the fourth page is where you choose the tables to move over and will see (next to each table) the ellipses box which will allow you to control your datatype transformations.

After that, it's pretty much Next, Next, though you might want to save the package before running it just incase you need it again.

Will I need to create a new SQL backend?

I can't answer that question, because I don't know if you've already installed SQL or not. If you have, just use the SQL Server you have installed.

Will the server I use need to have SQL running on it or can I use SQL running on my desktop.

The only version of SQL 2000 that runs on a desktop is Personal (had to be bought with Enterprise version) or Developer version. You'll need an actual Server type machine to run SQL Server on. You can, however, run the SQL Server client tools off your desktop, which will give you access to the server on which SQL is running.

To link your SQL Tables with an Access front end, you should be able to follow the Access help files about creating Linked tables. It should be that simple. Grab some books, play with SQL and Access in a test environment first, and then you should be ready to try moving things in production.






Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[AccordingToDale]

If I understand what you're asking...

The import tool will import your data and schema - which is the backend.

It's the frontend that will be the issue. Unlike Access, SQL Server is purely a datastore, it has no frontend development tools (creation of forms).

Although I've never done it, I know that you can use an existing Access frontend and connect to a SQL Server backend. I'm not sure of all the in's and out's, but I am pretty sure you have to convert your .mdb to a .adp - which is an Access Data Project.

Your forms are then able to link to a datasource other than the native Access.

You might want to Google 'Access Data Project' for a few more details.

Your other option is to develop an new frontend with, say, vb.net (hint, hint!) :>)

Good luck.

Dale