<![CDATA[FLEXquarters.com Limited]]> <![CDATA[[QODBC-POS] Sample Code for Creating to SalesReceipt using PHP]]> Sample Code for Creating to SalesReceipt using PHP

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using an application for Creating to SalesReceipt using PHP.

Run the application.

You need to insert CustomerListId, Comments, Cashier, SalesReceiptType, Item ListID, Quantity, Rate & Amount, which you want to enter, and click on the "InsertSalesReceiptItem" button.

New SalesReceipt created.

Result in QuickBooks POS.

Note: Modify/Append existing SalesReceipt feature is not supported by QuickBooks SDK, so it is unavailable through QODBC POS.

Application Source Code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
    <title>QODBC PHP Script To Display SQL Results</title>

</head>
<body topmargin="3" leftmargin="3" marginheight="0" marginwidth="0" bgcolor="#ffffff" link="#000066" vlink="#000000" alink="#0000ff" text="#000000">


<table>
	<tr>				
		<td>
			SalesReceiptItem
			<form name="frmItemLine" id="frmItemLine" method="post">
			<table>
                <tr>
                    <td>Enter CustomerListId</td>
                    <td><input type="text" name="txtcustomerName" id="txtcustomerName"/></td>
                </tr>
                 <tr>
                    <td>Enter Comments</td>
                    <td><input type="text" name="txtComments" id="txtComments"/></td>
                </tr>
                 <tr>
                    <td>Enter Cashier Name</td>
                    <td><input type="text" name="txtCashierName" id="txtCashierName"/></td>
                </tr>
                 <tr>
                    <td>Enter SalesReceipt Type</td>
                    <td> <input type="text" name="txtSalesReceiptType" id="txtSalesReceiptType"/></td>
                </tr>
                 <tr>
                    <td>  Enter SalesReceipt ItemListID</td>
                    <td> <input type="text" name="txtItemName" id="txtItemName"/></td>
                </tr>
                 <tr>
                    <td>Enter Quantity</td>
                    <td> <input type="text" name="txtQuantity" id="txtQuantity"/></td>
                </tr>
                 <tr>
                    <td>Enter Item Price</td>
                    <td><input type="text" name="txtItemPrice" id="txtItemPrice"/></td>
                </tr>
                <tr>
                    <td>Enter Tender Amount</td>
                    <td><input type="text" name="txtTenderAmount" id="txtTenderAmount"/></td>
                </tr>
                 <tr>
                    <td colspan="2"><center><input type="submit" name="btnItemLine" id="btnItemLine" value="Insert SalesReceiptItem" /></center></td>                    
                </tr>
            </table>
			
			
			
			
			</form>
		</td>
	</tr>
</table>


    
</body>
</html>



