Opening Linked Tables cause (What Seems)Indefinite loading.

[schulzy175]

I have QODBC setup for Quickbooks and the QB company file has a lot of information in it. Therefore, I would assume the linked-tables would be large. While importing linked tables rather than importing into non-linked tables, it is fairly quick. But opening one linked table takes ages. As a matter of fact, I haven't been able to open a linked table because it takes so long. If I import the tables rather than link, it takes even longer which I would assume is because it is not creating linked tables. How can I use Access at all if the tables take way too long to open or query? How can I find a work around for this? Any suggestions? Thanks.

[aytee111]

From a friend!

"I have used QODBC extensively and I have found it painfully slow doing pretty much anything. When reading in a linked table and doing any kind of filtering i.e. Select * from Customers where Zipcode = '12345' then expect it to take forever. I have found it quicker by far to read the entire table sequentially and do the filtering in code. Random lookups can be done if you are only after a very few records. Each lookup can take significant time.

There is an optimizer built into QODBC and if the table has not been optimized recently, when you open the table it will be optimized and, depending on the amount of records in the table, this can take a long time (possibly more than an hour) before your first record is retrieved. The optimizer settings can be changed in the QODBC configuration. I believe the default is to optimize the table every time it is opened. This is the slowest. I am not on my development PC now so I can't check on the other options. Try opening a QB table that has very few records. It should open reasonably quickly - certainly less than 10 seconds.


The optimizer data is stored on the PC doing the query so if you are accessing a large table for the first time, there will be a significant delay. Once you have opened the table on your PC, subsequent openings will take far less time because the bulk of the optimization has been done for that table. You can still expect some delay though.



I have an update that extracts a bunch of records from a number of different QB tables to populate a website and it runs reliably every night and has done for years. So, even though it is painful to use, you can achieve good results. The QODBC support people were generally excellent ... quick, responsive and helpful. I hope they are still like that.

I don't know what internal database structure they use but it is archaic. If you do attempt a complicated select clause like a select that includes a sub query or linking a couple of tables in the query, expect a message saying 'Catastrophic Failure'.



Good luck."

[schulzy175]

Wow, I appreciate all the info! Definitely gave me a head start (if you couldn't tell I just got QODBC going). Do you run your queries/tables through sql code or criteria based in MS Access? Currently I am using criteria in Access, but not sure if it is making any performance difference. EDIT: Do you run your QODBC Setup through Admin or a lower UAC user? Currently my PC is our company server and I can only login using admin, but I have setup another lower UAC user that I can shift+RMB and choose "Run as Different User", but it seems the QODBC doesn't like this because it doesn't act normal.

[NigelIT]

Hi,

I'm the friend. Sorry about the delay ... I am building a barn with a friend and we work 7 days and 7 nights (well, until about 10 pm).

I developed the QODBC stuff using Access 2003 so I use ADODB recordsets to retrieve the data from QB. All the QB tables are linked to my Access database using QODBC and I access them like I would retrieve data from an Access table. I define the SQL in a string field and open an ADODB.Recordset. I keep the queries as simple as possible. In some cases, I do add a where clause to the SQL select like when I extract invoices to calculate commissions. It is a huge table and even though the query takes forever to run, it is still quicker than sequentially accessing every record. It only runs once a month so the delay is acceptable.

I use Admin as the user for QODBC. I didn't want to deal with difficulties in accessing data. I have never tried using an alternate userid so I don't have an answer for that.

Feel free to ask questions. I don't get to my PC often so it is unlikely I will answer more than once per day but I will do my best. I went through a lot of pain learning how this works so I am happy to share.

[schulzy175]

Hi,

I'm the friend. Sorry about the delay ... I am building a barn with a friend and we work 7 days and 7 nights (well, until about 10 pm).

I developed the QODBC stuff using Access 2003 so I use ADODB recordsets to retrieve the data from QB. All the QB tables are linked to my Access database using QODBC and I access them like I would retrieve data from an Access table. I define the SQL in a string field and open an ADODB.Recordset. I keep the queries as simple as possible. In some cases, I do add a where clause to the SQL select like when I extract invoices to calculate commissions. It is a huge table and even though the query takes forever to run, it is still quicker than sequentially accessing every record. It only runs once a month so the delay is acceptable.

I use Admin as the user for QODBC. I didn't want to deal with difficulties in accessing data. I have never tried using an alternate userid so I don't have an answer for that.

Feel free to ask questions. I don't get to my PC often so it is unlikely I will answer more than once per day but I will do my best. I went through a lot of pain learning how this works so I am happy to share.

Excuse my language, but you are fucking awesome! This puts me in a great direction. It seems though that QODBC is good for specific queries or SQL statements for proficiency and speed which makes me plan out my goals for the driver more accordingly. I appreciate the help very much and hope everything continues to go well on your end with QODBC as well. Also, to note: The reason I asked if you run your QODBC setup under Admin is because if I run mine under admin, it doesn't save my settings if I change anything like the optimizer. And if I run it under a non-admin user then it will save the settings, but it doesn't link/connect to QB properly and gives me errors then freezes and closes. I'll have to find a way to apply my optimizer settings. Last thing I promise, but what settings do you have your optimizer set to? I cannot seem to find the most appropriate setting (even if I could save my changes/settings).

[NigelIT]

Thank you!

My optimizer settings are: 'Keep my optimized data up to date as of: The start of every query' and then further down where it says 'For balances and stock quantities', I have 'Dirty Reads' selected. This was based on a whole lot of back and forthing with the QODBC people. I said the queries were taking too long and they said this was the best way to speed them up but the data would not be completely up to date. This took place some years ago so I forget all the reasoning behind this decision. It has worked well for us though.

I am surprised that your optimizer settings are not being saved. From where do you run your QODBC setup? I run mine from Start/Administrative Tools/Data Sources (ODBC). That opens the ODBC Data Source Administrator, click on System DSN tab, select QuickBooks Data and click on Configure. This is an old 2003 Server but it is still where the application is stored. I am sure it will be hard to find and be called something different on a server belonging to this decade. I don't setup QODBC from within QB.

I hope this helps.

[schulzy175]

So for QODBC Setup, I usually search "qodbc setup" on my start menu and it brings up the program titled "QODBC Setup Screen". But I also went to my odbc setup under administrator tools and odbc32 and noticed that I wasn't able to change much on there nor did I feel like I needed to. For now the optimization is fine until I can better figure out how to pull my queries. Are you using excel vba, ms access vba, or other programs like crystal reports, etc? I did some research on stored procedures and came along the P&L stored procedure, but I cannot seem to figure out if the syntax is meant for SQL or VBA to SQL? Here is a screenshot.

[schulzy175]

Update: So I got everything working great with Excel which is probably what I'd prefer to do anyways since I'd be exporting it from Access to Excel just to run macros. This driver is excellent when you really get a good feel for the functionality of it. Thanks for all your assistance.

[NigelIT]

Sorry, I didn't see your posts until today. I am new to the forum and I was expecting an email if the thread was updated. Oops. I use Access pretty much exclusively. I have used Crystal but I prefer Access reports.

I am glad you have it all working.