• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 121
  • Last Modified:

Problem importing Access table into SQL Server

I am trying to import an Access table into SQL Server using the SQL Server Import and Export Wizard.
I created the SQL Server table using the same field names that are in the access table.
After selecting the Source and Destination tables in the wizard I get the message "Found 7 unknown column type conversion(s)".  The seven fields are all text fields in access that I am mapping to nvarchar fields in SQL server.   There are other text fields that map successfully.  Can  anyone tell me what my problem might be?
0
dsoderstrom
Asked:
dsoderstrom
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need to create a table before. The Import/Export wizard will do that for you and give suggestions for the data types. Do that to see what the wizard suggests for those 7 columns. You can change the data types during the process.
0
 
Jeffrey CoachmanMIS LiasonCommented:
Are the suspect fields identified by SQL Server?

Also make sure that none of your Access fields are "Access Only" datatypes:
ex: Lookup Fields, multivalued fields, or even fields formatted as "Rich Text" (HTML)

JeffCoachman
0
 
dsoderstromAuthor Commented:
when I try creating the SQL table using the wizard it shows a data type of -1 for these fields. It lets me change the type to nvarchar with a size of max.  It does not allow me to change the size to the appropriate length.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Try varbinary(max).
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You might also try the SQL Server Migration Assistant:

http://www.microsoft.com/en-us/download/details.aspx?id=43690

I've generally had better luck with that.
0
 
dsoderstromAuthor Commented:
Thanks for point me to the SSMA. I worked perfectly.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now