<?php
if(isset($_POST['btnItemLine']))
{

	$customerName= $_POST['txtcustomerName'];
	$comments = $_POST['txtComments'];	
	$cashierName = $_POST['txtCashierName'];	
	$salesReceiptType = $_POST['txtSalesReceiptType'];	
	$itemName = $_POST['txtItemName'];	
	$quantity = $_POST['txtQuantity'];	
	$itemPrice = $_POST['txtItemPrice'];	
	$tenderAmount = $_POST['txtTenderAmount'];		
	
	if($customerName == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter CustomerName")';
		echo '</script>';
	}
	else if($comments == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter Comments")';
		echo '</script>';
	}
	else if($cashierName == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter CashierName")';
		echo '</script>';
	}
	else if($salesReceiptType == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter SalesReceipt Type")';
		echo '</script>';
	}
	else if($itemName == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter Item listId")';
		echo '</script>';
	}
	else if($quantity == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter quantity")';
		echo '</script>';
	}
	else if($itemPrice == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter itemPrice")';
		echo '</script>';
	}
	else if($tenderAmount == "" )
	{
		echo '<script language="javascript">';
		echo 'alert("Enter tenderAmount")';
		echo '</script>';
	}
	else
	{
		set_time_limit(120);
		$oConnect = odbc_connect("QuickBooks POS Data QRemote", "", "");			
		$sSQL = "Insert Into SalesReceiptItem (CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId, 		SalesReceiptItemQty, SalesReceiptItemPrice, TenderCash01TenderAmount) 		Values ('$customerName','$comments','$cashierName','$salesReceiptType','$itemName',$quantity,$itemPrice,$tenderAmount)";				
		$oResult = odbc_exec($oConnect, $sSQL);
		$query="sp_lastInsertID SalesReceiptItem";
		//$query="select @@identity";
		$result = odbc_exec($oConnect, $query);
		 odbc_result($result, 1); 
		$result1 = odbc_field_name ( $result , 1 );
		$resultcount = odbc_fetch_row($result);
		$item_val = odbc_result($result, "LastInsertId");		
		$sSQL = "SELECT CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId,SalesReceiptItemDesc1,SalesReceiptItemQty, SalesReceiptItemPrice, TenderCash01TenderAmount  FROM SalesReceiptItem where TxnID='$item_val'";
		//echo $sSQL;
		#Perform the query
		$oResult = odbc_exec($oConnect, $sSQL);
		$lFldCnt = 0;
		$lFieldCount = odbc_num_fields($oResult);
		//print("$lFieldCount");
		print("<table border=\"1\">");
		print("<th>Line No.</th>\n");
		while ($lFldCnt < $lFieldCount) {
			$lFldCnt++;
				$sFieldName = odbc_field_name($oResult, $lFldCnt);
			print("<th>$sFieldName</th>\n");
		}
		
		
				$lRecCnt = 0;
		#Fetch the data from the database
		while(odbc_fetch_row($oResult)) {
			$lRecCnt++;
			print("                    <tr>\n");
			print("                        <td>$lRecCnt</td>\n");
			$lFldCnt = 0;
			$lFieldCount = odbc_num_fields($oResult);
			while ($lFldCnt < $lFieldCount) {
				$lFldCnt++;
				$sFieldValue = trim(odbc_result($oResult, $lFldCnt));
				If ($sFieldValue == "") {
					print("<td> </td>\n");
				}
				else {
					print("<td valign=\"Top\">$sFieldValue</td>\n");
				}
			}
			print("</tr>\n");
		}
		print("</table>");
		
		odbc_close($oConnect);					
	}
}
?>
]]> <![CDATA[[QODBC-POS] Sample C#.NET Code for Creating SalesReceipt]]> Sample C#.NET Code for Creating Sales Receipt

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using an application for creating a SalesReceipt using C#.NET.

I am connecting to the default DSN QuickBooks POS Data in this example. Run the application, select the DSN & click on the "Connect" button. In this example. You can change DSN as per your configuration.

The application is connected with QuickBooks POS.

You need to insert CustomerListId, Comments, Cashier, SalesReceiptType, Item ListID, Quantity, Rate & Amount, which you want to enter, and click on the "Insert New SalesReceiptItem" button.

New SalesReceipt created.

Result in QuickBooks POS.

Note: Modify/Append existing SalesReceipt feature is not supported by QuickBooks SDK, so it is unavailable through QODBC POS.

Application Source Code:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace QODBC_SalesReceipt_Insert_Sample
{
    public partial class Form1 : Form
    {
        private OdbcConnection _cn;
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            try
            {
                ListOutDSN();
                Application.DoEvents();
            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
            
        }

        private void ListOutDSN()
        {
            cboListOfDSN.Items.Clear();

            var myODBCKeys = Microsoft.Win32.Registry.LocalMachine.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", false).GetValueNames();

            foreach (var item in myODBCKeys)
            {
                cboListOfDSN.Items.Add(item);
            }
            myODBCKeys = Microsoft.Win32.Registry.CurrentUser.OpenSubKey("SOFTWARE\\ODBC\\ODBC.INI\\ODBC Data Sources", false).GetValueNames();
            foreach (var item in myODBCKeys)
            {
                cboListOfDSN.Items.Add(item);
            }
            //end of adding DSN to list

            if (cboListOfDSN.Items.Count > 0)
            {
                foreach (string item in cboListOfDSN.Items)
                {
                    if (item == "QuickBooks Data")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                    if (item == "QuickBooks Data POS")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                    if (item == "QuickBooks Data Online")
                    {
                        cboListOfDSN.Text = item;
                        return;
                    }
                }
                cboListOfDSN.SelectedIndex = 0;
            }



        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            try
            {

                Application.DoEvents();
            if (btnConnect.Text == "Disconnect")
            {
                if (_cn != null)
                {
                    lblConnectionStatus.Text = "Disconnecting....";
                    _cn.Close();
                    _cn.Dispose();
                    _cn = null;
                    btnConnect.Text = "Connect";
                    lblConnectionStatus.Text = "Not Connected";
                    lblConnectionStatus.ForeColor = Color.DarkRed;
                }
            }
            else
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    lblConnectionStatus.Text = "Connecting....";
                    _cn = new OdbcConnection(string.Format("DSN={0}", cboListOfDSN.Text));
                    _cn.ConnectionTimeout = 60;
                    _cn.Open();
                    btnConnect.Text = "Disconnect";
                    lblConnectionStatus.Text = "Connected";
                    lblConnectionStatus.ForeColor = Color.Green;
                }

            }
            Application.DoEvents();
            }
            catch (Exception ex)
            {
                Application.DoEvents();
                btnConnect.Text = "Errorred";
                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }

        }

    

        private void DisplaySalesReceiptInGrid(string customerListID)
        {
            string query = string.Format("SELECT CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId,  SalesReceiptItemQty as ItemQuantity, SalesReceiptItemPrice As ItemPrice, TenderCash01TenderAmount As TotalTenderAmount FROM SalesReceiptItem where TxnID='{0}'", customerListID);
            ProcessQuery(query);
        }

     


        private void InsertSalesReceiptLineItem(string customerName, string comments, string cashierName, string salesReceiptType, string itemName, int quanity, int rate, int tenderAmount)
        {            
            string query =
                    string.Format("Insert Into SalesReceiptItem (CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId,  SalesReceiptItemQty, SalesReceiptItemPrice, TenderCash01TenderAmount) Values ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7})",customerName,comments,cashierName,salesReceiptType,itemName,quanity,rate,tenderAmount);
                //query = string.Format("Insert into invoiceline(txnid,InvoiceLineItemRefFullName, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineDesc) values('{0}','{1}',{2},{3},'{4}') ", txnID, itemFullName, quanity, rate, description);
                //MessageBox.Show(query);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    //MessageBox.Show("" + QBEmployeecmd.Connection.State.ToString());
                    QBEmployeecmd.CommandType = CommandType.Text;
                    //MessageBox.Show("" + QBEmployeecmd.CommandType.ToString());
                    QBEmployeecmd.ExecuteNonQuery();
                    //MessageBox.Show("Execute Success");

                }
        }

        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);            
            DataSet dataSet = new DataSet();            
            OdbcDataReader reader = cmd.ExecuteReader();            
            DataTable myTable = new DataTable();
            myTable.Load(reader);
            grvData.AutoGenerateColumns = true;
            grvData.DataSource = myTable;
        }

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            if (_cn != null && _cn.State != ConnectionState.Closed)
            {
                _cn.Close();
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void button2_Click(object sender, EventArgs e)
        {
           
           
        }

        private void btnSalesReceiptItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    btnConnect_Click(null, null);
                }
                if (string.IsNullOrEmpty(txtcustomerName.Text))
                {
                    MessageBox.Show("Customer Name is Required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtComments.Text))
                {
                    MessageBox.Show("Comments is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtCashierName.Text))
                {
                    MessageBox.Show("Cashier Name is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtSalesReceiptType.Text))
                {
                    MessageBox.Show("Sales Receipt Type is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtItemName.Text))
                {
                    MessageBox.Show("Item Name is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtQuantity.Text))
                {
                    MessageBox.Show("Quantity is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtItemPrice.Text))
                {
                    MessageBox.Show("Item Price is required.");
                    return;
                }
                if (string.IsNullOrEmpty(txtTenderAmount.Text))
                {
                    MessageBox.Show("Tender Amount is required.");
                    return;
                }
                InsertSalesReceiptLineItem(txtcustomerName.Text, txtComments.Text, txtCashierName.Text, txtSalesReceiptType.Text, txtItemName.Text, int.Parse(txtQuantity.Text), int.Parse(txtItemPrice.Text), int.Parse(txtTenderAmount.Text));
                string lastInsertedId = GetLastInsertedId("sp_lastInsertID SalesReceiptItem");
                DisplaySalesReceiptInGrid(lastInsertedId);
            }
            catch (Exception ex)
            {

                MessageBox.Show(string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace));
            }
        }
        private string GetLastInsertedId(string query)
        {
            string lastInsertedId = "";
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;
                var executedResult = QBEmployeecmd.ExecuteScalar();
                lastInsertedId = executedResult.ToString();               
            }
            return lastInsertedId;
        }

    }
}
]]> <![CDATA[[QODBC-POS] Sample C#.NET Web Application for Creating SalesReceipt]]> Sample C#.NET Web Application for Creating Sales Receipt

Sample Application:

Please click here to download the Sample Code.

Please refer to the steps below for using an application for creating a SalesReceipt using C#.NET.

Run the application & click on the "Connect" button.

The application is connected with QuickBooks POS.

You need to insert CustomerListId, Comments, Cashier, SalesReceiptType, Item ListID, Quantity, Rate & Amount, which you want to enter, and click on the "Insert New SalesReceiptItem" button.

New SalesReceipt created.

Result in QuickBooks POS.

Note: Modify/Append existing SalesReceipt feature is not supported by QuickBooks SDK, so it is unavailable through QODBC POS.

Application Source Code:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Drawing;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

public partial class _Default : Page
{
    private static OdbcConnection _cn;
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {           
            lblErrorMessage.Visible = false;
        }
        catch (Exception)
        {

            throw;
        }
        finally
        {

        }
    }
    protected void btnConnet_Click(object sender, EventArgs e)
    {
        try
        {
            if (btnConnect.Text == "Disconnect")
            {
                if (_cn != null)
                {
                    lblConnectionStatus.Text = "Disconnecting....";
                    _cn.Close();
                    _cn.Dispose();
                    _cn = null;
                    btnConnect.Text = "Connect";
                    lblConnectionStatus.Text = "Not Connected";
                    lblConnectionStatus.ForeColor = Color.DarkRed;
                }
            }
            else
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    lblConnectionStatus.Text = "Connecting....";
                    _cn = new OdbcConnection(string.Format("DSN={0}", cboListOfDSN.Text));
                    _cn.ConnectionTimeout = 60;
                    _cn.Open();
                    btnConnect.Text = "Disconnect";
                    lblConnectionStatus.Text = "Connected";
                    lblConnectionStatus.ForeColor = Color.Green;
                }

            }

        }
        catch (Exception ex)
        {
            btnConnect.Text = "Errorred";
            lblConnectionStatus.Text = "Errorred";
            lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
            lblErrorMessage.Visible = true;            
        }
    }  
    private void InsertSalesReceiptLineItem(string customerName, string comments, string cashierName, string salesReceiptType, string itemName, int quanity, int rate, int tenderAmount)
        {
            string lastInsrtedId = "";
            string query = string.Format("Insert Into SalesReceiptItem (CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId,  SalesReceiptItemQty, SalesReceiptItemPrice, TenderCash01TenderAmount) Values ('{0}','{1}','{2}','{3}','{4}',{5},{6},{7})", customerName, comments, cashierName, salesReceiptType, itemName, quanity, rate, tenderAmount);
                using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
                {
                    QBEmployeecmd.CommandType = CommandType.Text;
                    QBEmployeecmd.ExecuteNonQuery();                    
                    lblErrorMessage.Text = "SalesReceiptItem Insert Successfully";
                    lblErrorMessage.Visible = true;
                }                
        }


        private void ProcessQuery(string query)
        {
            var cmd = new OdbcCommand(query, _cn);            
            DataSet dataSet = new DataSet();            
            OdbcDataReader reader = cmd.ExecuteReader();
            DataTable myTable = new DataTable();
            myTable.Load(reader);
            grvData.AutoGenerateColumns = true;
            grvData.DataSource = myTable;
            grvData.BorderStyle = BorderStyle.Double;
            grvData.DataBind();
        }

        private void DisplaySalesReceiptInGrid(string customerListID)
        {
            string query = string.Format("SELECT CustomerListId, Comments, Cashier, SalesReceiptType, SalesReceiptItemListId, SalesReceiptItemDesc1,  SalesReceiptItemQty As ItemQuantity , SalesReceiptItemPrice As ItemPrice, TenderCash01TenderAmount as TenderTotalAmount FROM SalesReceiptItem where TxnID='{0}'", customerListID);
            ProcessQuery(query);
        }
        protected void btnSalesReceiptItem_Click(object sender, EventArgs e)
        {
            try
            {
                if (_cn == null || _cn.State == ConnectionState.Closed)
                {
                    btnConnet_Click(null, null);
                    btnConnect.Text = "Connect";
                    lblConnectionStatus.Text = "NotConnected";
                    lblConnectionStatus.ForeColor = Color.DarkRed;
                }
                if (string.IsNullOrEmpty(txtcustomerName.Text))
                {
                    lblErrorMessage.Text = "Customer Name is Required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtComments.Text))
                {
                    lblErrorMessage.Text = "Comments is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtCashierName.Text))
                {
                    lblErrorMessage.Text = "Cashier Name is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtSalesReceiptType.Text))
                {
                    lblErrorMessage.Text = "Sales Receipt Type is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtItemName.Text))
                {
                    lblErrorMessage.Text = "Item Name is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtQuantity.Text))
                {
                    lblErrorMessage.Text = "Quantity is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtItemPrice.Text))
                {
                    lblErrorMessage.Text = "Item Price is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                if (string.IsNullOrEmpty(txtTenderAmount.Text))
                {
                    lblErrorMessage.Text = "Tender Amount is required.";
                    lblErrorMessage.Visible = true;
                    return;
                }
                InsertSalesReceiptLineItem(txtcustomerName.Text, txtComments.Text, txtCashierName.Text, txtSalesReceiptType.Text, txtItemName.Text, int.Parse(txtQuantity.Text), int.Parse(txtItemPrice.Text), int.Parse(txtTenderAmount.Text));
                string lastInsertedId = GetLastInsertedId("sp_lastInsertID SalesReceiptItem");
                DisplaySalesReceiptInGrid(lastInsertedId);
            }
            catch (Exception ex)
            {
                lblErrorMessage.Text = string.Format("Error - {0}, Stack Trace {1}", ex.Message, ex.StackTrace);
                lblErrorMessage.Visible = true;
            }
        }

        private string GetLastInsertedId(string query)
        {
            string lastInsertedId = "";
            using (OdbcCommand QBEmployeecmd = new OdbcCommand(query, _cn))
            {
                QBEmployeecmd.CommandType = CommandType.Text;
                var executedResult = QBEmployeecmd.ExecuteScalar();
                lastInsertedId = executedResult.ToString();
                lblErrorMessage.Text = "SalesReceiptItem Insert Successfully";
                lblErrorMessage.Visible = true;
            }
            return lastInsertedId;
        }
}
]]> <![CDATA[[QODBC-POS] How to Install QODBC POS and Connect to QuickBooks POS on Windows 10]]> How to Install QODBC POS and Connect to QuickBooks POS on Windows 10

Install QODBC POS

Please download the QODBC POS installer by clicking here

Right-click on the Installer and select "Run As Administrator."

 

Please click "Yes" in the UAC window to install QODBC POS.

 

Follow the Installation steps from the Installer by clicking the "Next" button till it finishes.

 

Installation completed.

 

Connect to QuickBooks POS via QODBC POS

First, please open QuickBooks POS & load your company file:

 

 

 

Now, please open QODBC POS Test Tool from Start Menu>> QODBC Driver for use with QuickBooks POS>> QODBC POS Test Tool :

 

Select DSN "QuickBooks POS Data" and click on "Connect."

 

 

QuickBooks POS will ask you to accept the Application certificate. Click the "Yes Always" button to allow QODBC POS Test Tool to access your QuickBooks POS company file.

 

QODBC POS Test Tool is connected to the QuickBooks POS company file.

Click on the "Run" button to obtain the customer data, or remove the default SQL Statement and enter your SQL statement.

 

Here is the customer data.

 

If you cannot connect, please change QuickBooks POS to run in multi-user mode. Click the "Switch Company File to Multi-User Mode" option in the File Menu & try to connect using the QODBC POS Test Tool.  

 

 

In QuickBooks POS references, you can see that QODBC POS has been included in the list of allowed applications:

You can open the QuickBooks POS company preference from:
File >> Preferences >> Company

 

QODBC POS Test Tool is shown in the list of allowed applications:

 

In the same way, you should be able to use QODBC POS with your application(s).

Also, Refer:
QODBC POS could not access to QuickBooks POS Company File

]]> <![CDATA[[QODBC-POS] Using QuickBooks POS Data with Excel 2016 / 365 / 2013]]> How to Use QODBC POS with MS Excel 2016 / 365 / 2013

Set up QODBC to use with MS Excel

Note: QODBC allows you to quickly merge live QuickBooks data into the cells of your Microsoft Excel worksheets for quick calculations and graphs. The data will retain a live link to QuickBooks, and one click will update the worksheet to the most current data in the linked QuickBooks table.

Before using the MS Excel application, please ensure you have installed the MS Office Component named "Microsoft Query." About MS Query, please refer to Microsoft Office with MS Query and QODBC for more.

Please start Microsoft Excel 2016/2013 from Windows Start->All Programs->Microsoft Office 2016 or Microsoft Office 2013:

Note: In recent versions of Microsoft Excel (including Excel 365), the Microsoft Query (Legacy) feature is hidden by default from the Get Data tab.
Please refer to Troubleshooting - How to enable Microsoft Excel 365 - Legacy Microsoft Query.

Office 2016:

Office 2013:

 

Office 2016:

Office 2013:

Create a blank workbook or can open an existing one. In this example, we are creating a new workbook:

An Empty sheet will open, and you shall be on the "Home" tab:

Please select the "Data" tab and click "From Other Sources-> From Microsoft Query."

It will take some time to get the DSN list:

 

And then, in the "Choose Data Source" Window, please select a DSN to continue. (Here, I choose the default data source with a sample company file open. And check to use the Query Wizard to create/edit queries.
For 64-bit MS Office, changing QRemote DSN to "QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC POS.

The first time you use MS Excel to access QuickBooks, you will be asked to grant access permission for QODBC:

Select the "Yes, always" option to finish authorizing.

Import QuickBooks data into MS Excel via MS Query

After you grant permission for QODBC, you will be guided by the Query Wizard. In the Choose Columns window, please select a table and the columns you want to input. Multiple selections are available. And once a table is set, it is the default that all its columns are set. You can add or remove columns from the correct list "Columns in your query."

You can set a filter for your query in this window. If you don't want to set any query, please click the "Next" button:

This window allows you to sort your data. If you don't want to change the order, please click the "Next" button:

After the setup is finished, MS Excel will ask you to choose an edit method for the records:

Return Data to Microsoft Excel:

This method will directly export the data to Excel, where you can view and edit these records, but the data is static. The modification will not be uploaded to QuickBooks.

You can view the detailed connection properties by clicking the "connection" button in the Data menu:

In the Workbook Connection Window, click the "Properties" button to view detailed information:

Now you can view the connection string and its SQL statement in the definition window:

View Data or Edit Query in MS Query :

This method will lead you to the MS Query window, and you can view and edit data via SQL Statement there, and all modifications will be uploaded to QuickBooks.

Click the "SQL" button to view the SQL Statement of this query. The SQL window will show the SQL Statement, and the user can also modify the statement to extract other records:

 

 

The desired record set contains only the records with a Name having a value equaling "Residential." Select the field header name and click on it, followed by selecting Criteria -> Add Criteria. In the popped-out Add Criteria window, specify the criteria Field as "Name," Operator as "equals," then click the Value button and from the list of values, select "Residential" -> OK -> Add.

 

 

 

 

The following screen reflects the applied criteria to the query and the resulting QuickBooks recordset:

 

When you finish the query, choose to save it so that the next time you can directly use it. Leaving MS Query, MS Excel will ask you to import the query result into MS Excel:

 

 

 


     Clicking Data -> Refresh Data or clicking the External Data toolbar will update the data in the worksheet from the latest information in the linked QuickBooks tables, as shown above. Changes to the QuickBooks files will not affect this worksheet until you refresh the data with the Refresh option.

Keywords: create odbc connection, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

]]> <![CDATA[[QODBC-POS] Using QuickBooks POS Data with Access 2016 / 365 / 2013]]> How to Use QODBC POS with MS Access 2016 / 365 / 2013

Creating a Table in Microsoft Access:

Note: Microsoft Access has many uses and will allow you to use your QuickBooks data files similarly to Access databases.

Open MS Access 2016/2013. You can find it via Windows Start->All Programs->Microsoft Office 2016/2013:

Office 2016:

Office 2013:

A default database filename, Database1, appears in the box. You can edit this to a more relevant name, such as QuickBooks Link. accdb. Save the file as type "Microsoft Office Access Databases."

Then, click "Create" to create a blank database:

 

After  the new database is created, navigate to "External Data":

Build a connection via External Data->More->ODBC Database, or directly click the ODBC database button on the External Data menu:

Under the Get External Data window, you will get two options:

 

Link to the data source by creating a linked table: this option will create a table just like the first option, but will keep its connection to QuickBooks, which means all your operations to this table will be uploaded to QuickBooks, and the data in the table is also in full sync with QuickBooks.

This article will show you how to build a linked table in MS Access.

Build a Linked Table in MS Access

After you select the access method and click OK, you will get a Select Data Source window:

Select the QuickBooks-related DSN you set up with the QODBC driver. This can be one of our pre-installed DSN names or one that you have created. (Here, I choose the default DSN as an example.)
For 64-bit MS Office, changing QRemote DSN to "QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC POS.

The first time you use MS Access to access QuickBooks, it will ask you to grant permission for this application:

Select the "Yes, Always" option, and then click "Done" in the next window:

Once the connection is built, Access will return a list of available tables in the Select Tables window. Please select the tables you want to extract data from. Multiple Selection is available.

If the Import/Linking works without any problems, you will see linked tables being created in the table list of MS Access:

If you want to view QuickBooks records in this linked table, double-click the linked server, and you will see the records:

The linked table is always in sync with QuickBooks Data, so you cannot add or delete any columns in this table. But you can directly modify the data in the linked table, and QODBC will automatically update the modification to QuickBooks.

Note: When you run across an application that is not fully ODBC compatible, they often support getting data from Access tables. To make this application work with QuickBooks tables, you can run the Microsoft Access setup, as shown above. Then have this application use the MS Access ODBC driver to talk to the Access database you created, which was linked to QuickBooks tables. This middleware approach allows us to support nearly any application, even if not fully ODBC compliant.

 

Suppose you want to import a table into MS Access. In that case, you have to select Import the source data into a new table in the current database option instead of linking to the data source by creating a linked table & repeat the above steps: this option will directly extract tables into Access Database, you can view and edit these tables, but all modification will not be uploaded unless you build another query to do so.

 

Keywords: read/write with MS Access, Office 2013, Office 365, Excel 365, Access 365, Office 2016, Access 2016, Excel 2016

]]> <![CDATA[[QODBC-POS] How to Configure QODBC POS to Work With 64-bit Applications]]> How to Configure QODBC POS to Work With 64-bit Applications

Instructions

64 and 32-bit words are kept separate in the Microsoft Windows Operating System. QuickBooks POS is a 32-bit application, so a 32-bit ODBC driver is required to communicate with it.

Since a 64-bit application cannot talk to a 32-bit driver, we have to do a bit of magic to get the worlds to connect. We call it QRemote. QRemote works as a bridge between 64-bit Applications and the 32-bit QODBC Driver by communicating data calls between the two worlds using TCP/IP protocol. QRemote allows 64-bit Web Servers and 64-bit applications to talk to QuickBooks POS live for the first time via the QODBC Driver.

QRemote has two components: QRemote Client Driver for QODBC and QRemote Server for QODBC.

QRemote Client Driver: Allows service-based applications and 64-bita pplications to connect to the QRemote Server and send the request to the QRemote Server for processing. 

QRemote Server: Processes the requested instruction from the QRemote Client to QuickBooks POS using the QODBC DSN Driver.

Setting Up a New QRemote Connection.

Start QRemote Server for QODBC POS from the Start menu.

 

Double-click the QRemote Server icon from the system tray.

 

QRemote Server Main screen shows the IP address and port, allowing the QRemote Client to connect.

 

Note the IP address and Port number listed on the QRemote Server Event Logs window.

 

Create a new DSN entry.

Before creating A DSN entry, verify you have made a DSN entry for QODBC, which can connect to QuickBooks. 

Create a new DSN entry/or configure the existing one

Navigate to Start Menu, QODBC Drivers for Use with QuickBooks POS, and click

Configure QODBC POS Data Source 64-bit 

 

 

"QuickBooks POS Data 64-bit" is the default DSN created when installing QODBC.

Navigate to System DSN. Click on the Add Button

 

From the driver list, select QRemote for QuickBooks POS and click Finish.

 

o Under the QRemote for QuickBooks POS Client DSN configuration screen, please fill in the following information

o Data Source Name: Name of your DSN entry

o Server: IP address displayed on QRemote Server

o Port: Port displayed on QRemote Server

o Encryption Key: ( Optional, required only if you want to encrypt the data transfer, also needs to be configured at QRemoteServer)

Remote DSN: select the QODBC POS DSN from the list. ( Make sure QRemote Server is running)

 

 

In this example, we will be using the existing (Default) DSN: QuickBooks POS Data 64-bit.  

Verify that the QRemote Client connects to the server and that the following logs are logged in the event window.

 

Connecting with 64-bit Applications

To allow 64-bit Application(s) to talk to QuickBooks POS via QODBC POS, start the QRemote server and configure the QRemote Client DSN entry.

Connect using the standard DSN connection string. e.g., DSN=QuickBooks POS Data 64-bit

 

 

]]> <![CDATA[[QODBC-POS] Using QuickBooks POS data remotely via QODBC POS]]> QRemote Tutorial: Using QuickBooks POS data remotely via QODBC POS

QODBC POS can talk to a copy of QuickBooks POS running anywhere in the world over the Internet or a local area network. This feature is built into the licensed version and requires no additional charge.

This feature can transfer data in an unencrypted fashion (plain text), or you can set up your encryption key to encrypt the data, which is preferable for transfer over the open Internet.

To get this feature to work, install QODBC POS on the machine that runs QuickBooks POS (the SERVER) and the device you want to use the data from (the remote CLIENT). It does not matter if the QuickBooks POS data file is located on the SERVER machine or another location, but the best speed is attained if it is on the SERVER machine. QuickBooks POS on the SERVER Machine should be able to open the QuickBooks POS company file. Once this is done, a few simple settings in the driver setup screen are required to use this data remotely.

Before testing remote capabilities, be sure you have tested basic QODBC POS functionality to ensure that QODBC POS can connect to QuickBooks POS and obtain data with this article.

The tool we use to make this remote connection is called QRemote. QRemote works as a bridge between remote applications and the QODBC POS Driver by communicating data calls between the two worlds using TCP/IP (Internet) protocol. 

QRemote has two components, "QRemote Client Driver for QODBC POS" and "QRemote Server for QODBC POS."

QRemote Client Driver (CLIENT): Allows applications to connect to QRemote Server and sends the Request to QRemote Server for Processing.

QRemote Server (SERVER): Processes requested instructions from QRemote Client to QuickBooks POS using QODBC Driver, talking to QuickBooks POS application SDK. 

Configuring the Server Machine: 

Start "QRemote Server for QODBC" from the Windows start menu.

 

Double-click the QRemote Server icon "Q" from the system tray.

 

QRemote Server Main screen shows the IP address and port setup for the QRemote CLIENT to connect.

 

Note the IP address and port number listed on the QRemote server.

 

Configuring CLIENT Machine: 

Select "Configure QODBC POS Data Source" from the Windows Start Menu. (If your application is 64-bit, select "Configure QODBC POS Data Source 64-bit."

 

Switch to "System DSN," Select the DSN "QuickBooks Data QRemote" Or "QRemote Data 32-bit". (For 64-bit DSN, "QuickBooks Data 64-bit QRemote" or "QRemote Data 64-bit")

 

 

Click Configure. This will bring up the QRemote Client DSN Configuration. Fill in the following information.

  • Data Source Name: Name of your DSN entry - Do Not Change

  • Server: Change the IP Address to the IP address displayed on QRemote Server at the SERVER Machine, e.g., 192.168.1.19

  • Port: Port displayed on QRemote Server at the SERVER Machine

  • Encryption Key: Keep default key "QODBC#QRemote$1"(In case you want to use encrypted data transfer between QRemote client and QRemoteServer, you can configure your encryption key on QRemoteServer and use the same on QRemote Client DSN)  

  • Remote DSN: select the QODBC DSN from the list. ( Make sure the QRemote Server on the SERVER Machine is running. This is the QODBC DSN on the SERVER Machine

 

 

"QuickBooks POS Data QRemote." (For 64-bit DSN, "QuickBooks POS Data 64-bit")

 

Verify the QRemote Client connects to the server, and the following logs are logged in the event window of the QRemote Server at the SERVER Machine:

 

Connection to Remote QuickBooks POS/QODBC POS via Applications (32-bit)

You may use any 32-bit application, like MS Access/Excel, to connect to the Remote QODBC. In this example, we are using the QODBC POS Test Tool.

Step 1 - Start QuickBooks POS to register QODBC POS

Please keep QuickBooks POS running on the SERVER Machine, logged in as "Admin."

QuickBooks POS is an accounting program that must produce trusted numbers that accountants can rely on. For this reason, they have created an interface to the QuickBooks application that follows all of the business rules. This is what our product uses to access QuickBooks POS data.

To get access to that data, your application (using QODBC/QRemote) must register with QuickBooks so that security access can be granted. To do this, you first need to run QuickBooks POS on your computer, logged in as an Administrator, and open your company file.

 

Step 2 - Running QODBC POS Test Tool

Leaving your QuickBooks company file open, click on "Start" and "Programs," locate the "QODBC Driver for use with QuickBooks POS" program group, and click on "QODBC POS Test Tool" to launch.

 

Select "QuickBooks POS Data QRemote" from the DSN list or the DSN you had configured that points to the SERVER QRemote Server's IP Address.

Click on "Connect"

 

"QuickBooks POS Data QRemote." (For 64-bit DSN, "QuickBooks POS Data 64-bit")

 

Step 3 - QuickBooks POS Integrated Applications

 

At the SERVER Machine, QuickBooks will request the Accepting Certificate for Data Access.

When the driver starts, QuickBooks POS will ask permission for the driver to access your QuickBooks POS data. Answer "Yes, Always" to this, and the QODBC driver is added to the Integrated Application list.

This will allow applications to integrate with QuickBooks data when QuickBooks POS runs on the desktop. There may be instances in which you want an integrated application designed to share data with QuickBooks POS to access QuickBooks POS files automatically without user intervention. This might permit, for example, a particular application to run during the night when there is no other activity on the company file. Also, QuickBooks POS need not be running on the desktop for ODBC-compliant applications to access the data files while set in this mode.

 

This will allow applications to integrate with QuickBooks POS data when QuickBooks POS is running on the desktop.

Step 4 - Testing Your Connection

CLIENT Machine:

After the successful connection, you may notice that the "Connect" button is altered to "Disconnect."

 

Now that you have successfully established a connection to your QuickBooks data, it's time to type in your desired SQL query; for example, you can use the default "SELECT * FROM CUSTOMER" or order in another table like stock by entering "SELECT * FROM ITEMINVENTORY."

Click the "Run" button, and the requested query results will appear! In case you're wondering, the * in the select statement means all columns in that Table.

Execute the "Sp_Tables" command to see a list of all the available QuickBooks POS tables.

 

Here, you can see we have extracted the customer records. You can connect to your application using the same DSN name and export the records.

 

To disconnect, you can either select "connections" / "remove the connection" or Click the "disconnect" button. If you get an error, please search our FAQ Knowledgebase or post a support ticket to resolve the issue. Click Here. If not, congratulations - you have created your first report from QuickBooks POS data and are ready to talk about your company data.

 

]]> <![CDATA[[QODBC-POS] QODBC POS Setup Options]]> QODBC POS Setup Options

QODBC POS General Setup

     The first step in configuring the driver is to click on the QODBC POS Setup Screen, and the following general setup screen will become available.

 

The company setting allows QODBC POS to enter the company file selection window.
Please leave the "Current Company Setting" option blank so that QODBC POS can directly access the company file thinning in QuickBooks POS.

Build Connection String: Click the "..." button at QODBC POS Setup Screen->General Window to enter the company file selection window:

 


In the company file selection window, QODBC POS will give you a list of all available company files (may take a few seconds) :

 

Select the company files you want to access, and then click ok. Then you will see the connection string has been built in the "Current Company Setting" option. And then, please click the "Test Connection" Button.

 

Data Source Name: This is a display name for your selected DSN.

Test Connection to QuickBooks will determine if you have a proper installation and settings with QuickBooks and a company file open.

 

QODBC POS Messages Setup

     Messages allow you to review message logs from QODBC POS regarding errors and issues communicating with QuickBooks POS, clear these messages, and review messages produced by the QuickBooks POS qbXML SDK interface.

 

Detail Tracing (Slow) creates a trace log of what QODBC POS is doing in the background with the application and is used for debugging purposes. Ensure the log is turned off when not needed, as these can use up large amounts of disk space.

Display Optimizer Status Panel will display the progress of optimization on the data. This is especially helpful on large data files being reloaded in their entirety.

When selected, the Display Driver Status Panel shows exactly what QODBC POS is doing when accessing the QuickBooks data and provides a good assessment of speed using optimized and non-optimized data.

Verbose Optimizer Status Panel causes the Optimizer panel to come up whenever any optimizer happens. The normal checkbox only pops up the optimizer when doing a full update or reloading all.

 

QODBC POS Optimizer Setup

      Recommended settings for using QODBC POS are shown here; read below for a summary explanation of all available options.

 

The optimizer brings some data to a local cache to increase query retrieval performance. Check to activate the optimizer.

Optimizer Database Folder specifies where the local data store will be saved on disk. The default location %AppData%\QODBC Driver for QuickBooks POS\Optimizer is under your Windows login account name under your Documents and Settings folder. The data will be stored in a file in this folder with a name similar to the company file name with a .opt extension. If you store that data somewhere other than the default, click the browse button and select the destination folder. With very large company files, we suggest running synchronization after hours.

Keep my optimized data up to date as of when you choose the optimizer to synchronize itself with your Company data. Depending on how static your data needs are and the size of the file, you can opt for various time frames of synchronization. The farther down the list you go, the less synchronization will occur, and your queries will run much faster but will be using less current data than the company files themselves.

The start of every query is the default setting using the most current data. Use this default setting if your question requires the latest up-to-the-minute data and speed is not the primary focus. Please remember that the QODBC Optimizer is local to your computer only, so when other users change things in QuickBooks, how does QODBC know? By default, the optimizer will update new and changed entries in a table from QuickBooks first and then execute the query against the locally optimized table. This is faster than reading everything out of QuickBooks every time, especially the more data you have.

The start of every new connection (with "Load Updated Data" first) is used if you don't need to update the optimized data while you are, for example, running queries, importing, or exporting data. The optimized information is updated upon a new connection and is left updated until the next new connection occurs.

The start of every new connection (with "Reload All Data" first) is used if you need to rebuild optimized data from scratch before running important queries (like Sales Commission) or importing or exporting data. The optimized data is reconstructed upon a new connection and updated until the next new connection occurs.

The end of yesterday was used to balance speed and data updating better, and would be the best option for most users. It only has to synchronize each table once per day, and the data will be current at the end of the previous day.

The end-of-last-month option is a great speed boost for running last month's or last year's queries. It does not need to synchronize again to get the data.

The last time I pressed one of the load data buttons, options allowed me to control the synchronized timing. Synchronizing only occurs when you manually run it by pressing the Load Updated Data in the QODBC setup screen.

For balances and stock quantities: the option Nulls - will return NULLs for all fields that cannot be kept in sync, Dirty Reads - means return the most current value that was loaded into the optimizer cache, and Real-Time uses the slower method to get the data directly out of QuickBooks, making sure you get the most current value for the requested fields. (Real Time is the previous method used).

Optimizing data after an Insert or Update causes QODBC to do Load Updated Data after an insert or update is executed on a table. Useful for programmers who want to test that their data has successfully been written to QuickBooks.

The multi-Table Sync option is unchecked by default for all non-USA enterprise users. If checked, the QODBC POS optimizer will use other tables to resync tables. However, this will slow down performance. When off, all Multi-Table Sync fields become Real-Time fields. Note: This feature is ignored and set to unchecked if the version is QBOE or the SDK is less than v3.

Load Updated Data: This button is used to synchronize your optimized data with your company file. This will only load the data that has changed since the last automatic or manual synchronization.

Reload All Data: This button is used to rebuild the optimized data from scratch. It is useful when starting a new company if you do not want to synchronize the tables as you first encounter them and ensure that your data is 100% current.

QODBC POS Advanced Setup

     The selected defaults in the Advanced settings should apply to most users and normally don't need to be changed.

 

Maximum Field Name Length: is set to the QuickBooks POS limitation, but specific applications like Delphi cannot handle this limit and must be set accordingly.

Use Case Sensitive RefNumber Searches, when checked, means a RefNumber of 'ABC' and 'ABC' will be different, but means QuickBooks can use an index internally for a search instead of a complete table scan. (Note: this feature applies to versions of QuickBooks 2005 and higher)

Include Raw Files, which are checked, it will display both filtered and raw data formats. QODBC formats data into SQL field types more easily used in programming languages for math and comparison operations. The Raw tables return the data as it is returned to QODBC without modification.

Run 2.1 ODBC Compatibility is checked for applications that require ODBC 2.1 Compatibility to use QODBC. Anything that uses the Java ODBC library, like ColdFusion, requires this option turned on, or recordsets will be returned with no row sets or only one row.

Simulate Transaction Support for SQL Server will turn transaction support on so that SQL Server will allow updates when using QODBC as a linked server. QODBC still does not support transactions, but simulates the turning on and off of transactions.

The Advanced tab in QODBC Setup v6 Build 186 has new defaults for Use Iterators and Iterator or Chunk Size fields. This allows you to turn off iterators. This may be needed on systems like MS-SQL Server and Web Servers that use multiple threads. Using Iterators is faster, but is only available to USA QuickBooks 2006 and USA QuickBooks 2007 users. The iterator or Chunk Size determines the size of the row sets that are retrieved from QuickBooks. Set this value high if you are exporting records from QuickBooks. The maximum Iterator or Chunk size that can be set is 100000 (more elevated than that, and it resets itself back to the default of 500).

Iterators and the Iterator or Chunk Size fields allow you to turn off iterators. This is needed on MS SQL and Web Servers that use multiple threads.

Text Field Handling has been added to QODBC v7.00.00.204 and later. Convert Fields > 255 to LONG VARCHAR and Limit length to 4096 characters (default): These fields help some development environments get data from long areas that would not be able to otherwise. Connection string options include ConvertToLongVARCHAR=Yes|No, and MaxLONGVARCHAR=4096.

Convert All Numerics to VARCHARS helps load tables in Access when Access chooses the wrong size numeric field sizes. Access will give an error "data too large for field DECIMAL." When you get this error, switching this option on will allow all the data to be loaded into Access as strings. The data can be converted as needed by your programs.

Adding XML to the trace log is an advanced option to put more information in the trace log to help.

The support team diagnoses a problem. It should not be used unless directed by the support staff, as this will generate large amounts of data in the trace log.

Adding Windows User Name to the Authentication Name will add Windows User Name to the Integrated Application Authentication Name.

Rewise XML Response

Converting special characters(control characters only) to white space will convert special characters to white space. 

 

Activate/Deactivate Tab

 



This tab allows you to activate a license with the CDKey or deactivate a currently active license and move that license to a new machine.

 




Activate is the online option for entering the CDKey that was issued to you upon purchasing the product to fully start QODBC POS from the evaluation version to either the full Read Only, Read/Write, or Web Edition that has been purchased. Suppose you do not have internet access to activate the product. In that case, you can use the phone option to supply your reseller or distributor with the information to activate the product.

 

 



Deactivate is the online method of deactivating a currently registered version of QODBC POS to transfer the product to another machine. Suppose internet access is unavailable from the registered device. In that case, you can use the phone option to supply your reseller or distributor with the information to deactivate the product CDKey for you. Using the latest version of QODBC, credit for deactivation CDKEY is received instantly. In some cases, this process will take 24 to 48 hours before your CDKey account is credited with the deactivated license, and you can reactivate the right on a different machine.

About Tab

 


 
The bout screen shows the QODBC POS version currently installed, whether it is an evaluation copy or fully registered. (The serial number may be required for upgrades, rebate offers, technical installation support, or the Help Desk system.) The first letter in the serial number denotes what type of license is installed, and the 2nd & 3rd digits of the QODBC version.



Check Website for Updates will launch your internet browser to our website and see if you have the latest version of QODBC installed on your machine. You can view the Readme file for the list of all the latest features in the current version of QODBC POS and download the latest updater should you be using an older version of the driver.

 Keywords: setup for online

 

]]> <![CDATA[[QODBC-POS] QODBC POS Quick Start Guide]]> QODBC POS Quick Start Guide


