Upsizing Access to SQL UK vs. US date formats

Question

I'm trying to upsize from Access 2010 to SQL Server 2008. All is working except one table; the table gets created in SQL Server, but no data gets upsized. The reason is, one column contains a date which, as stored in Access, is in the UK format (e.g. 31/12/2013). I know this to be true because a) exporting to a text file also fails with that date column, and the error message explicitly says it's because of the "date out of range", but more to the point, b) if I delete that column from Access, and do the upsize, the data gets upsized. So there is no doubt that the problem is, in fact, the date column.

And yes, I know about SSMA (SQL Server Migration Assistant), which appears to have evolved because of the shortcomings with the upsizing wizard. SSMA does not appear to be an option for me. I'm on a 64 bit Windows 7 machine, and when I try to go down that road, I get into an endless loop of "you don't have the right version of SSMA / Access" etc; "you need to install the 64 bit version of Office" etc; that's not an option.

It's annoying that the upsizing wizard can't handle a UK date, but that appears to be the case. So I'm trying to figure out a workaround. I'm not an Access expert. The ideas that occur to me include:

• exporting the table to a tab delimited text file, then using SSIS to migrate it, and doing a derived column transformation to get the data

• creating a calculated field on the table in Access, getting the data into the new column, and deleting the original column. (But, also annoyingly, that's not working either; when I follow the instructions from MS and it says "Access displays the expression builder", well... Access doesn't display the expression builder.

Any suggestions appreciated, thank you

Answer 1

I came up with an ugly manual hack. No doubt there are many, more elegant programmatic solutions that VBA experts would know, but I've got bigger fish to fry right now and there's only one date column that was causing me problems. But I did learn a couple things about Access. I'll share the hack here for whatever it's worth.

• The expression builder would not appear because it was an old (Access 2000) .MDB file. I converted it to an .accdb format (Save & Publish, in Access 2010, lets you convert). Then the expression builder becomes available for calculated fields on a table.

• I created four calculated fields; one for each of the date parts, and one final text field to concatenate them; then I created a fifth (text) column, and manually copied the values in the data table view (I told you it was a hack). The expressions for the calculated columns were (let's say the first three calculated columns are called 'TheYear', 'TheMonth' and 'TheDay'):

Year([TheBritishDate])
Month([TheBritishDate])
Day([TheBritishDate])

Then the concatenating column expression, which is a calculated text column, with a couple of IIF expressions to deal with single digit values (since there don't seem to be any "PAD" functions in Access):

[TheYear]+'-'+IIf(Len([TheMonth])=2,[TheMonth],'0'+[TheMonth])+'-'+IIf(Len([TheDay])=2,[TheDay],'0'+[TheDay])

This produces a text field with a date format like SQL: e.g. '2013-12-31'.

Then I just selected the column in the table, Ctrl-C, and Ctrl-V into a new empty text column. Then delete the original offending column, and the calculated columns. The end result is a properly formatted (text) column which can then be upsized without error and manipulated in T-SQL as desired.