Solved

SQL update  not writing the record tofile

Posted on 2013-01-17
10
296 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.

762 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