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

create a database, from ssms, right click on the newly created database -> all tasks -> import data
choose the  data source as the access

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.

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.

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

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

IS there not an TSQL script I can run to imprt Access.mdb into a new Database in SQL Management Studio called Access.mdf ?

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

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

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.

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

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.