Access ignoring some lines of code ??

[StephanieD]

I have a very simple module that runs a bunch of SQL queries to convert a database over to a new set of "course codes". The module is simple but long.

There are a little more than 600 lines of code. A few sample lines are shown below. "Most" of the lines run with no problem but some at the end do not run at all, as evidenced by the fact that some codes do not get changed over. But if I take the lines of code at the end and paste them at the top and run it that way, those lines or code run fine.

I tried to combine several modules into a one long module that was several thousand lines long, but Access complained that that it was too long. Fair enough. It is no longer complaining about that, so I assume that 600 lines is not too long. Maybe it is still too long and Access isn't aware that it can't run a module that long??

Any ideas?


Public Sub FUBAR()

DoCmd.SetWarnings False

DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI039' WHERE [COURSE_ID]= 'CPS1';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI117' WHERE [COURSE_ID]= 'ISL1';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI290' WHERE [COURSE_ID]= 'LS14';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI390' WHERE [COURSE_ID]= 'LS15';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI266' WHERE [COURSE_ID]= 'LS16';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI260' WHERE [COURSE_ID]= 'LS17';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI389' WHERE [COURSE_ID]= 'LS18';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI278' WHERE [COURSE_ID]= 'LS19';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI358' WHERE [COURSE_ID]= 'LS20';"
DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = 'JDI388' WHERE [COURSE_ID]= 'LS21';"

' 600 more lines of code similar to these....


DoCmd.SetWarnings True

End Sub

 

[gemma-the-husky]

a slightly different tack

this sounds like its a oneoff job

so where are you getting the new values from? if they are in a table, you can do all this with one simple query.

and if not, its as easy to do it all manually, than write and edit 600 sql statements

 

[DCrake]

What you need is a translation table. Whereby one table contains both the old and the new course ids

Then you can run a Do While Loop on the table

Do Until Rs.EOF

DoCmd.RunSQL "UPDATE Course SET [COURSE_ID] = '" & Rs(0) & "' WHERE [COURSE_ID]= '" & rs(1) & "';"

Rs.MoveNext
Loop

 

[Rabbie]

Have you tried making sure that Docmd.Setwarnings is set to true before you run the lines that aren't working. It may help you diagnose if you can see any error messages that occur.

 

[StephanieD]

Yeah this is a oneoff job.

The new and old codes exist in an Excel spreadsheet. Some other people in my department used Excel to work through the process of renaming the codes. The example I posted was the simplest of 20 modules - the rest of the data is a little messier - like job titles and course names (e.g. "Dockstocker Orientation" becomes "Electric Forklift Annual Review").

Since this data was in Excel already, I simply created a formula to concatenate several strings to create lines of code like DoCmd.RunSQL "UPDATE Course..... " etc.

I will try toggling the Setwarnings setting to see if it is actually telling me what the problem is. If that doesn't yield useful information I may have to create some tables as suggested. But I REALLY hope I can simply cut-n-paste these lines of code out of Excel right into Access. >

 

[StephanieD]

Found the problem. Turning the warnings back on revealed several Key violations. Course_ID is a Key field. Several old courses were being "combined" into one new course, so there were duplicates. This is a problem I didn't anticipate and should have mentioned to the folks creating the new course codes.

Thanks guys............

 

[gemma-the-husky]

the easiest way to do this is

a) create a new column in your table - oldcode
b) do an update query to copy the key values into the oldcode coliumn
c) import the newtable
d) join the newtable to the orginal table on the oldcode values, and then update the keyvalues to be the new values specified in the table

that way you can also find

a) duplicates in the new table
b) stuff in the old table, not in the new table
c) stuff in the new table, not in the old table

so that you can clean the data before you use it

 

[StephanieD]

I will give it a try. I will probably be back.