SQL Server export to Access Database error?

Question

SQL Server export to Access Database error?

Jrell Will

SSC Rookie

Points: 44
More actions
October 5, 2004 at 3:37 pm

#66923

I am trying to export a SQL Server table to a Access 2000 database using SQL Server export wizard. After exporting the table and it's data to the Access database, all the data is not transfer to the Access database. The data in several table fields are missing. For example if I have a table called Contact and it contain the fields First_Name, Last_Name, Telephone, and ID every person's in the table First_Name, Telephone, and ID information will be transferred to the table, however their Last_Name would be not. What could be causing this problem?
Table Example
ID     First_Name     Last_Name         Telephone
1       Mike                                  222-222-2222
2       Sarah                                333-333-3333
3       Hallie                                 444-444-4444

Viewing 4 posts - 1 through 3 (of 3 total)

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

Craig Webber SSC Rookie Points: 32 More actions · Answer 1

I take it you are using DTS --> Export Data to perform this? I've also had difficulty with importing and exporting data from two merged Access tables. Did you create the target table in Access in advance or did you let Access create it. Is the ID column in SQL Server a non-null int (identity)?

ButchH Ten Centuries Points: 1073 More actions · Answer 2

May I suggest that instead of exporting SQL data to MS-Access, you just create an MS-Access database and either Link the necessary tables (with links), or create Views in SQL and Link the views in MS-Access?

Much simpler and no worries about the data always being current!

Hope this helps.

Butch

Jrell Will SSC Rookie Points: 44 More actions · Answer 3

Thank you for all your replies. The DTS did not export all the data over to Access database because there was too much extra white space at the end of the data and the varchar data type could not handle all the extra space. I end up using the VBscript function Trim() in the Transformation script function of DTS to remove all the extra white space off the end of the data before exporting it over to the Access database. This solved the problem.