Download: QODBC POS 2019 Quick Start Guide (PDF)

QODBC POS Driver License Agreement 

Introduction 

What is ODBC? 

 

ODBC (Open Database Connectivity) is a specification designed by Microsoft to: 

1.     Allow Windows applications to access multiple data sources through a single method with no regard for various file formats;

2.     Overcome the problem of different databases having different means of providing access to the information contained within them, and

3.     Simplify access requirements so that the user no longer requires a high skill level to access databases.

 

ODBC also provides the following benefits: 

1.     SQL (Structured Query Language) is promoted as the method for accessing data through ODBC.

2.     It simplifies application development, making access to multiple databases easier.

3.     It insulates applications from changes to the underlying network and database versions. Modifications to networking services, servers, and databases will not impact current ODBC applications.

4.     The Corporation's investment in the existing database is protected;

5.     The developer's acquired database programming skills are protected;

6.     Making this easily accessible data available to some users can increase productivity and is a basis for more informed decisions. 

Microsoft, along with a few other database manufacturers, designed the ODBC driver to provide a standard, consistent, and straightforward process of connecting to a wide range of databases. 

Vendors of ODBC drivers (like this one) follow the specifications for ODBC so that any ODBC-compliant application can access the data available through the driver. 

ODBC-compliant applications include Microsoft SQL Server, Microsoft Access, Word, Excel, PowerBuilder, Borland Delphi, Crystal Pro Report Writer, Visual Basic, and hundreds of others. The placement of this driver can be seen in the illustration below.

 

 

 

