Upsize Database to Sql Server

[Karen]

I am attempting to upsize a 60MB database in Access 2000 to sequel
server using the upsizing wizard. I go thru all the steps that it
mentions in the book, but none of the tables are actually
upsized/converted. The error report says "Table was skipped or export
failed" for every table. When I attempt to add a new table to this
new project, I get an error that says "Table cannot be opened due to a
dropped server connection". I have no idea what that means... Can
anyone help?

Thanks.

Karen

[Rich P]

First, make sure you have compacted your Access mdb file. If you
haven't done that, your 60 meg file may really be only 20 megs. Either
way, 60 megs isn't a whole lot (even for Access). I have never had much
luck with the upsizing wizard. Your best bet is to use the Data
Transfer Service (DTS) from Sql Server. To get to DTS you can go

Start/Microsoft Sql Server/Import and Export Data

Or you can go straight to Enterprize Manager, select your database,
right-click on the database name (or on any name under the database), on
the context menu select All Tasks/Import data. This will also bring up
DTS. From DTS you can import all your Access tables in one try. But if
you are having import errors, I would do one table at a time.
Hopefully, only one table has the problem. At least with DTS you will
be able to Identify the offending table(s) and should be able to get a
better error message. Usually the problem is an invalid character in a
field, like text in a data field. Access is way more forgiving than Sql
Server because it is not an Enterprise application and you don't have to
worry about propagating errors throughout your system.

Once in DTS, the data source will be an Access MDB. scroll upward until
you find Access. Then locate your mdb file in the Filename window
(works best if the Access mdb is on the same machine as sql Server).
Then click next (assuming you have selected the correct Sql Server DB to
plant your table into - if not, make sure you selected the correct Sql
Server DB from the dropdown list right above the Back button), click
next, click next - here you select the table(s) to import - click in the
check box next to the table name, click next, click finish (and cross
your fingers).

If you get an import error, you will have to go through all the records
in the offending table to identify the invalid piece of data. Use DAO
code and loop through all the fields in each record to find that guy,
usually a typo (1/1/0 instead of 1/1/01) in a data field.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tony Toews]

kr****@portlite.com (Karen) wrote:

I am attempting to upsize a 60MB database in Access 2000 to sequel
server using the upsizing wizard. I go thru all the steps that it
mentions in the book, but none of the tables are actually
upsized/converted. The error report says "Table was skipped or export
failed" for every table.
The problem is likely dates in Access which don't work in SQL Servers default
smalldatetime sized field.

See my Random Thoughts on SQL Server Upsizing from Microsoft Access Tips page at
http://www.granite.ab.ca/access/sqlserverupsizing.htm for some things to try.

When I attempt to add a new table to this
new project, I get an error that says "Table cannot be opened due to a
dropped server connection". I have no idea what that means... Can
anyone help?

That one's wierd and may be the cause of the original message. Can you access the
SQL Server via the Enterprise Manager and create tables there?

Are you using an MDB or ADP? Presumably an MDB.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Big Time]

Not sure about the server connection issue but when I was upsizing I had to
massage a lot of the data in the Access tables before it would take all the
records. There were huge issues around dates as any record with date prior
to I believe the year 1743 would cause an error. Obviously you shouldn't
have records with this year on it but in the case of an incorrect data entry
(in my case, a number of records had the year 202 instead of 2002), this can
happen.

I used DTS to do the import rather than the upsize wizard. I also manually
mapped each field to the proper field type in SQL server. I found that some
fields that were set to text in my MDB were being converted to integer in
SQL Server which caused data to be dropped. I also had to modify the field
length in some of my varchar fields in SQL server as the default field
length was too short to accomodate the entire data transfer.

It took me a good handful of attempts before it finally imported correctly.
However the upside was that it did give me a chance to clean up some of the
data in my MDB file.

Hope this helps.

"Karen" <kr****@portlite.com> wrote in message
news:50**************************@posting.google.c om...

I am attempting to upsize a 60MB database in Access 2000 to sequel
server using the upsizing wizard. I go thru all the steps that it
mentions in the book, but none of the tables are actually
upsized/converted. The error report says "Table was skipped or export
failed" for every table. When I attempt to add a new table to this
new project, I get an error that says "Table cannot be opened due to a
dropped server connection". I have no idea what that means... Can
anyone help?

Thanks.

Karen