Knowledgebase: Connection
[QODBC-Desktop] How do I build a connection string with all the available QODBC parameters / Connection String with QODBC Parameters
Posted by Juliet (QODBC Support) on 03 November 2009 06:27 AM

General Connection String

Instructions

      The most challenging part of using QODBC in programming is the connection string. The simplest form includes a reference to a DSN. A system DSN called "QuickBooks Data" is automatically created when QODBC is installed. "QuickBooks Data" will be used in all examples but can be substituted for any different DSN name you make.

      Normally ADO pools connections. QODBC does not support connection pooling. It is recommended to tell ADO not to do connection pooling on any QODBC connection. This is done with OLE DB Services=-2.

Parameters available on the connection string

bullet DFQ = Path to company file or. (dot) to indicate the currently open company file.

bullet OpenMode = F (Follow Company) / M (Multi-user) / S (Single-user).

bullet DeveloperCode = This is for users of the QODBC v5 OEM version only. It is required to connect to the OEM licensing model. The Code is given to you when you purchase a QODBC v5OEM licensing pack.

bullet ColumnNameLen = This number specifies the maximum length a column name can be. Using this is required in some development environments. Its use will make the returned column names not match the normally defined schema.

bulletOLE DB Services=-2

bulletOptimizerOn = Yes / No

bulletUseCaseSensitiveRefNumber = Yes / No

Example of simple DSN
sConnectString = "DSN=QuickBooks Data;OLE DB Services=-2;"

Example of a DSNless connection string
sConnectString = "Driver={QODBC Driver for QuickBooks};DFQ=C:\Users\Public\Documents\Intuit\QuickBooks\Sample Company Files\QuickBooks Enterprise Solutions 14.0\sample_service-based business.qbw;OpenMode=M;OLE DB Services=-2;"

Example of using current ADO syntax
sConnectString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=QuickBooks Data;OLE DB Services=-2;"

Standard syntax
"DRIVER={QODBC Driver for QuickBooks};SERVER=QODBC;DFQ=.;OLE DB Services=-2;OptimizerOn=No;"

 

Additional Parameters for QuickBooks Online Edition (QBOE)

Parameters Added for QBOE

bulletIBizOEConnection = Yes / No
bulletIBizOETicketGUID = GUID from web login
bulletIBizOEPromptForLogin = Yes / No

A complete Example using the QODBC Remote Connector in the connection string

DRIVER={QODBC Driver for QuickBooks};OptimizerDBFolder=%AppData%\QODBC Driver for QuickBooks\Optimizer;IBizRemotePWD=123456;IBizRemoteConnect=Yes;IBizRemoteURL=http://192.168.1.10:2080; IBizRemoteConnTimeOut=120;IBizRemotePromptForPWD=No;IBizRemoteForce2002_2003=Yes; OpenMode=M;DFQ=C:\QBOOKSDW.QBW;OLE DB Service=-2

 


Additional Text Field Handling Parameters in Connection String

Instructions

       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 fields that would not be able to otherwise. Connection string options include:

Parameters Added in Connection String

bulletConvertToLongVARCHAR = Yes / No
bulletMaxLONGVARCHAR = 4096

Parameters in Connection String for QODBC v9

General Tab

bulletDFQ =. (Or company file full path)
bulletOpenMode = F / M / S

Server Edition Tab

bulletUseDCOM = Yes / No

RDS Server Tab

bulletUseRDS = Yes / No

Remote Connector Tab

bulletIBizRemoteConnect = Yes / No
bulletIBizRemoteURL =
bulletIBizRemoteUID =
bulletIBizRemotePWD =
bulletIBizRemoteSSLServerCert =
bulletIBizRemotePromptForPWD = Yes / No
bulletIBizRemoteConnTimeOut =
bulletIBizRemoteForce2002_2003 = Yes / No

Online Edition Tab

bulletIBizOEConnection = Yes / No
bulletIBizOETicketGUID = (Connection Key)
bulletIBizOELoginGUID = (Login Key)
bulletIBizOEPromptForLogin = Yes / No
bulletIBizReconnectionDelay =

Optimizer Tab

bulletOptimizerOn = Yes / No
bulletOptimizerCurrency = M (Manual) / O (Per Connection) / Y (Yesterday) / L (Last Month) / R (Per Connection with a Reload first) / C (Current meaning every query)
bulletOptimizerDBFolder =
bulletOptimizerEncryptionOn = Yes / No
bulletOptimizerForceDelete = Yes / No
bulletOptimizerAllowDirtyReads = N (Nulls) / D (Dirty) / R (Read direct)
bulletOptimizerSyncAfterUpdate = Yes / No

Advance Tab

bulletColumnNameLen =
bulletNumberCustomFields = Yes / No
bulletUseCaseSensitiveRefNumber = Yes / No

bulletUseODBCCompatibility = Yes / No
bulletUseSimulatedTransactions = Yes / No
bulletFileNameCase = Upper / Lower
bulletUseIterators = Yes / No
bulletIteratorChunkSize =
bulletIAppLaunchQBUI = Yes / No
bulletIAppAutomaticLogin = Yes / No
bulletIAppReadOnly = Yes / No
bulletIAppAllowPersonalData = Yes / No
bulletConvertToLongVARCHAR = Yes / No
bulletMaxLONGVARCHAR =

bulletSyncFromOtherTables = Yes / No
bulletNoIncludeRaw = Yes / No

ODBCINST.INI settings

Messages Tab

bulletDebugPanel = Yes / No
bulletShowICON = Yes / No
bulletShowICONWarnings = Yes / No
bulletOptimizerPanel = Yes / No
bulletVerboseOptimizerPanel = Yes / No
bulletTracingOn = Yes


Also, Refer: How I build a connection string with all the available QRemote parameters / Connection String with QRemote Parameters.

(228 vote(s))
Helpful
Not helpful

Comments (2)
don meehan
30 September 2017 12:30 AM
how would i build string for DAO from Access? I formerly used this..

qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"

before upgrading to 64-bit Access, QB 2016, and new Qodbc with REMOTE server...

thanks!

Jack
03 October 2017 06:31 AM
Hi Don,

You need to use QRemote 64-Bit DataSource (DSN) in connection string as below & check again:

qd.Connect = "ODBC;DSN= QuickBooks Data 64-Bit QRemote;SERVER=QODBC"

If you are still facing issue, Please raise a support ticket to the QODBC Technical Support department from below mentioned link & provide requested information:
http://support.flexquarters.com/esupport/index.php?/Tickets/Submit

We may need the following information, Please attach below listed files when replying to the ticket.
1) Screenshot of QODBC Setup Screen -- > About
2) Screenshot of the issue you’re facing.
Share Entire Log Files as an attachment in text format from
3) QODBC Setup Screen -- > Messages -- > Review QODBC Messages
4) QODBC Setup Screen -- > Messages -- > Review SDK Messages
Refer: How to take screenshot: www.qodbc.com/links/screenshot.htm
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).