What is QODBC for POS? 

QODBC for POS is an ODBC driver (DLL) for QuickBooks POS format files. This driver allows users of any ODBC-compliant front-end application to read and write data contained in QuickBooks POS  format files, similarly to any other ODBC-enabled database. QuickBooks POS, by default, stores its data in its proprietary DBMS file format. 

QODBC for POS works by accepting SQL commands through the ODBC interface, converting those calls to navigational controls to the QuickBooks POS DBMS, and returning record sets that qualify for the query results.

 

What can I do with QODBC for POS? 

QODBC for POS opens up the QuickBooks POS  files to a new world of database connectivity. In the same way, Windows has created an environment where all applications have a similar look and feel; ODBC drivers make all database formats look the same. 

·         A user of Microsoft Office can use Excel to pull company accounting or invoice information into a spreadsheet and create a chart.

·         Microsoft Word can merge a customer mail directly from QuickBooks POS files.

·         Crystal Pro Report writer can create multi-file reports from QuickBooks POS data.

·         Microsoft Access or Alpha 5 can merge QuickBooks POS data with other data sources to produce complex reports or run mass file updates based on multiple criteria. 

QuickBooks POS application developers can now offer this driver to their customers, allowing them direct access to data vital to them through custom third-party applications. Business personnel who require up-to-the-minute data to make on-the-fly business decisions will have that real-time data instantly available to virtually hundreds of desktop applications. Best of all, this driver is transparent and works precisely like ODBC drivers from other vendors.

 

