Solved Errors with calling QueryDef

[Kayleigh]

Hi,
I am working on moving a DB to SQL server back end but there are a couple of errors which I am having trouble resolve.
In particular there is one form that has the following code when opens to change a querydef. The query runs fine but these lines of code keep flagging an error and I'm not sure why. (it worked fine until now)

Set db = CurrentDb
Set qdf = db.QueryDefs("qryCashProjectionByWeekTotals")
Debug.Print qdf.SQL

See the error below

 

[arnelgp]

on my experience Driver 17 is toooo slow to connect with.
i change the driver to Native Client 11.

 

[gemma-the-husky]

Your code should just show you the SQL for the query in the front end. I don't see how that would involve the SQL Server unless one of the field definitions in the query is reading the back end.

Would it still need to read the SQL Server back end to show you the SQL for the query?

Can you show us the "funny" bit of the query?

 

[oleronesoftwares]

The query runs fine but these lines of code keep flagging an error and I'm not sure why. (it worked fine until now)

So in school,i was thought when such happens, check what was the last thing, or last set of changes you made in the application.

Since u said it worked fine until now.

its advisable to have a log of changes been made by you while working on an application development project.

 

[The_Doc_Man]

So in school,i was thought when such happens, check what was the last thing, or last set of changes you made in the application.

Since u said it worked fine until now.

its advisable to have a log of changes been made by you while working on an application development project.

Sad but true. Access programmers very often are tinkerers and as such are their own worst enemies.

<<sheepishly raises hand and pleads guilty to "tinkerer" charges>>

 

[Kayleigh]

Your code should just show you the SQL for the query in the front end. I don't see how that would involve the SQL Server unless one of the field definitions in the query is reading the back end.

Would it still need to read the SQL Server back end to show you the SQL for the query?

Can you show us the "funny" bit of the query?

I don't think so. But it is a pass-through query if that makes a difference?

I'm also having trouble with opening recordsets to add new record in vba - seems to say it can't make changes to linked table?

 

[bastanu]

Can you show us the SQL for the pass-through? How many records are you trying to update? Can you get it to run with a single record? You need to remember that pass-through queries need to be written in the SQL variant of the back-end server that will run them.
As for editing or adding new records with recordsets based on SQL tables you need to add dbSeeChanges to the options when you set them.
Here is recent thread explaining this:

Solved - Error 3662 You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column

I managed to connect sql server and ms access database and now I am gettting some errror. "You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column". This error appears when you open recordset that contains IDENTITY column. I search...

www.access-programmers.co.uk

Cheers,

 

[Kayleigh]

Yes that has has definitely solved SOME errors I've been having!

But can't use dbSeeChanges in my code for the queryDef - so how would I do this?

The code to change the WHERE clause is here. If there is a different way to achieve this whilst using the dbSeeChanges I would be happy to try.

Set db = CurrentDb
Set qdf = db.QueryDefs("qryCashProjectionByWeekTotals")
orgSQL = qdf.SQL

varWhere = " WHERE (((IIf(DatePart(""ww"",[fldOAnticipatedPayDate])=53,1,DatePart(""ww"",[fldOAnticipatedPayDate])))=" & iWeek & ")) OR (((IIf(DatePart(""ww"",[fldOAnticipatedPayDate])=53,1,DatePart(""ww"",[fldOAnticipatedPayDate])))=" & iWeek1 & ")) OR (((IIf(DatePart(""ww"",[fldOAnticipatedPayDate])=53,1,DatePart(""ww"",[fldOAnticipatedPayDate])))=" & iWeek2 & ")) OR (((IIf(DatePart(""ww"",[fldOAnticipatedPayDate])=53,1,DatePart(""ww"",[fldOAnticipatedPayDate])))=" & iWeek3 & "))"

strsql = Replace(orgSQL, ") qryCashProjectionByWeek", varWhere & ") qryCashProjectionByWeek")

qdf.SQL = strsql

I've attached the SQL for the pass-through query - it always has no issue running on its own.

 

[bastanu]

Sorry but I doubt this is a true pass-through query as it seems to rely on objects named "qry..." which I assume are Access queries and not SQL server tables\views. I would recommend that you try to replace this with a SQL stored procedure that you would call from Access (passing the required parameters that you currently have in the WHERE clause).

Cheers,

 

[gemma-the-husky]

I have been out and just returned

Set qdf = db.QueryDefs("qryCashProjectionByWeekTotals")
Debug.Print qdf.SQL

so does the second line not print the text of the query? Why would this actually run the query?

these are the lines you said produce the error about SQL Server timing out. weren't they?

 

[Kayleigh]

Yes that is correct.

 

[gemma-the-husky]

so if you just try the following instead of debug.print the msgbox doesn't display, and you get the error?
It's that the msgbox will halt the code, until you dismiss it, but the debug statement won't, and the error might be on the next line after.

msgbox qdf.sql

 

[Kayleigh]

Thanks I think I've finally resolved it!
I realised there were some flaws with the SQL when I tried running in SSMS it showed the errors. Learnt a lot along the way though