How to transfer table structre and data from Access.

[Lotruth]

I need to copy tables from Access to SQL Server 7 (ODBC Driver). Using the DTS, I can copy some tables, but get the following error with others:

"Error at Destination for Row 1. Errors encountered so far in this task: 1."

All off the tables transfer, but those with the error only transfer the structure and no data. How can I copy these tables and keep the data as well? Thanks for any help you can offer.

 

[redlam]

its hard to say what the problem is from that error message. is there more detail available?

could it be that there is a primary key that is violated by this data or an invalid datatype? that would be my first guess. is it possible to include the access table structure in your next post with the details of the data in row 1?

 

[Lotruth]

I cannot give the details, but I can say that the table uses text, date/time, memo, number, and yes/no fields. Also the primary key field has missing numbers that were skipped due to deletion.

PS:
Is there a limit to the size of the table that can be
transfered?

 

[redlam]

hmmm... i don't think the problem is the size of the table -i would think that access would have a lower threshold than sql. it also doesn't appear that your datatypes are an issue. without knowing exactly what the error is, i would export the the access table to a delimited text file and attempt to import that file into the sql table that way.

let me know if you are able to obtain any more details from that error message.

 

[redlam]

just thought of something.... there might be an invalid character in your access table. check the data to be sure that all date values are really dates and that all yes/no values are valid bit values. you also might want to check the date range of your datetime data. sql has some limitations for these datatypes:
smalldatetime - January 1, 1900, through June 6, 2079
datetime - January 1, 1753 through December 31, 9999

also - when you use dts, click on the 'transform' button in the source/destination grid to verify that all datatypes are mapping appropriately. sql does a good job guessing but sometimes it makes mistakes which would cause a problem like this.

 

[Lotruth]

I was able to copy the table by exporting to Excel first. Thanks for all your help.