Installation 

Initial Setup 

If you have downloaded this program, run the self-extracting executable in a temporary directory to extract the installation files. Don't forget to read the "Readme" file during installation for the latest information on QODBC for POS setup and other information. 

Unload the Microsoft Office toolbar from memory and any other Windows applications that can impair installation. Click on the Install program from within Windows to install the driver. Select the destination directory to install the QODBC for POS support files. Click "OK" to continue. 

 

The FLEXquarters QODBC Driver for QuickBooks POS  has been installed on your computer. You can begin to use it immediately because it will directly run as a 30-day evaluation edition, with read/write access to the QuickBooks POS tables. Note: Inserts are limited to 20 per session 

 

(On 32-bit Operating System)

 

 

(On 64-bit Operating System)

To install a complete purchase or evaluation QODBC for the POS registration code, see the Icons that have been added to your Start Menu under QODBC Driver for QuickBooks POS. 

The first step in configuring the driver is to click on the ODBC for POS Setup Screen icon, and the following screen will become available. 

QODBC for POS Setup Screen 

 

 

 

Use Practice Mode when checked will use a POS sample data file, which can be selected in the drop-down. 

Data Source Name is a display name for your QuickBooks POS data (QuickBooks POS Data, QuickBooksPRO, My QuickBooks Data, etc.) 

