orbitus
asked on
**HELP PLEASE** 'ODBC--Remote query timeout expired' Access 2003
I have a module that runs 200+ queries and updaates a form with a label and a progress bar. Each query copies information from a linked table to an Exchange mailbox folder. I manually linked all the subfolders of the mailbox each to its own table, then created an append query for each table to add to one master table. letting me know where each message is at any given time.
My problem is that all of these queries used to work fine with Access XP but ever since this machine has been upgraded to Office 2003 I have been getting an error message 'Run-time Error 3234: ODBC--Remote query timeout expired'. The machine was upgraded to Office 2003 yesterday and the queries have not worked since then.
Here is an example of the code that initiates the queries
...
Form_frmProgressBar.Label2 .Caption = "GPMJR BNR Dept 23 Rejected Jrfs"
DoCmd.OpenQuery "qryGPMJR BNR Dept 23 Rejected Jrfs", acViewNormal, acEdit
Form_frmProgressBar.myProg ressBar.Va lue = Form_frmProgressBar.myProg ressBar.Va lue + 0.42
Form_frmProgressBar.Label2 .Caption = "GPMJR BNR Dept 24"
DoCmd.OpenQuery "qryGPMJR BNR Dept 24", acViewNormal, acEdit
Form_frmProgressBar.myProg ressBar.Va lue = Form_frmProgressBar.myProg ressBar.Va lue + 0.42
Form_frmProgressBar.Label2 .Caption = "GPMJR BNR Dept 25"
DoCmd.OpenQuery "qryGPMJR BNR Dept 25", acViewNormal, acEdit
Form_frmProgressBar.myProg ressBar.Va lue = Form_frmProgressBar.myProg ressBar.Va lue + 0.42
...
Here is an example of the SQL from one of the queries
INSERT INTO DMTable ( Importance, [Message Class], Priority, Subject, [From], [Message To Me], [Message CC to Me], [Sender Name], CC, [To], Received, [Message Size], Body, [Creation Time], [Last Modification Time], [Subject Prefix], [Has Attachments], [Normalized Subject], [Object Type], [Content Unread], JRFno, Emergency, RapidRefresh, Dept, DueDate, Folder )
SELECT [GPMJR BNR Dept 23 Rejected Jrfs].Importance, [GPMJR BNR Dept 23 Rejected Jrfs].[Message Class], [GPMJR BNR Dept 23 Rejected Jrfs].Priority, [GPMJR BNR Dept 23 Rejected Jrfs].Subject, [GPMJR BNR Dept 23 Rejected Jrfs].From, [GPMJR BNR Dept 23 Rejected Jrfs].[Message To Me], [GPMJR BNR Dept 23 Rejected Jrfs].[Message CC to Me], [GPMJR BNR Dept 23 Rejected Jrfs].[Sender Name], [GPMJR BNR Dept 23 Rejected Jrfs].CC, [GPMJR BNR Dept 23 Rejected Jrfs].To, [GPMJR BNR Dept 23 Rejected Jrfs].Received, [GPMJR BNR Dept 23 Rejected Jrfs].[Message Size], [GPMJR BNR Dept 23 Rejected Jrfs].Body, [GPMJR BNR Dept 23 Rejected Jrfs].[Creation Time], [GPMJR BNR Dept 23 Rejected Jrfs].[Last Modification Time], [GPMJR BNR Dept 23 Rejected Jrfs].[Subject Prefix], [GPMJR BNR Dept 23 Rejected Jrfs].[Has Attachments], [GPMJR BNR Dept 23 Rejected Jrfs].[Normalized Subject], [GPMJR BNR Dept 23 Rejected Jrfs].[Object Type], [GPMJR BNR Dept 23 Rejected Jrfs].[Content Unread], getdigitnumber([subject],1 3,True,1) AS JRFno, getcomments([Body],'Emerge ncy = ',1) AS Emergency, IIf(InStr([subject],'RAPID ')>0,'Y',' N') AS RapidRefresh, getcomments([Body],'Depart ment = ',2) AS Dept, getcomments([Body],'Due_Da te = ',8) AS DueDate, 'GPMJR BNR Dept 23 Rejected Jrfs' AS Folder
FROM [GPMJR BNR Dept 23 Rejected Jrfs];
What I have tried...
Changing the properties of the query to have an odbc timeout of 0
Went to Tools -> Options -> Advanced and changed the OLE/DDE timeout to 0
Went to HKEY_LOCAL_MACHINE\softwar e\microsof t\jet\4.0\ engines\OD BC and tried upping the timeout to b4 (180)
It seems to be giving the error message no matter how long it actually takes, but if I choose to debug and run step into it, it runs fine.
I am at a loss, please help
Chris
My problem is that all of these queries used to work fine with Access XP but ever since this machine has been upgraded to Office 2003 I have been getting an error message 'Run-time Error 3234: ODBC--Remote query timeout expired'. The machine was upgraded to Office 2003 yesterday and the queries have not worked since then.
Here is an example of the code that initiates the queries
...
Form_frmProgressBar.Label2
DoCmd.OpenQuery "qryGPMJR BNR Dept 23 Rejected Jrfs", acViewNormal, acEdit
Form_frmProgressBar.myProg
Form_frmProgressBar.Label2
DoCmd.OpenQuery "qryGPMJR BNR Dept 24", acViewNormal, acEdit
Form_frmProgressBar.myProg
Form_frmProgressBar.Label2
DoCmd.OpenQuery "qryGPMJR BNR Dept 25", acViewNormal, acEdit
Form_frmProgressBar.myProg
...
Here is an example of the SQL from one of the queries
INSERT INTO DMTable ( Importance, [Message Class], Priority, Subject, [From], [Message To Me], [Message CC to Me], [Sender Name], CC, [To], Received, [Message Size], Body, [Creation Time], [Last Modification Time], [Subject Prefix], [Has Attachments], [Normalized Subject], [Object Type], [Content Unread], JRFno, Emergency, RapidRefresh, Dept, DueDate, Folder )
SELECT [GPMJR BNR Dept 23 Rejected Jrfs].Importance, [GPMJR BNR Dept 23 Rejected Jrfs].[Message Class], [GPMJR BNR Dept 23 Rejected Jrfs].Priority, [GPMJR BNR Dept 23 Rejected Jrfs].Subject, [GPMJR BNR Dept 23 Rejected Jrfs].From, [GPMJR BNR Dept 23 Rejected Jrfs].[Message To Me], [GPMJR BNR Dept 23 Rejected Jrfs].[Message CC to Me], [GPMJR BNR Dept 23 Rejected Jrfs].[Sender Name], [GPMJR BNR Dept 23 Rejected Jrfs].CC, [GPMJR BNR Dept 23 Rejected Jrfs].To, [GPMJR BNR Dept 23 Rejected Jrfs].Received, [GPMJR BNR Dept 23 Rejected Jrfs].[Message Size], [GPMJR BNR Dept 23 Rejected Jrfs].Body, [GPMJR BNR Dept 23 Rejected Jrfs].[Creation Time], [GPMJR BNR Dept 23 Rejected Jrfs].[Last Modification Time], [GPMJR BNR Dept 23 Rejected Jrfs].[Subject Prefix], [GPMJR BNR Dept 23 Rejected Jrfs].[Has Attachments], [GPMJR BNR Dept 23 Rejected Jrfs].[Normalized Subject], [GPMJR BNR Dept 23 Rejected Jrfs].[Object Type], [GPMJR BNR Dept 23 Rejected Jrfs].[Content Unread], getdigitnumber([subject],1
FROM [GPMJR BNR Dept 23 Rejected Jrfs];
What I have tried...
Changing the properties of the query to have an odbc timeout of 0
Went to Tools -> Options -> Advanced and changed the OLE/DDE timeout to 0
Went to HKEY_LOCAL_MACHINE\softwar
It seems to be giving the error message no matter how long it actually takes, but if I choose to debug and run step into it, it runs fine.
I am at a loss, please help
Chris
I would check for missing references. Hit Ctrl + G to open a VB window. Then go to Tools --> References and see if any say Missing.
ASKER
nope, nothing missing
ASKER
This is what the help file says...
ODBC — remote query timeout expired. (Error 3234)
The ODBC server may not be properly installed, or a required network connection is not active. Try increasing the value of the QueryTimeout property.
ODBC — remote query timeout expired. (Error 3234)
The ODBC server may not be properly installed, or a required network connection is not active. Try increasing the value of the QueryTimeout property.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.