Click here to Skip to main content
15,939,620 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello all,
How can i migrate my access tables to sql server12?

What I have tried:

try to import tool in sql server but i cant create the tables
Posted
Updated 3-Jul-16 18:12pm
Comments
CHill60 13-Jun-16 8:42am    
Why can't you create the tables? What is the error message?
Member 10525430 13-Jun-16 8:48am    
its said its succesfull but i cant see the table on database>tables part
phil.o 13-Jun-16 9:05am    
Right-click on the database -> refresh (or update)
Member 10525430 13-Jun-16 9:11am    
hahaha
i tried it many times and nothing change

It's very simple. Follow these steps to plop all Access table onto SQL Server:

1. Open ODBC Data Source Administrator. This windows app is located in C:\windows\syswow64\ ... (hint hint) ... if you can't find it under Administrative Tools on the Start Menu.

2. Tab to "FileDSN" and click "Add..." button. Scroll to the end of the driver list that pops up and select "SQL Server" then click "Next>".

3. In the (Browse) textbox type a name for the assignment which will take place on the "Next>" dialog step. Click "Next>" for that.

4. Click on "Finish" in the next. Another dialog will popup again, this time stating it's the SQL Server of the name you typed previously. Type in a description and choose the SQL Server you want as the target for your Access tables. That "server" choice will probably not show up as a dropdown choice. So type the instance in to prime the pumps (sts) ... like {NAMECOMPUTER}\{NAMEINSTANCE}. Hit "Next>".

5. Keep authentication as default Windows NT. Hit "Next>" again.

6. Generally, and finally here in ODBC setup, you can leave the default database as-is (master). Until you get a feel for using the SQL Server itself, changing this right now would be unwise. Click "Next>"

7. And on the last page click "Finish". A popup will then show up displaying all ODBC knows about the data source configuration. Hit "OK" to dismiss.

8. Back in the "FileDSN" user directory you'll now see displayed the newly created source as a .dsn extention. Click "OK".

9. Back in Access, get a display of the name of your data table in "All Tables" panel, right-click & in the context menu select "Export/ODBC Database". The "Export" popup will appear and hitting "OK" should cause the "Select Data Source" dialog to show up again, and since this dialog should open where you left off creating the new .dsn, just scroll right and select that new .dsn now. If you created this thing somewhere other than Administrator directory you might have to hunt around for it. And you could probably create another .dsn (File Data Source) anywhere also. But make sure you "find" an SQL Server. Hit "OK".

10. Way down lower right in Access a neon green progress bar will alert you to the success of the plopping.

11. You'll be looking for the results of this shuttle in the {NAMECOMPUTER}\{NAMEINSTANCE} that initialized that .dsn you used. Under master.dbo ...
 
Share this answer
 
You can easily migrate Access database to SQL Server by using SQL Server Migration Assistant (SSMA)
Migrating Access Databases to SQL Server - Azure SQL DB (AccessToSQL)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900