Access database to sql2k

Question

Access database to sql2k

Barkingdog

SSCoach

Points: 18670
More actions
March 15, 2004 at 4:34 pm

#60083

We have an Access database 350MB (no misprint) in size. I need to import it into Sql 2k. While I can easily capture the data via DTS I suspect it won't capture the relationships between the tables.
What's the best way to do this?
TIA,
Bill

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

Peter Evans Ten Centuries Points: 1187 More actions · Answer 1

Bill,

Have you just tried using the Upsizing Wizard built into Access 2k? It creates a nice report of how it converts Access Types into SQL Types and reports problems.

Peter Evans (__PETER Peter_)

Barkingdog SSCoach Points: 18670 More actions · Answer 2

Peter,

Thanks. I am not that familar with Access. I ran the upsizing wizard as you suggested and it seemed to go well but the Sql dataabse is only 25MB in size, Given the orignal Access database was 350MB I don't know if this is cause to celebrate or to wonder where all the data went.

Thanks,

Bill

Arden Weiss Right there with Babe Points: 749 More actions · Answer 3

Regarding Access file size -- it can "blote" real big in a short period of time depending on activity against it.

Try loading the Access Database and them via the Access Menu run the "Tools" - "Database Utilities" - "Compact and Repair Database" menu item. I bet the size of the Access database will shrink dramatically.

I have had 5mb Access files compact down to 0.5mb if they had been in use a while with lots of activity.

Arden

Peter Evans Ten Centuries Points: 1187 More actions · Answer 4

Bill,

Arden is right on the money, I've seen Access database shrink by this factor. So try the built-in tools. The cause as IRC is that really never internally deletes records just marks them as deleted with a flag. This keeps its performance fast. (Note: the last time I looked at an inside the Access format Knowledge base article was back in the Access 2.0 days.) Consequently a routine tasks to do with access(.MDB) files is to use the Database utilities to compact them. I think if you search around there is probably a Visual Basic Script for doing this programmatically.

Peter Evans (__PETER Peter_)

The Aethyr Dragon SSChasing Mays Points: 636 More actions · Answer 5

You may also want to do a random check (row counts) on a few tables to make sure that all your data came across. I've used the MS Access Upsizing Wizard a few of times and it does tend to be a bit finicky if you don't pick the right options for your database. Double-check any RI you may have had in the Access DB - it will be implemented as either constraints or triggers in SQL - depending on which options you picked.

Also, take a scan thru the pages and pages of report the wizard churned out - you will be able to pick up any errors or warnings where the wizard couldn't complete something.

WRT compacting Access DB programmatically - you can run the Compact and Repair from the command line or set up a shortcut that does it - I would assume that you can also schedule it to run on a regular basis. The following are the parameters:

{path to MS Access.exe} {Path to database} /compact

"C:\program files\MSAccess.exe" "C:\MyDBs\db1.mdb" /compact

Check STARTUP COMMAND LINE OPTIONS in MS Access help - there are other options you can use as well

Cheers

The Aethyr Dragon

Cape Town
RSA

kpspencer Mr or Mrs. 500 Points: 586 More actions · Answer 6

Another option I have used several times (this is after being caught out by upsize wizard loosing data!)

Use Upsize wizard to export the desin only to SQL

Then use SQL Import data wizard to import data after.

I have found this to be far more reliable than doing everthing from access.

Karl

Kevin Jones SSC Enthusiast Points: 114 More actions · Answer 7

I have a follow-up question to what's been said here. I work a lot with Access (and the suggestions above are correct for correcting the bloat).

I'm about to start working with SQL Server and will use the upsizing wizard.

Which option is best in SQL Server for preserving the relationships, via triggers or constraints? I expect its one of those "it depends" situations. So, maybe a better question is, briefly what are the main factors in deciding for one or the other?

Kevin

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 8

Actually, the upsizing wizard is not a very good tool to use. It is the best one if you do not have quality tools, like ERWin Data Modeler. But, After upsizing, you need to go in and clean up the naming of all the relationship, indexes, and primary keys. Also, you should never upsize the data, only the structure. ERWin has the capability of reverse engineering the Access DB and the selecting a different target, SQL Server, Oracle, etc, for the destination db.

ERWin is not a cheap tool, running $3,500 retail. It is available as older versions from EBay and can be had for about $700 that way. The current release 4.1.4 seems to be more stable that the past versions.

You should use the import data wizard or DTS for transfering the data after cleaning up the database.

You should use DRI (I think it is called) and not triggers for referential integrity on the SQL Server side.

Kevin Jones SSC Enthusiast Points: 114 More actions · Answer 9

Are those capabilities mentioned for ERWin the same type of features that I believe exist in Visio? If so, that's more affordable for me (since I already have that).

And thanks for the other tips, too.

Robert Stewart Hall of Fame Points: 3399 More actions · Answer 10

I am not sure about Visio. I would think that the Visio for Enterprise Architect would do some of these things. You might also check out Quest Software for their version of Toad for SQL Server (vs. Toad for Oracle). That would give you some kind of administration tool that you could use if you do not have Enterprise Manager for SQL Server.

You might also look at Visual Modeler from MS. It is designed to work only with SQL Server like Toad, but it will allow you to do a good model of your db.

What I do not think (and someone correct me if I am wong) is that Visio will reverse one type, Access, and create another type, SQL Server. ERWin is compatible with about 15 or so different databases. And, to comment on the My SQL earlier in this thread, it is not one of the ones supported by ERWin.

The cheapest way of doing it is to upsise, then clean it up using one of the administration tools. Visio can not do the administration.

Barkingdog SSCoach Points: 18670 More actions · Answer 11

Thank you for all the great replies. Obviously, I thought the original problem was simply a "use DTS" one so I put it in the forum. I didn't realize that there were that many aspects to the problem!

Bill

EdH334 SSCrazy Points: 2857 More actions · Answer 12

One issue to watch for when using DTS to import MS Access data is that Access date/time fields support 'older' dates (100 AD through to 9999 AD) compared to the datetime fields in SQL Server (1753 AD to 9999 AD). This can be a problem if you are storing historic (e.g. archaeological) data, or if the year has been mistyped (e.g. 201 for 2001).

(tip: You can quickly identify stray dates by opening the table in access and sorting the date/time column in ascending order and filtering out any null values)

Alamir SSCertifiable Points: 6085 More actions · Answer 13

I transfer my Access DB to SQL Server 2000

the best way is to use Access 2002 (XP) upsizing wizard .... it trasfer data , RELATIONSHIPS, INDEXES, defaults and many other things

Use RDI in Relationships

of course check all checkbox (to trasfer indexes)

Ok ... ?

try .. and If you need any help .. I am here

Alamir Mohamed
Alamir_mohamed@yahoo.com