asked on

I am having an ODBC timeout issue with an Access Frontend

Hello All

I am having an odd thing happen. I have a form in Access that runs 2 append queries. One will append fields on the form to one table and the other will append different fields to a different table. Neither table is linked in any way to each other. The only thing they share is the same key field (basically I originally had all the fields in one table but it started to get hard to deal with so I just split it into 2 tables).

The append queries each add about 20 fields to each table (one record). The first append query runs fine, but then I get a timeout on the second one (I have attached the error).

This only started happening today. Up until now all was fine.

The tables have a little over 11000 records. There are about 20 people in the company but only 2 people at most use that form that runs those 2 append queries.

Here is the Append Query that is failing

INSERT INTO QuoteStatusAddendum ( TrueID, [Standard Order], [ARC Type], [ARC Wording], QuoteItem, [Discount Type], [Main Or Copy], [Raman Notes] )
SELECT [Forms]![Popup ARC]![txtIDBuilder] AS Expr1, [Forms]![Popup ARC]![chkStandardOrder] AS Expr2, [Forms]![Popup ARC]![cboProductID] AS Expr3, [Forms]![Popup ARC]![txtARCDetails] AS Expr4, [Forms]![Popup ARC]![cboQuoteItemName] AS Expr5, "None" AS Expr6, "Main" AS Expr7, [Forms]![Popup ARC]![txtIDBuilder] AS Expr8;

Any insights would be much appreciated.

Thanks!
ODBC-Error.jpg

SOLUTION

UnifiedIS

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alevin16

ASKER

Hey UnifiedIS

Neither table is linked to the form. Is that possible that one append query can lock the second?

alevin16

ASKER

Hello Again UnifiedIS

I just tried flipping the order of the two queries so that it runs the bad one first and it still does not work (I put in a msgbox between the two that says "I am Here" and it never popped the msgbox up.

Thanks

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alevin16

ASKER

For now I recreated the table, query and form. That seemed to fix it. I think there was some sort of locking that was going on but I just do not have the time to keep looking into it. Plus I think Pat hit it on the head, I need to wean my way off Access

PatHartman

If you use Access as it was intended, you will find it pretty powerful and it will do a great deal of the work for you. Access is a narrowly focused tool. It excels at data-centric client/server applications. It is very scaleable if your BE is SQL Server or some other server based RDBMS. It doesn't work well over the internet and you get no benefit from it if you don't use its powerful features. All you get is the baggage.