Microsoft Access is a rather popular relational database system that has quite a few advantages. It is very easy to install and manage, no matter whether you are a beginner or a seasoned database user. It has a set of flexible tools for data migration. It has a large community and lots of helpful resources all over the Internet, so you won’t have any trouble getting in-depth insights into it. Finally, Access is available as part of the Professional and higher editions of the Microsoft 365 suite, which makes it an affordable addition to Microsoft’s flagship office tools such as Word, Excel, and Outlook.
But of course, there are lots of drawbacks and limitations to it. The features and capacities of Access can’t even hope to get close to its more advanced counterpart — Microsoft SQL Server. And it’s no wonder that many businesses that once started with Access eventually have to scale to a database system that delivers more firepower, such as SQL Server, MySQL, MariaDB, Oracle, or PostgreSQL.
If that’s your case, chances are that you are already struggling with the migration of your tables from Access to one of the abovementioned databases. That is why we are going to show you how to do it most painlessly using dbForge Edge, a multidatabase solution that covers all of these systems with comprehensive IDEs.
There are four IDEs included in dbForge Edge—Studio for SQL Server, Studio for MySQL, Studio for Oracle, and Studio for PostgreSQL—all of which offer support for up to 14 most widely used data formats for import and export. And without further ado, let’s show you how to bulk import table data to a SQL Server database using the corresponding Studio.
Contents
- Prerequisites
- Bulk data import from Microsoft Access to SQL Server
- Error handling
- Adapting the process for other databases
Prerequisites
Now what do you need to get started?
- Your source Access database—one or multiple MDB files that contain all the tables you want to import
- Your target SQL Server database, fully set up as you require and ready to get your data imported
- dbForge Edge, downloaded for a free 30-day trial and installed on your machine; alternatively, you can download and install dbForge Studio for SQL Server individually
- Once it’s all ready, open the Studio and establish a connection to your target SQL Server database
Bulk data import from Microsoft Access to SQL Server
Before we start, let us note that currently, the methods of importing individual and multiple tables from Access to SQL Server are different. In case you need a detailed guide on importing one Access table at a time, carefully and thoroughly going through data format settings, table mapping, import modes, error handling, and so on, refer to our blog post “How to Convert a Microsoft Access Database to a SQL Server Database“.
When it comes to bulk import, we’ll choose a different way that involves the command line. Take a look at the following example.
"C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com" /dataimport /templatefile:access.dit /table:sakila.dbo.country /inputfile:dbo.country.mdb /inputtable:country
"C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com" /dataimport /templatefile:access.dit /table:sakila.dbo.city /inputfile:dbo.city.mdb /inputtable:city
"C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com" /dataimport /templatefile:access.dit /table:sakila.dbo.address /inputfile:dbo.address.mdb /inputtable:addressThis example shows import from three tables; and in the same way, you can do it for dozens and even hundreds of tables. First, you need to write down a similar set of commands for all the tables you want to import, where:
- In /inputfile, you specify the name of the required MDB file
- In /inputtable, you specify the name of the source table from the said MDB file
- In /table, you specify the full name of the target SQL Server table, which includes the corresponding database
You should also take note of /templatefile, which must be created in dbForge Studio beforehand. To create it, go to the Database menu > Import Data to open the Data Import Wizard. Select MDB as the required format.
In the lower left corner of the Wizard, select Save Template from the dropdown menu next to the Save button, specify the name and path to your template, and click Save. For everything to run correctly, make sure that the template and all the involved files are stored on your main C: hard drive.
Optionally, in the same lower left corner of the Wizard, you can go to Save > Save Command Line to open Command line execution file settings. Paste your set of commands into the text box and click Validate to make sure they don’t contain any errors.
Now let’s see our example in action, shall we? We’ve got empty tables like this one, ready to be filled with data.
We open the Command Prompt and run the commands as follows. Note that it is obligatory to open the Prompt as an administrator.
Done! Now let’s make sure that our data has been successfully imported.
Now let us briefly show you another example. What if you need to run your bulk import from Access to SQL Server through an ODBC connection? Then the command may look as follows:
"C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com" /dataimport /templatefile:odbc.dit /table:database.dbo.country /inputtable:dbo.country
"C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com" /dataimport /templatefile:odbc.dit /table:database.dbo.city /inputtable:dbo.city
"C:\Program Files\Devart\dbForge Studio for SQL Server\dbforgesql.com" /dataimport /templatefile:odbc.dit /table:database.dbo.address /inputtable:dbo.addressAs you can see, the syntax is similar to that of the previous example. Note that the source database is specified when configuring your ODBC connection.
By the way, if you work with ODBC, you can get your fair share of benefits from Devart ODBC Drivers, high-performance connectivity solutions that cover any integration you might require, enabling fast and secure access to data. Specifically, we can suggest ODBC Driver for SQL Server that, among other things, delivers seamless compatibility with Access.
Error handling
To configure error handling behavior, add /errormode to your command. The possible options are as follows:
- /errormode:ignore — ignores all errors
- /errormode:abort — aborts the operation upon encountering an error
This is what it looks like in the Data Import Wizard.
Adapting the process for other databases
Now what if you need to perform the same operation with another database system? Never easier—just do the same thing in another corresponding Studio with your code adjusted accordingly. For instance, this is what the import of a table from the abovementioned example will look like in dbForge Studio for Oracle.
"C:\Program Files\Devart\dbForge Studio for Oracle\dbforgeoracle.com" /dataimport /templatefile:access.dit /table:database.dbo.country /inputfile:country.mdb /inputtable:countryDownload dbForge Edge for a free 30-day trial today!
The capabilities of each Studio included in dbForge Edge go far beyond data migration. Let us just briefly list the essentials that you most likely need in your daily work as well.
- Visualization of database structures on ER diagrams
- Visual table design
- Enhanced SQL coding: context-aware completion, formatting, refactoring, and debugging
- Visual query building that does not require any coding
- Query optimization via profiling
- Flexible data management in a visual editor
- Identification of differences in database schemas and table data
- Quick synchronization of source and target databases
- Data analysis: data aggregation in visual pivot tables, observation of related data in Master-Detail Browser, and generation of detailed data reports
- Generation of realistic test data
- Generation of full database documentation
- Administration: database and server monitoring, backup/recovery, user and session management, and more
You can get started with dbForge Edge today—just download it for a free month-long trial and give it a go to see the full power of its capabilities. We bet they won’t leave you indifferent.
