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
Solved

SQL update  not writing the record tofile

Posted on 2013-01-17
10
295 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

808 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