There may be a third option to QODBC and QBSDK that doesn't suck, cost money, or waste a LOT of your time. It can be done with native Windows Server tools alone if you are not terrified of .NET and PowerShell.
Intuit hides it but, Quickbooks Enterprise supports ODBC access natively for custom reporting. Lesser editions might/might not; I have not worked with them. So ...
PLEASE, IF YOU ATTEMPT THIS METHOD, leave a comment with your results and the edition/year of your Quickbooks installation for posterity.
Caveats, Provisos, Disclaimers, and the like.
The underlying data engine in Quickbooks is Sybase's SQL Anywhere. So to write your queries you will need to familiarize yourself with Sybase conventions and syntax. See References at the end of this article.
This procedure can only be performed on a system that Quickbooks is installed upon. The proprietary Sybase drivers are part of the QB installation. So if you intend to do this "across the net", you must have the same edition/version of Quickbooks installed on both ends.
You cannot access a company file that has been left in "single-user" mode using this method. You'll need to log in as admin and change the mode to "multi-user"
Pay careful attention to your access method defined in the connection string. It is easy to leave a company file inaccessible until reboot. See ASTOP below.
It will always be safest to make copies of your files and access the copies. You will need the .qbw, .qbw.DSN, .qbw.ND, and .qbw.TLG files. This may be impractical if your company file is constantly in use and, like myself, you need to perform dozens of extracts daily.
Always have a backup before accessing your data in this (or any other) manner.
And finally, if this blows up in your face, I was never here...
Procedure
- Using the admin account, establish an ODBC user account. This is separate from your QB logins. Grant the account only those rights it needs for your application. The user name and password will be used in your connection string plain text so any file containing it must be kept secured.
- Construct a connection string. Most of the required parameters are provided by the QB DSN file that is created in the same folder as your company file. But the data needs to be converted to Sybase convention. And special care needs to be given to the DBF (DatabaseFile), LINKS (CommLinks), ASTOP (AutoStop) parameters; ASTOP needs to be 'NO' for multi-user access via the QB Server (using LINKS=TCP...) and 'YES' for direct file (shared memory) access (using DBF=). If AutoStop is not set appropriately for your access type, you may well lock everyone out of the file until you reboot.
Examples connection strings:
For direct connect (DBF=.../ASTOP=YES):
connStr = "DRIVER={QB SQL Anywhere}; `
DBF=<full path to company file>; `
ENG=<ServerName from QB DSN file>; `
UID=<ODBC account>;PWD=********; `
ENP=<DatabaseName from QB DSN file>; `
LOG='<Path to a debug log file>'; `
ASTOP=YES;INT=NO;DBG=NO;DMRF=NO;COMP=NO";
For access via the server (LINKS=.../ASTOP=NO):
connStr = "DRIVER={QB SQL Anywhere}; `
LINKS=<CommLinks from QB DSN file>; `
ENG=<ServerName from QB DSN file>; `
UID=<ODBC account>;PWD=********; `
ENP=<DatabaseName from QB DSN file>; `
LOG='<Path to a debug log file>'; `
ASTOP=NO;INT=NO;DBG=NO;DMRF=NO;COMP=NO";
- Write your code. Here's an example:
function Export-QbKe {
[CmdletBinding()]
param(
# Full path to company (.qbw) file
[Parameter(Mandatory)]
[string]$QbDataFile,
# ODBC username as set under Custom Reports in Quickbooks
[Parameter(Mandatory)]
[string[]]$aQueries,
# ODBC username as set under Custom Reports in Quickbooks
[Parameter(Mandatory)]
[string]$User,
# ODBC user password as set under Custom Reports in Quickbooks
[Parameter(Mandatory)]
[string]$Pswd,
# Flag for using TCP (company file has active users) or Shared Memory (company file is inactive)
[Parameter(Mandatory)]
[bool]$UseTCP,
# Full path to output XML file.
# The DSN Debug file will be created/appended in the same folder
[Parameter(Mandatory)]
[string]$DataSetFile
)
# Construct Paths
$DebugDSNPath = ( Split-Path -Path $DataSetFile -Parent ) + "\DebugDsn.log";
$QbDsnFile = $QbDataFile + ".dsn"
# Construct Sybase compliant connection string from QB DSN file
$connStr = "UID=" + $User + ";PWD=" + $Pswd + ";LOG='" + $DebugDSNPath + "';"
#$connStr = "UID=" + $User + ";LOG='" + $DebugDSNPath + "';"
Get-Content $QbDsnFile | ForEach-Object {
If ($_ -match "=") {
$line = $_
$p = ($line -Split "=")[0]
$v = ($line -Split "=")[1]
Switch ($p) {
("driver") {$connStr += "DRIVER={" + $v + "};"}
("compress") {$connStr += "COMP=" + $v + ";"}
("DisableMultiRowFetch") {$connStr += "DMRF=" + $v + ";"}
("debug") {$connStr += "DBG=" + $v + ";"}
("integrated") {$connStr += "INT=" + $v + ";"}
("ServerName") {$connStr += "ENG=" + $v + ";"}
("DatabaseName") {$connStr += "ENP=" + $v + ";"}
("CommLinks") {
if ($UseTCP) { $connStr += ($line -replace "commlinks","LINKS") + ";ASTOP=NO;"}
else { $connStr += "DBF='" + $QbDataFile + "';ASTOP=YES;"}
}
}
}
}
# Instantiate required objects
$conn = New-Object System.Data.Odbc.OdbcConnection
$cmd = New-Object System.Data.Odbc.OdbcCommand
$adapter = New-Object System.Data.Odbc.OdbcDataAdapter
$ds = New-Object System.Data.DataSet
# Attempt connecting to Sybase - bail on failure
"Connecting to " + $QbDataFile + " using:"
$connStr -replace "=$Pswd","=********" # Obfuscate the password
try {
$conn.ConnectionString = $connStr
$conn.Open()
$cmd.Connection = $connStr
$adapter.SelectCommand = $cmd
}
catch {
$Err = "An error occurred on open. Extract is terminated."
#Alert $Err
$Err
return $false
}
<#
Example array of queries:
$aQueries = @(
"select * from v_cf_custom_field"
,"select * from v_cf_customer"
,"select * from v_cf_item"
,"select * from v_cf_name"
,"select * from v_lst_contacts"
,"select * from v_lst_customer"
,"select * from v_lst_employee"
,"select * from v_lst_item"
,"select * from v_lst_names"
,"select * from v_lst_unit"
)
#>
# Perform queries
try {
foreach ( $qry in $aQueries )
{
$tname = $qry.Split()[-1]
$ds.Tables.Add($tname)
$cmd.CommandText = $qry
$recCount = $adapter.Fill($ds,$tname)
"$recCount records read from QB table: $tname"
}
}
catch {
$Err = "An error occurred while querying QB. The extract is terminated."
#Alert $Err
$conn.Close();$conn.Dispose();
$Err
return $false
}
$conn.Close();$conn.Dispose();
# Output DataSet to XML file
$writer = New-Object System.IO.StreamWriter $DataSetFile
try {
$ds.WriteXml($writer, [System.Data.XmlWriteMode]::WriteSchema)
}
catch {
$Err = "An error occurred while querying QB. The extract is terminated."
#Alert $Err
return $false
}
$writer.Close()
$writer.Dispose()
# Truncate DebugDSN.log
Set-Content $DebugDSNPath (Get-Content $DebugDSNPath -Tail 500)
}
Our Environment
As of this writing, we are running Quickbooks Desktop Enterprise 2021 on Windows Server 2012r2 (migrating to 2019 Q1-2023) with Remote Desktop Services. Our users RDP to the server to use Quickbooks. Our field service folk (who do not have access to Quickbooks) are coordinated via a website updated every 20 minutes by a Scheduled Task that utilizes the exact PowerShell function above. The resulting XML file is copied to our MSSQL server and imported into the websites database using the very cool Write-SqlTableData command (but that is a different episode).
References
Sybase Connection Strings
SQL Anywhere Reference