Company File User Name=the name of your computer goes here; Company Data=the name of your data file goes here; 

Messages Menu

 

 

Message Center allows you to review messages from QODBC for POS regarding errors and issues communicating with QuickBooks POS. Clear these messages, and review messages produced by the QuickBooks POS SDK interface. 

Detail Tracing (Slow) creates a trace log of what QODBC for POS is doing in the background, with the application using it for debugging purposes. Ensure the record is turned off when not needed, as these logs can use up large amounts of disk space. 

Display Optimizer Status Panel will display the progress of optimization on the data; this is especially helpful on large data files being reloaded in their entirety. 

Verbose Optimizer Status causes the Optimizer panel to come up whenever any optimizer happens. The standard checkbox only pops up the Optimizer when doing the full update or reload. 

Display Driver Status Panel, when selected, shows precisely what QODBC for POS is doing when accessing the QuickBooks POS data and provides a good assessment of speed using optimized and non-optimized data.  

Status Icon displays a green "Q" in your system tray when QODBC for POS is used. It allows you to interact with QODBC for POS while connected to QuickBooks POS. 

Warning Bubble When this is checked, the QODBC icon will display a warning message reminding you that QODBC for POS is still working if QuickBooks POS takes longer than 30 seconds to return information.

 

Optimizer Tab 

 

