QueryDefs.sql fails in 2010

[Leif]

Using Access 2003 I update a query dynamically and it works fine. Below is a snippet of the code.

    strSQLOrig = CurrentDb.QueryDefs("qryImportDocsFilter").sql
    
    strSQL = Replace(strSQLOrig, "ExtMDB", "'" & ImportFile & "'")
    
    If txtContractor = "Fluor" Then
        strSQL = Replace(strSQL, "JEG Proj", "FLR Proj")
    End If
    
    CurrentDb.QueryDefs("qryImportDocsFilter").sql = strSQL

In 2010 it fails on the first line. I'm using the development version of 2010. The error I get is:

Microsoft Access has stopped working

Windows can try to recover your information and restart the program

I tried replacing the first line with the actual SQL from the query. When I do that it fails on the last line where I try to update the query it the changed SQL. The if statement for txtContractor in the code does not execute in my test.

Something different with QueryDefs in 2010?

 

[jdraw]

When you post code please post the whole thing. A snippet like this is not helpful unless you describe what you are trying to do; provide some sample input and what you think the output/answer should be.

 

[Leif]

I've found the problem is not with the coding, but with the query.

Here is the SQL for the query qryImportDocFilter:

SELECT [D&D Dwg List].AFC, nz([D&D Dwg List].[JEG Proj]) AS CPN, [D&D Dwg List].[PrintedDoc#],
SpecialPDN([APCDoc#],[PrintedDoc#]) AS MPDN, [D&D Dwg List].[ApcDoc#],
[D&D Dwg List].Rev, [D&D Dwg List].DocType, [D&D Dwg List].DocSubType, 
[D&D Dwg List].Title1, [D&D Dwg List].Title2, [D&D Dwg List].Title3, 
[D&D Dwg List].[PSMDocumentY/N], [D&D Dwg List].Issued_For,
IIf(IsNumeric([D&D Dwg List].[Trans_No]),CStr([D&D Dwg List].[Trans_No]),
[D&D Dwg List].[Trans_No]) AS Trans_No, [D&D Dwg List].Comments
FROM [D&D Dwg List] IN EXTMDB;

Note EXTMDB at the end of the query is replaced. Also note when I open the query in Access 2003 I get the above query as SQL. When I try to open the query in Design View in 2010 I get a fatal error. I'm thinking perhaps when 2010 opens the query it cannot find EXTMDB (which is really just a placeholder to be replaced with the external Access location and file name).

 

[Leif]

Well I have a work-around, although I still don't know what caused the problem.

I simply recreated the query using SQL. I don't know why it works in 2003, but not 2010 with the old query. I guess it will be one of those great unsolved mysteries. You would probably need to work on the Access coding at Microsoft to understand the reason.