Database to Quickbooks Integration

DBSync – Database to Quickbooks Integration

• Configure your Database for Integration with your Quickbooks file. 
• For this Tutorial I will be using SQL Server Database Named "DBSyncDB" and 2011 US Edition of Quickbooks.
• Create tables "account" and "invoice" in Database with following script.

USE [DBSyncDB]

CREATE TABLE [dbo].[account](
	[id] [varchar](20) NULL,
	[Name] [varchar](50) NULL,
	[phone] [nchar](20) NULL,
	[fax] [nchar](20) NULL,
	[billingstreet] [varchar](200) NULL,
	[billingcity] [varchar](50) NULL,
	[billingstate] [varchar](50) NULL,
	[billingcountry] [varchar](50) NULL,
	[billingcode] [varchar](50) NULL,
	[company] [nchar](100) NULL
) ON [PRIMARY]

GO

USE [DBSyncDB]

CREATE TABLE [dbo].[invoice](
	[invoice_no] [nchar](10) NULL,
	[customerid] [nchar](100) NULL,
	[item] [nchar](100) NULL,
	[itemdesc] [nchar](100) NULL,
	[quantity] [nchar](10) NULL,
	[uom] [nchar](50) NULL,
	[salesprice] [nchar](10) NULL,
	[total] [nchar](10) NULL,
	[billaddress] [nchar](100) NULL,
	[billingcity] [nchar](100) NULL,
	[billingstate] [nchar](100) NULL,
	[billingpostalcode] [nchar](10) NULL,
	[billingcountry] [nchar](100) NULL,
	[date] [nchar](50) NULL
) ON [PRIMARY]

GO

* Make sure both the tables have sample data in them before they can be mapped within DBSync.

Database Setup

• Open www.mydbsync.com in your browser and click on Customer Login.
• Enter in your DBSync username and password to login.
• Once logged in, click on My Home from the menu and click on Launch button in the following page as shown.
• Once logged into the DBSync console, click on Create Profile button as shown below.
  

• You can use any naming convention for a profile as long as there are no white spaces in the name of the profile.
• Once the profile is created, click on Manage — Edit to configure the adapters.
• Click on the Create Adapter on the top left hand side of your window and select the required adapters from the drop down menu as shown.
• Enter the name for the adapter and select Database Adapter from the drop down. Click on Save button once done.
• Similarly, click on the Create Adapter button again to add Quickbooks Adapter to your DBSync profile.
• Once both the adapters are added, you will see them listed within the console page as shown.
• Now click on the Edit link next to the Database Adapter and enter your Database credentials as shown. In this tutorial, the database used is SQL Server 2005 and corresponding credentials are entered.

Note : If you are using a different Database server, you can enter the connection string for the same as shown here.

• Click on Validate link for the Database adapter. If the credentials entered are accurate, you will get a pop-up message saying the "Connection Settings are Valid".
• At this point you have successfully validated your Database Adapter.

Quickbooks Setup

• Click on the Edit link next to the Quickbooks adapter in the console page.
• Under the file section, enter the full path of your Quickbooks file as shown.
  

• You can retrieve full path / location of your Quickbooks file by pressing 'F2' key on your keyboard when in Quickbooks 'Home' page as shown below.

• Click on configuration Link at the bottom of the page. A Pop-up box will appear, prompting you to open the file with "QuickBooks Web Connector (default)", click on OK button as highlighted below in screenshot.

• Click on OK in the next window (first screenshot below) and on the next window select the option button "Yes, always; allow access even if QuickBooks is not running" and check the check box for "Allow this application to access personal data" and click on continue button (second screenshot below).

• In the Access Confirmation window click on the Done button. And the profile will get added to your QuickBooks Web Connector. A screenshot of the QuickBooks Web Connector is displayed