The optimizer brings some data to a local cache to increase query retrieval performance. Here you can adjust where optimizations are stored and when they are updated. Check to activate the Optimizer.

Optimizer Database Folder specifies where the local data store will be saved on disk. The default location is under the folder where you specified QODBC for installing POS. The data will be stored in this folder with a name similar to the company file. If you can keep that data somewhere other than the default, click the browse button and select the destination folder. With huge company files, we suggest running synchronization after hours.

Keep my optimized data up to date as of when you choose the Optimizer to synchronize itself with your Company data. Depending on how static your data needs are and the file size, you can opt for various time frames of synchronization. The farther down the list you go, the less synchronization will occur, and your queries will run much faster but will be using less current data than the company files themselves.

· Every query starts with the default setting using the most current data. Use this default setting if your reporting needs require the latest up-to-the-minute data and speed is not the primary focus.

·         The end of yesterday is used for a better balance between speed and data updating, and would be the best option for most users. It only has to synchronize each table once daily, and the data will be current at the end of the previous day.

·         The end-of-last-month option is a significant speed boost for running last month's or last year's reports. It does not need to synchronize again to get the data. 

·         The last time I pressed one of the load data buttons, the options allowed you to control the synchronized timing. Synchronizing only occurs when you manually run it by pressing the Load Update Data. 

·         For balances and stock quantities: the option "Nulls" - will return NULLs for all fields that cannot be kept in sync, "Dirty Reads" - means return the most current value that was loaded into the optimizer cache, and "Real-Time" uses the slower method to get the data directly out of QuickBooks making sure you get the most current value for the requested fields. (Real-Time is the previous method used). 

·         Optimize data after an Insert or Update: causes QODBC for POS to do "Load Updated Data" after an insert or update is executed on a table. Useful for programmers who want to test that their data has successfully been written to QuickBooks POS.  

·         Multi-Table Sync: This option is unchecked by default for all non-USA enterprise users. If checked, the QODBC for the POS optimizer will use other tables to re-sync tables; however, this will slow down performance when all Multi-Table Sync fields become Real-Time fields. (Similar to version 7.0.x) Note: This feature is ignored and set to unchecked if the version is QuickBooks Online (Classic) or the SDK is less than v3 (used by QuickBooks 2004 USA and all QuickBooks versions outside the USA). Before SDK v3, the other multi-sync tables did not exist, so using accurate data for those fields is safer. 

·         Load Updated Data. This button is used to synchronize your optimized data with your company file. This will only load the data that has changed since the last automatic or manual synchronization. 

·         Reload All Data. This button is used to completely rebuild the optimized data from scratch. It is useful when you start a new company if you do not want to synchronize the tables you first encounter and ensure your data is 100% current. 

 

Advanced Menu 

 The selected defaults in the Advanced settings should apply to most users and need not be changed. 

Reconnect Delay: is the time to wait after a disconnect when using the remote connector. This allows QuickBooks POS to fully unload it if it is being loaded into the background on the Host Remote Connector before you connect again. 

Maximum Field Name Length is set to the QuickBooks POS limitation, but certain specifications, like Delphi, cannot handle this limit and must be set accordingly. 

Use Case Sensitive RefNumber Searches, when checked, means a RefNumber of 'ABC' and 'Abc' are different, but means QuickBooks POS can use an index internally for a search instead of a complete table scan. 

Include Raw Files if checked; checked will display both filtered and raw data formats. QODBC formats data into SQL field types more easily used in programming languages for math and comparison operations. The Raw tables return the data as it is returned to QODBC for POS without modification. 

ODBC Compatibility "Default" is 2.1. ODBC compatibility is for certain specifications that require ODBC 2.1 Compatibility to use QODBC. Anything that uses the Java ODBC library, like ColdFusion, requires this option turned on, or recordsets will be returned with no row sets or only a 1-row set.

http://support.flexquarters.com/esupport/newimages/QODBC-Setup-Enterprise/ODBC-Compatiblity-dropdown.png

If you are using uuseosoft Excel Power Query or Microsoft Power BI, change the ODBC Compatibility to 3.8. 

SQL Server Support/Simulate Transaction Support for SQL Server will turn transaction support on so that SQL Server will allow updates when using QODBC as a linked server. QODBC for POS still does not support transactions, but simulates the turning on and off of transactions. 

Use the Iterators and Iterator or Chunk Size fields to allow you to turn off iterators. This is needed on systems and Web Servers that use multiple threads.  

Integrated Application Defaults are the defaults set in the integrated application's dialog within QuickBooks and are available in the US versions of QuickBooks. 

 

Activate/Deactivate Tab 

 This tab allows you to purchase a CDKey, activate a license with the CDKey, or deactivate a currently active license and move that license to a new machine. 

 

Activate is the online option for entering the CDKey that was issued to you upon purchasing the product to fully activate from the evaluation version to either the full Read Only, Read/Write, or Web/Server Edition that has been purchased. Suppose supposed not have internet access to activate the product. In that case, you can use the phone option to supply your reseller or distributor with the information to activate the product. 

Deactivate is the online method of deactivating a currently registered version of QODBC to transfer the product to another machine. This internet access is not available from the registered device. You can use the phone option to supply your reseller or distributor with the information to deactivate the product CDKey for you.

 

Note: Using the latest version of QODBC, credit for deactivation CDKEY is received instantly. In some cases, this process will take 24 to 48 hours before your CDKey account is credited with the deactivated license, and you can reactivate the license on a different machine.

 

About screen shows the QODBC version currently installed, whether it is an evaluation copy or a fully registered copy. The first letter in the serial number denotes what license type is installed (R for Read-Only, M for Read Write), the 2nd & 3rd digit the QODBC for POS version, and the fourth denotes what version of QuickBooks POS (US), the license is compatible with. (The serial number may be required for upgrades, rebate offers, technical installation support, or the Help Desk system.) 

Check Website for Updates will launch your internet browser to our website and check that you have the latest QODBC for POS installed on your machine. You can view the Readme file for the list of all the latest features in the current version of QODBC for POS, and download the latest version should you use the older version of the driver. 

 

The first use of QODBC for POS after setup is complete  

Testing Installation 

The first time you run this driver (for example, you can test it using the QODBC POS Test Tool included), you need my stunning QuickBooks POS Pro on the machine simultaneously logged in as Administrator. This is for security reasons. 

Click on the QODBC POS Test Tool icon to launch the application.

 

To establish a connection: select the "QuickBooks POS Data" default DSN, and click "Connect." 

 "When the driver starts, QuickBooks POS will ask permission for the driver to access your QuickBooks POS data. Answer "Yes, Always" to this, and the QODBC for POS, the river is added to the Integrated Application list. 

 

This will allow applications to integrate with QuickBooks POS data when QuickBooks POS is running on the desktop. There may be instances where you want an integrated application designed to share data with QuickBooks POS to access QuickBooks POS files automatically without user intervention. This might permit, for example, a particular application to run during the night when there is no other activity on the company file. Also, QuickBooks POS does not need to be running for ODBC-compliant applications to access the data files while set in this mode.

 

Testing Driver Capabilities 

This driver is not guaranteed to work with all possible front-end ODBC applications, and it would be impossible to test them all. If you find you have problems with this driver that do not occur in other ODBC drivers, contact support at FLEXquarters. We do want to provide maximum compatibility wherever possible. A simple test program called QODBC POS Test Tool has been included in this product to allow basic testing and isolate problems from various environments. 

QODBC POS Test Tool 

After accepting the certificate, the QODBC POS Test Tool is successfully connected to the QuickBooks POS company file.

 

Now you can run the default SQL statement or write your SQL Statement and click on the "Run" button to extract the records.

 

To disconnect, click the "disconnect" button.

 

Tutorials and Documentation 

QODBC for QuickBooks POS is very similar to our QODBC for QuickBooks editions, except that it is a true ODBC and does not need the QuickBooks POS system to access the data. Much of the documentation we have for QODBC also applies to  the QODBC for POS drivers, which can be found at www.qodbc.com 

Step-by-Step Tutorials

q  Alpha Five

