Solved

SQL update  not writing the record tofile

Posted on 2013-01-17
10
298 Views
Last Modified: 2013-01-17
below is the statement. I did a debug print and it forms the record but is not writing it to the table below is the debug print. The records exist in the table to match on

UPDATE StateAccessFile2 SET StateAccessFile2.[E2EG6FemaleCount12thGrade] = '4', StateAccessFile2.[E2EG6MaleCount12thGrade] = '11', StateAccessFile2.[E2EG6FemaleCountMil] = '0', StateAccessFile2.[E2EG6MaleCountMil] = '0', StateAccessFile2.[E2EG6FemaleCountCol] = '0', StateAccessFile2.[E2EG6MaleCountCol] = '0', StateAccessFile2.[E2EG6FemaleCountColUnRel] = '0', StateAccessFile2.[E2EG6MaleCountColUnRel] = '0', StateAccessFile2.[E2EG6FemaleCountEmpRel] = '0', StateAccessFile2.[E2EG6MaleCountEmpRel] = '0', StateAccessFile2.[E2EG6FemaleCountEmpUnRel] = '0', StateAccessFile2.[E2EG6MaleCountEmpUnRel] = '0', StateAccessFile2.[E2EG6FemaleCountNot] = '0', StateAccessFile2.[E2EG6MaleCountNot] = '0', StateAccessFile2.[E2EG6FemaleCountTot] = '0', StateAccessFile2.[E2EG6MaleCountTot] = '0', StateAccessFile2.[techprepyn]= 'N', StateAccessFile2.[NonTradCrs]= 'N' WHERE CourseCode='4630' and CType='Concentrator' AND StateAccessFile2.[AdultYN]='N'Type='Concentrator' AND StateAccessFile2.[AdultYN]='N'

Here is the update statement

 SQlUpdQry2 = "UPDATE StateAccessFile" & PTypeLpCnt & " SET " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCount12thGrade] = '" & E2EGFemaleCount12thGradeStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCount12thGrade] = '" & E2EGMaleCount12thGradeStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountMil] = '" & E2EGFemaleCountMilStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountMil] = '" & E2EGMaleCountMilStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountCol] = '" & E2EGFemaleCountColStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountCol] = '" & E2EGMaleCountColStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountColUnRel] = '" & E2EGFemaleCountColUnrelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountColUnRel] = '" & E2EGMaleCountColUnrelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountEmpRel] = '" & E2EGFemaleCountEmpRelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountEmpRel] = '" & E2EGMaleCountEmpRelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountEmpUnRel] = '" & E2EGFemaleCountEmpUnRelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountEmpUnRel] = '" & E2EGMaleCountEmpUnRelStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountNot] = '" & E2EGFemaleCountNotStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountNot] = '" & E2EGMaleCountNotStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "FemaleCountTot] = '" & E2EGFemaleCountTotStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[E2EG" & Trim(Str(LpCnt)) & "MaleCountTot] = '" & E2EGMaleCountTotStr & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[techprepyn]= '" & TechPrepVal & "', " & _
        "StateAccessFile" & PTypeLpCnt & ".[NonTradCrs]= '" & NonTradVal & "' " & _
        "WHERE CourseCode='" & CBEDSCrsNum & "' and CType='" & CTypeArr(CTypeLpCnt) & "' AND StateAccessFile" & PTypeLpCnt & ".[AdultYN]='" & ProgTypeVal & "'"
0
Comment
Question by:needhelpfast569
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 34

Expert Comment

by:Paul MacDonald
ID: 38787607
It seems likely your WHERE condition isn't being met.  If you SELECT FROM StateAccessFile WHERE CourseCode='" & CBEDSCrsNum & "' and CType='" & CTypeArr(CTypeLpCnt) & "' AND StateAccessFile" & PTypeLpCnt & ".[AdultYN]='" & ProgTypeVal & "'" is anything returned?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787640
<<it forms the record but is not writing it to the table>>

What is the rest of your code?

Are you actually executing your SQL?

After the big  SQlUpdQry2 = "UPDATE... etc", you need the following:

CurrentDB.Execute SQlUpdQry2, dbFailOnError

Open in new window

0
 

Author Comment

by:needhelpfast569
ID: 38787828
mbizup, thanks you are right it was the program was falling thru and not doing the excute.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 61

Expert Comment

by:mbizup
ID: 38787911
Did that help you resolve it?  If so, please remember to close the question.

Or post back if you need additional help.
0
 

Author Comment

by:needhelpfast569
ID: 38787918
doing the excute but still not updating the record????
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38787947
Did you add dbFailOnError to your Execute statement?

CurrentDB.Execute SQlUpdQry2, dbFailOnError

If so, do you get any specific error messages?

Try copy/pasting your Debug.Print output into your query builder, and try running this directly from the query builder:

UPDATE StateAccessFile2 SET StateAccessFile2.[E2EG6FemaleCount12thGrade] = '4', StateAccessFile2.[E2EG6MaleCount12thGrade] = '11', StateAccessFile2.[E2EG6FemaleCountMil] = '0', StateAccessFile2.[E2EG6MaleCountMil] = '0', StateAccessFile2.[E2EG6FemaleCountCol] = '0', StateAccessFile2.[E2EG6MaleCountCol] = '0', StateAccessFile2.[E2EG6FemaleCountColUnRel] = '0', StateAccessFile2.[E2EG6MaleCountColUnRel] = '0', StateAccessFile2.[E2EG6FemaleCountEmpRel] = '0', StateAccessFile2.[E2EG6MaleCountEmpRel] = '0', StateAccessFile2.[E2EG6FemaleCountEmpUnRel] = '0', StateAccessFile2.[E2EG6MaleCountEmpUnRel] = '0', StateAccessFile2.[E2EG6FemaleCountNot] = '0', StateAccessFile2.[E2EG6MaleCountNot] = '0', StateAccessFile2.[E2EG6FemaleCountTot] = '0', StateAccessFile2.[E2EG6MaleCountTot] = '0', StateAccessFile2.[techprepyn]= 'N', StateAccessFile2.[NonTradCrs]= 'N' WHERE CourseCode='4630' and CType='Concentrator' AND StateAccessFile2.[AdultYN]='N'Type='Concentrator' AND StateAccessFile2.[AdultYN]='N'

Open in new window

0
 

Author Comment

by:needhelpfast569
ID: 38788004
added the dbfailonerror to the stmt and receiver a 128 error code
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38788058
1.  What was the exact text of the error message?

2.  Also try running the text from debug.print directly from the query builder (see comment http:#a38787947) and let us know if it updates correctly or fails.
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38788100
dbFailOnError is a Constant whose value is 128.  If you got that from hovering the mouse over dbfailOnError in the debugger, that is to be expected.

dbFailOnError makes your execute statement return a specific error number and message if the query itself fails. Without dbFailOnError, your code will continue to execute normally without any warning messages - even if your query has failed (so dbFailOnError mprovides you with important debugging info if your query has problems).

So does the query execute without error, and just not update anything?

Or is it not executing because of errors in the query?

(The reason for adding dbFailOnError is to help you make that distinction)
0
 

Author Closing Comment

by:needhelpfast569
ID: 38788152
LOADING THE STMT INTO QUERY WORKED GREAT THE PROBLEM WAS THAT i WAS MISSING A VALUE SO THE UPDATE COULD NOT FIND A MATCH. THANKS
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

626 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question