Import doesn''t import every table

Question

Post reply

Import doesn''t import every table

Oliver Richardson

SSC Veteran

Points: 202
More actions
April 4, 2006 at 3:21 am

#167796

Hello there,
I've just imported an mdb file file to my MS SQL 2005 server and have realised that it does not import all the tables. I have repeated an import on the tables that were missed the first time
and this seems to work. However, this doesn't seem to be a particularly elegant way of doing things. Can anyone suggest why this should be so?
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 1

Oliver,

How did you do the import?

Regards,Yelena Varsha

Oliver Richardson SSC Veteran Points: 202 More actions · Answer 2

Hi Yelena,

I right-click on the datbase of my choice->tasks-import data. The import/export wizard appears. I select Microsoft Access as a data source and SQL Auth. as authenification procedure (plus username/password).

THen for the client I slect SQL Native Client and also SQL Authentifacation. I check the radio button for copying data from at least one table or view (the first one). Then I select all the tables from the mdb and press continue. The import process starts but doesn't import everything.

Greetings,

Oliver

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 3

Hello Oliver,

did you get any error messages?

There are 2 things to try:

1. On the screen with the table list click on each table and click Preview button. If the table could be accesses, you will see data or if the table could not be accesses you may get an error message that read access denied. This may be the reason that this table was not imported.

2.Next to each table name there is a Edit button on the same table selection screen under the field called Mapping. Click it and see what is selected for this table: is it to Create New Table or Append Rows to the Destination Table. Check if all column mappings are OK

Regards,Yelena Varsha

Oliver Richardson SSC Veteran Points: 202 More actions · Answer 4

Hi Yelena,

Sorry for the late reply - I have seen been continuing tests on a copy of the data. Howevr, I took the two steps above and everything is order - the data is there and if the tables don't exist(by first import)then the "Create New Table" caption is checked, otherwise "Append Rows to the Destination Table".

However, by looking closely at the error reports like you said I realised that SQL Server is not importing some data of type "datetime". Only "datetime" data in access that has a time 00:00:00 attached to the date is imported otherwise the value is NULL.

This an error but do you know how to change the format?

Thanks for the help,

Oliver

Oliver Richardson SSC Veteran Points: 202 More actions · Answer 5

Hi there.

Actually the problem here was invalid data. The error report told me this but not where the data was located. Therefore, I imported my data using selects until I narrowed my the selects down to an invalid data set. The problem was obvious - the year is 205 instead of 2005. While Access accepts this, SQL Server doesn't. This is obviously down to poor validation on the frontend of the application and seems to be irreversible.

Does anyone have any thoughts to this problem?

Thanks