?
Solved

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

Posted on 2014-08-26
13
Medium Priority
?
324 Views
Last Modified: 2014-09-01
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
0
Comment
Question by:Jason Laskey
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 40286392
create a database, from ssms, right click on the newly created database -> all tasks -> import data
choose the  data source as the access
0
 
LVL 40

Assisted Solution

by:PatHartman
PatHartman earned 1000 total points
ID: 40286412
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
 
LVL 49

Expert Comment

by:Dale Fye
ID: 40286440
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 40

Expert Comment

by:PatHartman
ID: 40286647
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
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40287327
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
 

Author Comment

by:Jason Laskey
ID: 40287393
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
 

Author Comment

by:Jason Laskey
ID: 40287451
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
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 1000 total points
ID: 40287469
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
 

Author Comment

by:Jason Laskey
ID: 40287528
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
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40287577
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
 
LVL 40

Expert Comment

by:PatHartman
ID: 40287969
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
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 40288242
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
 

Author Closing Comment

by:Jason Laskey
ID: 40296457
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question