up vote 1 down vote favorite
share [g+] share [fb]

I am working on upsizing an MS-Access database from Access 2010 to MS-SQL 2008 (10.0.1600). When I run the Upsizing Wizard, all the tables get moved over except for one...

The table that doesn't get moved has ~18,000 records and many of the column names have spaces in them. I am just unsure of how to find out why the table is not getting moved. The error that I receive only tells me that "Tab was skipped, or export failed." I don't know how to get Access to provide me with a more useful error than that.

So far I have confirmed that the error is not being caused by:

  • Invalid data types (yes/no, date, etc.)
  • Spaces in column names
link|improve this question
feedback

4 Answers

up vote 0 down vote accepted

The upsizing wizard that comes with Access is always behind the curve, as it can only deal with older versions of SQL Server.

The proper tool for upsizing is the SQL Server Migration Assistant for Access. It is much more flexible and much smarter about what it can do. It is also updated to reflect the latest version of SQL Server.

With it, you can preview what the upsizing operation will do and correct problems if there are any. The one thing I've found that it can't do is properly interpret certain kinds of field-level validation rules. Otherwise, it's quite good with flagging problems and identifying exactly what needs to be changed.

link|improve this answer
Thanks David. I can understand the Access upsizing wizard only working with previous versions of SQL Server, but for Access 2010 to not work with SQL Server 2008 is a bit unbelievable in my opinion. – Dave Long Apr 26 '11 at 0:20
R2 came out after A2010, I think. Or maybe it was just a few months the other way, but the main issue is that they were in development at the same time and maybe it couldn't be fit in the schedule. The SSMA is simply much more versatile and flexible than the upsizing wizard, mostly because it allows you to test the process to see what doesn't work properly, so that you can then make changes to your Access database (and the data it contains) to avoid the problems. – David W. Fenton Apr 29 '11 at 1:13
feedback
up vote 1 down vote

I ran into a similar problem. It turned out that I had a field that was set to auto increment as a identity field. The table structure was brought over to SQL Server but no data was imported. I have not had a chance to move that far yet but my plan is to set the identity property to "NO" and then after successfully splitting the database, set the property back to "YES". This is probably just one of many possibilities.

link|improve this answer
feedback
up vote 0 down vote

Do any of your other tables have spaces in their column names? If not, then you could try renaming the columns by changing the spaces to something like underscores ("_") and then use "ALTER TABLE" SQL commands on the MS-SQL server console to change the columns back (if it's supported).

But, using spaces in your column names is not recommended because it can lead to all sorts of complications (particularly with quoting portions of SQL commands and preventing injection attacks, etc.) and problems, and especially where more developers are involved. I recommend switching over to underscores and updating your code accordingly as well.

link|improve this answer
I was assuming the issue was underscores. Is there a fast way to change about 100 column names? Something regex searching for spaces and replacing with underscores. – Dave Long Apr 21 '11 at 15:38
In MS-Access? I'm not aware of it, but I haven't used MS-Access for over a decade now so it's possible that there is an option. A Regular Expression that changes spaces to underscores could look something like this: / /_/ – Randolf Richardson Apr 21 '11 at 15:40
spaces weren't the issue. Changed all to underscores and I still get he vague error. – Dave Long Apr 21 '11 at 15:57
Well, at least you've ruled that out. I checked in to the maximum number of columns permitted, and MS-SQL seems to have a limit that's much higher than 100, so that's probably not it either. =( – Randolf Richardson Apr 21 '11 at 16:02
Are you using any special characters in any of the column names? They should all start with a letter or an underscore, and contain only letters, digits, and/or underscores. If you have other characters in there, then that could be a problem. – Randolf Richardson Apr 21 '11 at 16:03
show 4 more comments
feedback
up vote 0 down vote

for grins (should be quick to try for trouble-shooting/sanity-checking) run a make-table query containing all the fields but with very short names in the output fields (i.e. fname:[User First Name] ), conforming all nicely, short, one word. You'll then have a duplicate table and you can mess with data type updates and so on as you modify the table and try and upsize it

hopefully someone will chime in with the real technical limitation your running into, but when I run into this stuff, I find it's best to get to really basic structures and work my way up to what I want to do - small steps, one change at a time - your right to question the accuracy of the error messages - I've gotten such misleading messages from sql, and access specifically - not just vague, but plainly misleadingly, wrong that either the error message and problem are so common and specific that the answer is all over the first 5 pages of google results OR it's so bad, I just get back to bare bones basics and iterate to the complex thing I'm actually after

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.