How can I import a MS Access Db into a new SQL database via SQL Managment Studio

Hello Gurus,

Is there anyone who knows how to import a MS Access *.mdb database into a new Database in SQL Managment Studio or even via TSQL?

Look forward to your response and thank you for taking the time to assist

Jason Laskey
Jason LaskeyICT Systems and Network EngineerAsked:
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.

Aneesh RetnakaranDatabase AdministratorCommented:
create a database, from ssms, right click on the newly created database -> all tasks -> import data
choose the  data source as the access
0
PatHartmanCommented:
You can also download the SSMA (SQL Server Migration Assistant).  This comes in both 64-bit and 32-bit versions and you may need to search for the 32-bit which is what you will most likely need since bit-wise, you need to match your Access version.

Versions of Access prior to A2013 included an upsizing wizard.  Although I wouldn't use this to create a production database, it is a pretty simple way of converting to a test database.  I used this almost exclusively prior to A2013 mostly because I was never responsible for creating the final production database.  A DBA always did that.  I just gave him the schema and filled it with data.
0
Dale FyeCommented:
I would go with the SSMA mentioned by Pat.

Before you do this, I would recommend that all of your Access need to contain a primary key, or you will not be able to edit or delete records from those tables from Access.
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:
I had to run off to a meeting so let me continue where Dale left off.  In addition to defining primary keys, make sure all your data types are consistent relative to length and type.  Access is more flexible than SQL Server when it comes to joining tables.  For example, if you use the 2-digit State code rather than an ID field, make sure that the State field is 2-digits in ALL tables where it appears.  Access defaults to 50 characters for text fields so you may have missed changing one.  There are a couple of data types you need to be careful with.  Y/N fields are converted to bit in SQL Server and bit does NOT allow null.  The field is 0 or 1 and that's it so if you have any Y/N fields that are null, change the data type to integer before the conversion or replace all the null values with either true or false depending on what would be the appropriate default.  SQL Server has added several new "date" data types and not all are compatible with older ODBC drivers so be specific when using SSMA as to what "date" data type you want to convert to in order to avoid issues.

I also clean up my relationships.   Then I run the upsizing wizard or SSMA as many times as it takes to achieve a clean conversion.
0
BitsqueezerCommented:
Hi Pat,

little correction: of course SQL Server allows NULL on bit values. I don't remember any datatype in SQL Server which is not NULLable, would be strange if not. The real difference is that a Y/N-field is -1 in Access where it is 1 in SQL Server but that is automatically changed during migration.

If the frontend to be used is Access then a date field should be datetime or smalldatetime. In any other case there should be no problem using other datatypes. The reason is that Access as ACCDB/MDB uses ACE/JET as "database-in-the-middle" and that doesn't support the new datatypes.

Cheers,

Christian
0
Jason LaskeyICT Systems and Network EngineerAuthor Commented:
Hello All and thank you for your response.

I am currently at work attending to this problem and need urgent reposense for this please
I have downloaded MSSA http://www.microsoft.com/en-za/download/details.aspx?id=42656
and installed it. However when I open the Access db and try to import it , I recieve the following error (see attachment)
Also once I have imported this into the SQL Mngmnt studio and ran my Left join SQL.Can I convert the new "Left join" SQL DB back to an MS Access DB???
SSMA-Error1.png
0
Jason LaskeyICT Systems and Network EngineerAuthor Commented:
IS there not an TSQL script I can run to imprt Access.mdb into a new Database in SQL Management Studio called Access.mdf ?
0
BitsqueezerCommented:
Hi,

that error comes from using the wrong version of SSMA, either 64 or 32 bit depending on your system. Both versions should be installed on your machine and should be available in the start menu, normally the 32bit version is the correct one.
See also:
http://social.technet.microsoft.com/Forums/windowsazure/en-US/781f522d-64ce-4c12-a7d6-d2d212638c8b/ssma-error-80040154-on-windows-7-64-bits
http://tokyoimage.wordpress.com/2010/08/10/microsoft-sql-server-migration-assistant-for-access-issue-on-64bit-systems/

You can of course create a T-SQL-Script to create and import the data based on your Access database. But that would be a lot of manual work as without a tool there's nothing which creates such a script and there is of course no standard script which can manage all kind of external inputs.

If you have the full version of SSMS (not the Express version) then you have also an importer there, right click on the database name, then under "Tasks" and "Import Data". This can work with Access databases, but unfortunately only with MDBs. So you need at least to convert your database to MDB if it is an ACCDB, but that should be no problem. The importer is not easy to manage, the SSMA is of course the better choice. The same tool can also be used to export SQL tables into Access tables (or Excel or whatever you want).

I don't know what you mean with "your left join SQL".

Cheers,

Christian
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
Jason LaskeyICT Systems and Network EngineerAuthor Commented:
OK so far so good I am at the last stage now of importing this MD file but when I specify the following I get an error (See Attachments)
SSMA-Error2.png
SSMA-Error3.png
0
BitsqueezerCommented:
Hi,

that's a connection specific problem. You should check if you get a connection with SQL Server Management Studio on the machine where you use SSMA, if yes, then the server is configured as it should, if not, you first need to check any usual connection problems like Firewall and Ports, server configured to allow remote connections, TCP/IP and NamedPipes-protocols activated (see SQL Server Configuration Manager), server can be pinged using the short server name, if not, change the "ServerName" in your connection to the FQDN (full qualified server domain name) or use the IP address instead. Some frontends also need to have the SQL Server Browser service enabled on the server.

If you have a 64bit SQL Server it may also have problems to connect from a 32bit SSMA, but that I don't know, I never used that.

Cheers,

Christian
0
PatHartmanCommented:
Jason,
I see that you ran into the bit-wise issue I warned about.  The install gives you no indication that two versions are installed and it makes the assumption that you will want to use the 64-bit version because most PCs these days are 64-bit hardware and therefore run 64-bit windows which doesn't mean they run 64-bit office and most don't so it is a really stupid assumption.  But who said Microsoft installs are logical.

bitsqueezer,
I wonder if I ran afoul of some SSMA setting (like with the default date type format being incompatible with Access) because none of my Y/N fields would convert if they had null values.
0
BitsqueezerCommented:
Hi Pat,

maybe the bit issue is a problem of the SSMA while converting? I don't know as I don't use that tool.

Cheers,

Christian
0
Jason LaskeyICT Systems and Network EngineerAuthor Commented:
Your solution was 100% correct the issue why I was getting this error was due to a corrupt *.mdb file so I ran a repair on the DB and then I was able to import the DB into SQL. Thanks allot for your help as this has helped me.
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 2008

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.