Last post Jun 20, 2016 05:58 AM by ijmar86
Jun 20, 2016 05:27 AM|Shibly|LINK
I've a database in ms access which is in (.accdb) form with some updated tables information. Now I want to insert the updated table information into sql server. I already have the database in the sql server. I just want to update the table information which
is on the access database.So, Is there any way to import this information from access database to sql server database.
Thanks in advance.
Jun 20, 2016 05:58 AM|gaurabchatterjee|LINK
it is simple,
Hope the following steps will help .
Microsoft has made importing data from Access much easier using Access 2007 or above than previous versions. Running
SSIS packages or using the Import/Export wizard seemed time consuming and often would error out requiring more troubleshooting than it's
worth. I'm using an Access database with two tables named Customers and Orders for this tip:
First, make sure all tables are closed within in our Access database or we'll get an error:
Once all tables are closed navigate to Database Tools | SQL Server as shown below:
After clicking SQL Server an Upsizing Wizard dialog box should appear. Since I don't have a database created I will click "Create New Database" option as shown below.
If there was already a database in place clicking "Use existing database" will bring up a few screens to setup a data source to a preexisting database. After clicking next, we need to enter some information to connect to our SQL Server and create our database.
When specifying the Login ID make sure this user has CREATE DATABASE permissions on the server. For this example I will use SQL2008 for my server
and create a database called Bama:
After clicking next, you should see a dialog box displaying your Access tables in the left column. Simply click the tables you want to import and click the right arrow seen here and click the "Next > " button. In this example we are importing the Customers
and Orders tables as shown below:
The Upsizing wizard can export table attributes i.e. indexes, defaults, etc. as well as data as you
can see in the next screen.
After choosing your table attributes click "Next >" and you will see a dialog box where you can modify the existing application or create a new application to work with the database. I will choose "No application changes" for this example and click the "Finish"
The Upsizing wizard will then start importing data:
...and display the Upsizing Wizard Report:
To confirm a successful import we can open SQL Server Management Studio and drill down to Databases where we will see our new database Bama, and our two new
tables, Customers and Orders.
To confirm our data was imported we can query our Customers table using:
SELECT * FROM Customers
Jun 20, 2016 05:58 AM|ijmar86|LINK
Check the below link, it has a video of how to migrate data from access to SQL Server