Migrate Access Database to SQL Server - Invoice Manager for Excel

Step 2 - Test Connection with SQL Server Management Studio

Download and install Microsoft SQL Server Management Studio (https://msdn.microsoft.com/en-us/library/mt238290.aspx). Connect to the remote database with the server name, database name, user (login) name, and password.

Step 3 - Connect SQL Server Using Invoice Manager for Excel

Now start Invoice Manager for Excel. Click Settings and go to the Database tab, and then choose "SQL Server Database" as the database type.

On the above figure, Invoice Manager for Excel is connecting with an Access database (that is why the "Access Database" tab is marked as "Active"). To connect a SQL Server on the Internet, on the "SQL Server" tab, you should fill in the fields "Server Name", "Authentication Mode" (This is usually "SQL Server Authentication" for SQL Servers on the Internet; and for the SQL Servers running on the local domain, it is usually "Windows Authentication"). See Authentication in SQL Server for more information.

In case of "SQL Server authentication", you need to also fill in "Login name" and "Password". "Database name" is also a required field.

If this database (as specified in the "Database name" box) is not created on the server side yet, check "Create new database". Note that if your SQL Server is running on the Internet using a shared web site hosting account, your SQL Server account may not have the permission to create database this way. In this case, the database should be created using the web hosting control panel, such as Plesk.

If your database is created from the Web hosting control panel, it is an empty database that does not contains the required tables to run Invoice Manager for Excel. Click "Connect to SQL Server" button, Invoice Manager for Excel will create the tables automatically.

If Invoice Manager for Excel connects with the specified SQL Server database successfully, it saves the connection in the invoice template. To make this the default connection (so that the program restores the connection next time you start Invoice Manager for Excel), you should save the template by clicking the "Save" button on Excel quick access toolbar.

Step 4 - Verify and create custom fields

If your template is connecting with an Access database, and you create your SQL Server tables with the Access database connected, then all custom fields defined in the Access database tables are created on the SQL Server database too. However there are situations where the SQL Server tables contain only standard fields only.

To make the later importing procedure smoothly, it's better to verify if the custom fields are created identically as in the original database.

1. Open your invoice template as usual.
2. Make sure it is connecting to your "old" database -- i.e. the database that contains the data to be exported.
3. Click "Customers" on the "Invoice" ribbon tab, and then "Custom fields".
4. From the "Database table" list, choose "Customer".
5. Write down "Name" and "Type" of each custom field.
6. Repeat step 5 and 6 for other tables -- "Shipping Address", "Product", "Invoice Header" and "Invoice Body".
7. Now connect to your new SQL Server.
8. Click "Customers" on the "Invoice" ribbon.
9. Click "Custom Fields".
10. Select a table from the "Database table" list.
11. Click "Add field" to add a custom field. Make sure the "Name" and "Type" match exactly what you have write down.
12. Repeat step 10 and 11 to create all the required custom fields for other tables.

Step 5 - Import Existing Invoice Manager for Excel Data

You can use "SQL Server Import and Export Wizard" to import existing data, from either local Access database or local SQL Server database, to an online remote SQL Server database.

Before begin, first make sure you are able to connect to the remote SQL Server database from Invoice Manager for Excel, as detailed above. This also makes sure that the database contains all the tables required by Invoice Manager for Excel, and all the fields in the database are defined exactly as required by Invoice Manager for Excel.

SQL Server Import and Export Wizard could be start from Windows 10 search / command box or from within SQL Server Management Studio. Let's say you have started SQL Server Management Studio. From the "Object Explorer" located on the left side, expand "Databases" node and then right-click any database (not necessary the destination database). Right-click the database name and choose "Task" and then "Import Data". This starts "SQL Server Import and Export Wizard".

Click "Next" on the welcome screen to go to the "Choose a Data Source - Select the source from which to copy data" page, as shown on the figure below.

From the "Data source" drop down list, choose the type of your database. If your source data is in an Access database, then choose "Microsoft Access (Microsoft Access Database Engine)", the wizard then shows options that allow you to browse to and specify the Access database file used by Invoice Manager for Excel.

If your local database is a SQL Server database, choose "Microsoft OLE DB Provider for SQL Server" or "SQL Server Native Client". The wizard shows options to let you specify the login information for the SQL Server.

Click Next to go to the "Choose a Destination - Specify where to copy data to" screen, as shown below.

Assuming we are going to copy data to a remote SQL Server database, from the "Destination" drop down list choose "Microsoft OLE DB Provider for SQL Server". And then specify the server name (or address), authentication mode, and in case of "SQL Server authentication", you'll also need to provide "User name" and "Password". Click "Refresh" to connect to the SQL Server and have the "Database" drop down list populated. You can then choose your destination database, or fill in the database manually.

Click "Next" to go to the "Specify Table Copy or Query" page. Since we want to import data from a local database to a remote SQL Server database, choose "Copy data from one or more tables or views".

Click "Next" to go to the "Select Source Tables and Views" screen. Check the box before each table name from where you want to import data.

Note that don't import data from the "InvOption" table and "InvCost" View.

Once a table is selected, click "Editing Mappings" to verify column mappings. Select "Append rows to the destination table" if you are adding additional data to the destination table; or select "Delete rows in the destination table" if you want to empty the destination table before adding data to it. Also make sure each column in the source table is mapped correctly to the column in the destination table. Check the "Enable identity insert" option.

After verifying the mappings, click "Next" to start importing data.

Delete Existing Objects

If some objects were already created during the previous unsuccessful importing process, it is necessary to delete all those objects before running the new importing procedure. To delete these objects:

1. Start SQL Server Management Studio.
2. Connect to your SQL Server.
3. From the left pane, expand the "Database" node.
4. Find and expand your database name.
5. Delete all objects under <database name>\Tables and <database name>\Views that has the "dbo" prefix in their names. See figure below.

   

6. Once done, start to import your database.