problem with querydef 1

[hwkranger]

I'm changing a bunch of queries running from docmd to .execute, i've run into the oddest occurance perhaps someone can assist me:

*Yes I know about the naming schema - I didn't make it, and I don't have time to fix every name problem.

The former code:

DoCmd.OpenQuery "Recovery Step 1"

replacing code:

Dim db As DAO.Database
Dim qd As DAO.querydef

Set db = CurrentDb()
Set qd = db.QueryDefs("Recovery Step 1&quot
qd.Execute
Set db = Nothing
Set qd = Nothing

The problem:

I get, Error 3061 too few parameters. Expect 3.

The Query def:

UPDATE [DAILY OUTAGE TICKETS] SET [DAILY OUTAGE TICKETS].MatchID = [Forms]![Recover]![ID], [DAILY OUTAGE TICKETS].RecoveredOprID = [Forms]![Operator]![OperatorID], [DAILY OUTAGE TICKETS].RecoveryType = "Recovered", [DAILY OUTAGE TICKETS].RecoveryDate = Date()
WHERE ((([DAILY OUTAGE TICKETS].RecoveryType)="Research Required&quot AND (([DAILY OUTAGE TICKETS].ID)=[Forms]![Recover]!

• ));
  
  Thank you for your help in advance!
  
  
  
  Randall Vollen
  National City Bank Corp.
  
  Just because you have an answer - doesn't mean it's the best answer.

 

[willir]

hwkranger

Your query references forms -- recover and operator.

Are these forms open at the time the update query is run? Otherwise, the query will not be able to obtain critical info required for the update.

With the two forms open, try running the query manually. If it works, then you will need to look at how the query is being handled within VBA. Put a "Stop" before the execute command and use the debug window to print the value of the querydef. For example, quotation marks.

Richard

 

[hwkranger]

Thank you,

Yes those windows are open when the query is run. It does work when I manually run this..

But oddly enough, when I check the value of the SQL of the qd variable it gives me this:

"UPDATE [DAILY OUTAGE TICKETS] SET [DAILY OUTAGE TICKETS].MatchID = [Forms]![Recover]![ID], [DAILY OUTAGE TICKETS].RecoveredOprID = [Forms]![Operator]![OperatorID], [DAILY OUTAGE TICKETS].RecoveryType = "Recovered", [DAILY OUTAGE TICKETS].RecoveryDate = "

it's not the entire SQL statement that is saved for the Querydef...

is there a reason why it's cutting it short?

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[GGleason]

I'll take a guess as to why it is truncated. I copied the SQL statement into my text editor and it is 255 characters long. Some things in Access have a 255 character limit. Perhaps someone can shed some more light on this fact.

There is a character limit in SQL statements via the SQL portion when you build a new query and it is over 59,000 characters long.

If there is a character limit of some kind you may have to use aliases for your table names.
[tt]
"UPDATE [DAILY OUTAGE TICKETS] AS q0 SET q0.MatchID = [Forms]![Recover]![ID], q0.RecoveredOprID = [Forms]![Operator]![OperatorID], q0.RecoveryType = "Recovered", q0.RecoveryDate = "
[/tt]
This reduces the truncated SQL statement from 255 to 181 characters.

hth,
GGleason

 

[willir]

Randall

You have found out why you are getting the error. I have seen queries with over 7000 characters, but not in a domcmd.runsql statement.

This is a work-around. There may be a better solution.

Instead of using a querydef, use a recordset with the form. It is similar to what you have already done but uses DAO instead of SQL.


Dim db As DAO.Database
Dim rst As DAO.RecordSet
Dim strSQL as String, strQ as String
Dim intTest as Integer
Dim lngMatchID as Long, OprID as Long, TicketID as Long
Dim strRecoveryType as String
Dim dtRecoveryDate as Date

strQ = chr$(34) ' ascii character for "
lngMatchID = [Forms]![Recover]![ID]
lngOprID = [Forms]![Operator]![OperatorID]
dtRecoveryDate = Date()
strRecoveryType ="Research Required"
lngTickettID =[Forms]![Recover]!

• 
  
  strSQL = "Select * from [Daily Outage Tickets]"
  strSQL = strSQL & " WHERE [DAILY OUTAGE TICKETS].RecoveryType = " & strQ & strRecoveryType & strQ
  strSQL = strSQL & " AND [DAILY OUTAGE TICKETS].ID = " & TicketID
  
  Set db = CurrentDb()
  Set rst = db.OpenRecordSet(strSQL)
  
  intTest = rst.RecordCount
  
  if intTest > 0 then
  ' you may have to use a loop instead if many records
  with rst
  .edit
  .MatchID = lngMatchID
  .RecoveredOprID = lngOprID
  .RecoveryType = "Recovered"
  .RecoveryDate = dtRecoveryDate
  .update
  end with
  end if
  
  rst.close

 

[hwkranger]

Thank you to everyone who replied.

Using an alias isn't an options since - I can use the docmd statement. I was trying to change everywhere it has docmd to a .execute

As I think about this, It makes total sense as to why it's truncated... yet it seems very odd..

the qd is a querydef. as a VB querydef it's limited to 255 characters. Since it's only getting the first 255, when I try to execute it, it's not able to execute since it's not the entire query def

Yes this makes sense, but also - it makes total NONSENSE as to why they would limit a variable defined as a querydef from storing the entire query definition.

Kudos for the help, but if anyone knows a way to redim a querydef variable so that it stores more characters I'd love to know.



Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.

 

[boriska40]

Her is another solution. It looks pretty stupid,
but it worked in my test (Acc97).

Make Module with 3 functions (don't forget about Error handling):

Option Compare Database
Option Explicit

Public Function GetMatchID() As Long

GetMatchID = [Forms]![Recover]![ID]

End Function

Public Function GetRecoveredOprID()

GetRecoveredOprID = [Forms]![Recover]![OperatorID]

End Function

Public Function GetID()

GetID = [Forms]![Recover]!

• 
  
  End Function
  
  Make your query as :
  
  UPDATE [DAILY OUTAGE TICKETS] SET [DAILY OUTAGE TICKETS].MatchID = GetMatchID(), [DAILY OUTAGE TICKETS].RecoveredOprID = GetRecoveredOprID(), [DAILY OUTAGE TICKETS].RecoveryType = "Recovered", [DAILY OUTAGE TICKETS].RecoveryDate = Date()
  WHERE ((([DAILY OUTAGE TICKETS].RecoveryType)="Research Required&quot AND (([DAILY OUTAGE TICKETS].ID)=GetID()));
  
  Sorry, in my test I used only one form (Recover),
  but it should work with 2 or more forms.
  
  Try it.

 

[willir]

Randall

Thanks for the star. I hope the code worked.

Something not discussed, but something to consider is using spaces when nameing variables. This type of thing can cause problems.

Here is an article written by one of the top contributors to the Access forum...

http://www.tek-tips.com/faqs.cfm?spid=700&sfid=2190

 

[willir]

Randall

You can have a query reference another query. I have seen this approach to be fairly typical when working with large queries.

 

[GNIKOL]

hwkranger
When you use a form reference as criteria inside the code, must put <<“ &>> at the begin and at then end.
Also double quotation marks at the string, for example:
[DAILY OUTAGE TICKETS].RecoveryType = “&quot;Recovered”&quot;
If you use the expression << qd.Execute>> and the querydef has parameters it will not run like that.
You must give values to the parametrs.

For your example query:
Set qd = db.QueryDefs(&quot;Recovery Step 1&quot
qd![[Forms]![Recover]!

• ] = Me.
  • 
    ‘do the same with every other parameter
    ‘If the query is a Maketabe query, don’t forget to delete the table before execute the query
    Then qd.Execute
    
    Your SQL will be like this:
    
    UPDATE [DAILY OUTAGE TICKETS] SET [DAILY OUTAGE TICKETS].MatchID = “ & [Forms]![Recover]![ID] & “, [DAILY OUTAGE TICKETS].RecoveredOprID = “ & [Forms]![Operator]![OperatorID] & “, [DAILY OUTAGE TICKETS].RecoveryType = “&quot;Recovered”&quot;, [DAILY OUTAGE TICKETS].RecoveryDate = Date()
    WHERE ((([DAILY OUTAGE TICKETS].RecoveryType)=&quot;Research Required&quot AND (([DAILY OUTAGE TICKETS].ID)=” & [Forms]![Recover]!
    • & “));
      
      Good luck

 

[Eupher]

Okay, I want to make sure I understand this. When does the 255 character limit apply? I just tested this in Access XP. I used a SQL statement that is 440 characters in length. First I did a debug.print on just the SQL string variable (I printed both the length of the string with Len() and the entire string), then assigned the string to a NewQueryDef object, then printed the lenth of the querydef's SQL and the entire querydef.SQL string. In neither case was the SQL truncated (one version gave me a length of 440, the other was 444, but I attribute that discrepancy to line breaks which Access inserted into the querydef.SQL). This is a SELECT query statement, not an UPDATE or APPEND, so I wasn't able to test it with the .Execute method.

I just want to make sure I understand the when and why of this limitation so I know how to avoid it and/or deal with it should it occur. Thanks for any insight.

Ken S.

 

[hwkranger]

This issue I had with Access 97, I'm not sure about XP - I use 2k and 97 (depending on which department and who made the database)

I do mainly repairs and maintance on db's created by others - The issue that I had and never solved was that it was truncating an UPDATE query to 255 characters

There's no easy solution I found. I wanted to execute my queries from a saved query definition (which is faster). but it didn't work out the way I wanted

Randall Vollen
National City Bank Corp.

Just because you have an answer - doesn't mean it's the best answer.