QODBC Active Link Updating Records

[Bill Semitekol]

I am running Alpha 5 V-9 Platinum Edition with an active link to Quick Books Pro 2008 using QODBC Version 9.00.00.253.
The link works quite well and I can view all 1,800 Customer Records.
When I try to update a field in a record in the Alpha 5 default browse screen I get the following error message:

Server side error updating row.
database API specific error

Your database has returned the following error code and description to Apha Five.
Consult your data base documentation for further information.

11015 - '[QODBC] Expected lexical element not found:
SQL state is: 42000'
refresh the data to see the current server values.

I have contacted QODBC and they have not given me an answer to the problem.

Has anyone used QODBC with Alpha 5 Version 9 and been successful will updating data in Quick Books through the Alpha 5 data base?

[Steve636]

Re: QODBC Active Link Updating Records

Bill,
Did you ever get an answer to this? I receive the same error message.

[Bill Semitekol]

Re: QODBC Active Link Updating Records

No
The people at QODBC say it is Alpha's problem and I have not yet sent a bug report to them.
I will have a long weekend coming up so I plan on sending them a bug report.
I will let you know if I get it working.

[Steve636]

Re: QODBC Active Link Updating Records

Bill,

I tinkered with this a little and found that you can�t update a field from a browse and form because A5 attempts to update all related fields, even those that are read only.

You can try Xbasic and A5�s implementation of ADO to get better results. For example, using sample company (Rock Castle), the following code will insert a general ledger entry to Insurance, Disability against Job Abercrombie, Kitchen:

dim cs as C
dim conn as ole::adodb.connection
dim ra as N = 0
cs = "Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Data Source=QuickBooks Data"
conn.open(cs)
sql=<<%a%
Insert Into JournalEntryCreditLine (TxnDate, JournalCreditLineAccountRefListID,
JournalCreditLineAmount, JournalCreditLineMemo,FQSaveToCache)
VALUES
({d'2012-12-12'},'110000-933270541',4075.00,'ENTRY TO JOB',1)
%a%
conn.execute(sql,ra)
ra=0
sql=<<%a%
Insert Into JournalEntryDebitLine (TxnDate, JournalDebitLineAccountRefListID,JournalDebitLineAmount,
JournalDebitLineMemo, JournalDebitLineClassRefListID,JournalDebitLineEntityRefListID, FQSaveToCache)
VALUES
({d'2012-12-12'},'2A0000-933270542',4075.00,'ENTRY TO JOB','40000-933272658','170000-933272658',0)
%a%
conn.execute(sql,ra)
conn.close()
delete conn
delete cs


And the following code will query Customer records with a Salutation of �Mrs.�
dim cs as C
dim conn as ole::adodb.connection
cs = "Provider=MSDASQL.1;Persist Security Info=False;User ID=Admin;Data Source=QuickBooks Data"
conn.open(cs)
dim rs as ole::adodb.recordset
''rs.cursortype = 3
sql=<<%a%
select FullName from Customer NOSYNC
Where Salutation='Mrs.'
%a%
rs.open(sql,conn)
dim FullName as c = ""
while .not. rs.Eof()
temp = rs.fields.item("Fullname").value
Fullname = Fullname + temp + crlf()
rs.MoveNext()
end while
rs.close()
conn.close()
ui_msg_box("Full Name",FullName)

However, my limited experiments indicate that the use of A5�s limited ADO with Flexquarters� QODBC (see QODBC's posting on QODBC's failings with ADO) is not suitable for applications that require reading larger amounts of data from QB�it�s simply way too slow.

Try reading records from the very limited �Transaction� table from Rock Castle and you will get a feel for the speed. My guess is that you would have to code a message that stated, �Thanks for your query. Please check back in 6 months for the results.�

You may discover a faster method with this where I could not. Let me know.

[Al Buchholz]

Re: QODBC Active Link Updating Records

Bill

I'd suggest that you use the SQL - ADO update/insert from action scripting to update or insert into QB. I've had good luck using that as a starting point for developing code to insert or update QB data.

You may also want to use a passive link table instead of an active link table ( at least for now..).

The passive link table doesn't need to refresh, except as you require and may give you better performance then an active linked table.

The interface that Intuit provides (SDK) requires that the actual exchange of information with QB (read or write) travels through the QB program rather than directly to the QB database. There is good and bad with that, but performance does suffer.

I've found the best way to improve performance is 3 fold.
1. retrieve only the columns needed - adjust the columns in the select
2. retrieve only the rows need - use a where clause properly
3. refresh only when needed - probably the toughest choice of all.

Hope that helps.