Import From Access, No Indexes Kept

We are moving our Access MDB (2003 format) to SQL server.  Every table in the Access DB has a unique primary key/index defined and most have several other indexes also.

I used the SQL Import Wizard to create a SQL DB with all of the Access tables.  It went very smoothly and all tables were imported.

When I look at the tables in SQL Management studio none of them have any indexes, or a primary key defined.

Is this the way they are always imported or did I miss an option on the import to pull the indexes into SQL with the tables?

Is it possible to pull the indexes with the tables using the Import wizard?
LVL 1
mlcktmguyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I actually would start over with that and use the SSMA (SQL Server Migration Assistant):

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

  It does a much better job than the wizard and will find if there are any problems in moving the data.  In fact the built in wizard has now been removed from Access.

One note: by default, it does not add a timestamp column to the SQL tables.  You need to check this option on in the options before you do your conversion.   Every table accessed by Access should have a timestamp field added.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nick67Commented:
I used the SQL Server Migration Assistant of MS Access which is a much better tool for doing what you did than the Upsizing wizard in Access.  I cleaned up issues that it flagged before moving the data, and my PKs relationships and indexes were migrated.

I would use SSMA (free, too) and not the wizard.
You can script a possible upgrade to see what you will get.
0
Deepak ChauhanSQL Server DBACommented:
No Import Wizard does not create indexes.
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.

PatHartmanCommented:
@deepakChauhan, The import Wizard always created them for me.  

@mlcktmguy , You probably forgot to check the "Use Declarative RI" button or something like that.  I only have A2013 so I can't review the options you have.

Personally, I found the upsizing wizard to be much easier to use.  SSMA has a fair number of gotcha's starting with figuring out that you need to install the bitwise version that matches your Office installation rather than your OS.  You also need to be careful regarding the data type that SSMA uses to convert your Access dates.  The current default translates as a string if you are using the default SQL server driver.  You either need to force the date to convert as datetime or switch everyone to use a newer Native Client driver.  Plus, even with the newest driver, Access still sees long int's as text strings and that seems to be all the rage in table keys these days.
0
Nick67Commented:
The bitwise thing was a pain.
Everyone just about runs 64 bit Windows, and almost noboby runs 64 bit Office.
The SSMA download site punches down a single executable now.
http://www.microsoft.com/en-us/download/confirmation.aspx?id=43690
I was running v5.1.1105.
The type mapping screen was fairly straight forward.
This is a shot from a new project.
Long-to-int and date to datetime2[0] are what my defaults are now (though I may have set them long ago)
screenshot
The first conversion I did was tough.
These days, I keep SSMA running so I can do table design in Access and flange them into SQL Server afterwards if it suits -- or when whole separate Access apps are being merged in.
I'm tearing out 5.1 and blowing in 6.0.0 right now...
If it's a lot different, I'll post.
***edit***
Looks exactly the same.
The last major use I made of it was to push ~120 tables to SQL Azure.
Install was pretty smooth.
7 MB download, 11 MB installed.
My default data mappings remained the same as in the screenshot.
0
mlcktmguyAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.