Importing access data into SQL server

Question

Importing access data into SQL server

victor zayas-197939

SSC-Addicted

Points: 465
More actions
January 21, 2005 at 7:44 pm

#65336

I thought that converting my Access application to SQL server would be tough. It's nothing compared to trying to import the existing Access tables into my SQL Server tables. I can't get the import wizard to import my tables. It gets part way through the process and then I get a data mismatch error on the column Datetime in SQL and Date in access. What's that about?? Thank you.

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

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

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

You probably have some data in your Access tables that SQL doesn't like. For example, Access will accept a date of '1/1/1000' , but the same data is invalid in SQL Server. From Books Online, a datetime can be from January 1, 1753 through December 31, 9999. A smalldatetime can be from January 1, 1900, through June 6, 2079.

If you can tell from the import wizard which table is causing the problem, then run some queries in Access to find the out of range dates.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

EdH334 SSCrazy Points: 2857 More actions · Answer 2

I suffered the same problem as Kathi describes. In my case the problem was due to user finger trouble where they would mis-enter a date, e.g. 01/01/101 instead of 01/01/01 which returns a valid date in Access for the year 101 AD, but is invalid in SQL Server.

Try writing a quick Access query to show all dates before 1900, e.g.

SELECT * FROM MyMSAccessTable WHERE mydate < #01/01/1900# ORDER BY mydate DESC;

robertk SSCommitted Points: 1608 More actions · Answer 3

Yup, I experienced both of the above problems. The key is to filter out the bad dates before you try to import...

but, it is stupid that access allowed those craptastic date values to be inputted as dates in the first place.

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 4

I guess it depends on what you are collecting data about whether the dates are craptastic or not. If I was setting up a database about some historical information or artwork where those dates were valid I would have to come up with a workaround to use SQL.

Anyway, you can set up validation rules in Access tables that work like contraints in SQL.

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

robertk SSCommitted Points: 1608 More actions · Answer 5

yeah, in your case, I can understand why you'd want a huge variety of date values... but in most applications, it'd be nice if access had a more restrictive date setup as a default. But, on that part, I blame the developers here for not testing that out and making a lot of my imports more difficult.

I believe there's a bigdatetime datatype in SQL Server 2005, but for sql server 2000, I think you'd just have to use a varchar field to store date values before 1753.