q  ColdFusion/PHP

q  Crystal Reports

q  Flash Tutorials

q  Microsoft Access

q  Microsoft Excel

q  Microsoft Office

q  Microsoft Word

q  SQL Server

q  Visual Basic/ADO

To follow the tutorials using the QODBC for POS driver, simple QuickBooks POS data DSN when getting external data from ODBC sources.

Data Schema

Using the QODBC POS Test Tool included with QODBC for QuickBooks POS, you can view the schema of the individual tables using the following SQL statement: sp_columns table name. Configuring QRemote POS after QODBC is set up. 

QuickBooks POS and QODBC POS are 32-bit applications and work with 32-bit Applications. If you have 64-bit applications, they cannot communicate with 32-bit drivers like QODBC. 

To overcome this limitation, QRemote works as a bridge between 64-bit Applications and the 32-bit QODBC Driver. QRemote allows Web Servers and 64-bit applications to talk to QuickBooks via the QODBC POS Driver. 

QRemote has two components: "QRemote POS Client Driver for QODBC" and the "QRemote Server for QODBC POS." 

QRemote POS Client Driver: Allows 64-bit application (s) to connect to the QRemote server and sends the Request to the QRemote Server for processing. 

QRemote POS Server: Processes a requested instruction from the QRemote Client to QuickBooks using the QODBC DSN Driver. 

Setting Up A New QRemote Connection. 

 

Start "QRemote Server for QODBC POS" from the start menu. 

Double-click the QRemote Server icon from the system tray. 

 

 

QRemote Server Main screen shows the IP address, and the Port allows the QRemote client to connect. 

Note the IP address and Port number listed on the QRemote Server "Event Logs window. 

 

Create a new DSN entry. 

Before creating a DSN entry, verify that you have made a DSN entry for QODBC, which can connect to QuickBooks. The ODBC Manager 32-bit can be found under Start Menu à All Programs à QODBC Driver for use with QuickBooks POS à Configure QODBC POS Data Source. 

 

         To create a new QRemote DSN entry, navigate to ODBC Manager 64-bit. Located under Control Panel > Administrative Tools > Data Sources (ODBC). 

 

 

Or

 

Start Menu >> All Programs >> QODBC Driver for use with QuickBooks POS à Configure QODBC POS Data Source 64-bit. 

 

             Navigate to System DSN. Click on the "Add" button. 

 

          Select "QRemote for QuickBooks POS" from the driver list and click "Finish."  

 

Under the QRemote for QuickBooks POS – Client  DSN configuration screen, please fill in the following information:

§  Data Source Name: Name of your DSN entry

§  Server: IP address displayed on QRemote Server

§  Port: Port displayed on QRemote Server

§  Encryption Key: QRemoteServer has an Encryption feature that will send/receive data between the client & server in an encrypted format. So you can use this feature. (By default, the Encryption Key/Password is set to "QODBC#QRemote$1")

§  Remote DSN: select the QODBC DSN from the list. ( Make sure QRemote Server is running) 

 

 

 

                Verify the QRemote client connects to the server and that the following logs are logged in the event window. 

 

 

 

Connecting with 64-bit Applications 

To allow 64-bit application (s) to talk to QuickBooks via QODBC, start the QRemote server and configure the QRemote Client DSN entry. 

 

 

How to connect with 64-bit MS Access. 

-Create or Open MS Access Database 

-Navigate to the "External Data" tab and select "ODBC Database." 

 

 

 

Select the method to import the data.  

 

Select the "QuickBooks POS Data QRemote" DSN from the list under "Machine Data Source." 

(May List Either "QRemote POS Data 64-bit" or "QRemote POS Data 32-bit") 

 

You will notice the connection information getting logged under QRemoteServer. 

 

 

Select the table(s) you want to import to MS Access and click "OK." 

MS Access imports the table(s) information via QRemote. 

 

After the import process is completed, a notification window appears in MS Access. 

 

A list of imported table(s) will be shown under "All Access Objects."  

 

 

 How to connect with 64-bit MS Excel. 

Create or open an MS Excel 2010 Spreadsheet and navigate to "Data">>"Get External Data">> "From Other Sources">> Select the source as "From Microsoft Query."  

 

 

Select the QRemote Client DSN entry from the list.  

 

(May List Either "QRemote POS Data 64-bit" or "QRemote POS Data 32-bit") 

 

You will notice the connection information getting logged under QRemote Server.  

  

  

Select the table(s) you would like to import.  

 

Select the columns you want from the list -                

 

If you want to filter the data, you can opt for data filtering based on the criteria you specify.  

 

Select "Return Data to Microsoft Excel" and click "Finish." 

 

Choose the Output Location 

 

MS Excel may take a few moments to populate data into the worksheet.        

 

QODBC for POS Frequently Asked Questions 

The frequently asked questions about this product are constantly changing, so we have a web page set up with these questions. It can be found at: 

http://www.qodbc.com/support.htm

 

QODBC for POS Support 

This driver has the following ODBC support and limitations: 

·         Only ODBC, Core Level SQL, is supported.

·         Only ODBC Level 2 API calls are supported.

·         ODBC Version 3.0 compliant.

·         Column and table names are not case-sensitive. String data comparisons are case-sensitive.

·         Character values supplied for parameterized queries (SELECT * FROM EMP WHERE NAME = ?) are limited to 255 characters.

·         Transactions are not supported.

·         Qualifiers and owners are not allowed in databases, tables, etc.

·         Table creation and deletion are not supported currently. 

Windows Support 

This product supports Microsoft Windows 95, Windows 98, Windows NT, Windows ME, Windows 2000, Windows XP, Vista, and Windows 7. 

Product Support 

We offer a free, fully-functional trial copy of QODBC for POS, limited to selected elements in the QuickBooks POS system. An FAQ section and a Help Desk support system are available for registered users and those evaluating QODBC for POS. Those purchasing the product will receive Free installation support for 30 days from the date of purchase. Program anomalies will be addressed at no charge, and updates will be posted on our website. Other issues may be subject to a fee. 

The following methods can obtain support: 

Mail:                                       FLEXquarters.com LLC

                                       2620 S Maryland Pkwy  Ste 14 # 557

                                       Las Vegas, NV 89109-1692

 

Forum Online Support:       http://www.qodbc.com/QODBCAutoFAQ.htm

E-mail:                                   mailto:support@qodbc.com

Newsgroups                         http://groups.yahoo.com/group/qodbc 

The most effective of these methods is the Forum support system via our website, since you can attach an ODBC or SDK trace log to the message, and we can connect an updated product to you if we have a patch. 

Before contacting us, run an ODBC trace log of the operations you are having problems with and get the application vendor to see if they are driver-related problems. If they are, send us the file and a description of the problem. 

 

Product Updates 

Since the product is distributed in Demo form, the latest updated versions will always be available online at our web page: http://www.qodbc.com/posDownload.htm.

 

QODBC for POS Credits

 

QODBC for  POS version 18

For Microsoft Windows 95, Windows 98, Windows ME, Windows NT, Windows 2000, Windows XP, Vista, Windows 7, Windows 8, and Windows 10. 

© Copyright 1995-2017, FLEXquarters.com LLC, All Rights Reserved Worldwide 

Portions of this product © Copyright Intuit Incorporated

QODBC is a trademark of FLEXquarters.com LLC

QuickBooks Point of Sale is a registered trademark of Intuit Incorporated

Windows and Vista are registered trademarks of Microsoft Corporation

All other trademarks are the property of their respective holders 

Version 1.0 First released July 2005

Version 9.0 First released June 2010

Version 10.0 First released May 2011

Version 13.0 First released September 2012

Version 15.0 First released June 2015

Version 18.0 was first released in October 2017

 

The program was published and supported by: 

FLEXquarters.com Limited

41-43 Carnarvon Road, Suite 809

Tsim Sha Tsu in Kowloon,n Hong Kong 

USA Mailing Address:

2620 S Maryland Pkwy  Ste 14 # 557

Las Vegas, NV 89109-1692

WWW:                          http://www.qodbc.com

E-mail:                          support@qodbc.com 

 

USA Sales Office:                                   

FLEXquarters USA Sales

P.O. Box 0036

Union Lake, MI 48387-0036 

E-mail:                          mark@qodbc.com

Voice:                           248-620-2400

Licensing Support         248-620-2406 (Registered user activation/deactivation issues only)

 

Brad Waddell, Matthew Froncek, and Rajendra Dewani developed the program.

Documentation produced by Mark Kuznar, Brad Waddell, and Rajendra Dewani.

 

Tags: configurations, permissions, user rights

 

]]>