table update (1 Viewer)

InFlight

User
Local time
Tomorrow, 05:58
Joined
Jun 11, 2015
Messages
130
I have 2 Tables Sections_New and Sections. Both have the same fields and data type.
I am trying to copy the data from the Judge field from Sections into Sections_New using the following code
the error is cannot find input table Sections_New.

the table Sections is a linked table and Sections_New is in the current DB
Code:
    Dim strsql As String
    strsql = "UPDATE Sections_New INNER JOIN Sections ON Sections_New.SectionID = Sections.SectionID SET Sections_New.Judge = [Sections]![Judge];"
    db.Execute strsql


if i make a query using the code it works fine
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:58
Joined
Oct 29, 2018
Messages
21,358
Hi. If the query works, what happens if you db.Execute the query?
 

InFlight

User
Local time
Tomorrow, 05:58
Joined
Jun 11, 2015
Messages
130
I haven't done that before. I will try
 

InFlight

User
Local time
Tomorrow, 05:58
Joined
Jun 11, 2015
Messages
130
This Works
Code:
    DoCmd.SetWarnings False:
    DoCmd.OpenQuery "qryKeepJudges"
    DoCmd.SetWarnings True
But i am trying to learn how to do it with db.execute
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 00:58
Joined
May 7, 2009
Messages
19,169
or use querydefs:

currentdb.Querydefs("qryKeepJudges").Execute
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:58
Joined
Oct 29, 2018
Messages
21,358
This Works
Code:
    DoCmd.SetWarnings False:
    DoCmd.OpenQuery "qryKeepJudges"
    DoCmd.SetWarnings True
But i am trying to learn how to do it with db.execute

Hi. Glad to hear you got it to work one way. You at least have one way to get the job done. I can’t tell you how to fix the original code without being able to run it myself.
 

InFlight

User
Local time
Tomorrow, 05:58
Joined
Jun 11, 2015
Messages
130
Yes this works
Code:
CurrentDb.QueryDefs("qryKeepJudges").Execute
But how do i do it with the code
Code:
strsql = "UPDATE Sections_New INNER JOIN Sections ON Sections_New.SectionID = Sections.SectionID SET Sections_New.Judge = [Sections]![Judge];"
db.Execute strsql
 

InFlight

User
Local time
Tomorrow, 05:58
Joined
Jun 11, 2015
Messages
130
This code won't work ether. Cannot find input table
Code:
    db.Execute "INSERT INTO Sections SELECT Sections_New.* FROM Sections_New;"
is it because Sections_new is in the current DB and sections is a linked table
what code should i use to tell the difference between the 2 db's and table's
 

Cronk

Registered User.
Local time
Tomorrow, 03:58
Joined
Jul 4, 2013
Messages
2,770
Have you set db to a database object?

Code:
    Dim strsql As String
    set db = currentdb
    strsql = "UPDATE Sections_New INNER JOIN Sections ON Sections_New.SectionID = Sections.SectionID SET Sections_New.Judge = [Sections]![Judge];"
    db.Execute strsql
or more simply

Code:
    Dim strsql As String
    strsql = "UPDATE Sections_New INNER JOIN Sections ON Sections_New.SectionID = Sections.SectionID SET Sections_New.Judge = [Sections]![Judge];"
    currentdb.Execute strsql
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:58
Joined
Feb 28, 2001
Messages
26,999
Just as a style pointer: If you can use Sections_New.SectionID = Sections.SectionID then you can save a small amount of typing (and maintain a consistent style) using Sections_New.Judge = Sections.Judge

However, this JOIN implies that your section ID already exists in the _New table to match what is in the "old" table. When you say "This works" my question is, does it?

Code:
    DoCmd.SetWarnings False:
    DoCmd.OpenQuery "qryKeepJudges"
    DoCmd.SetWarnings True

What happens if you drop the line DoCmd.SetWarning False? Does it still work? If not, what errors do you get? HINT: When debugging something new, turning off warnings is one of the LAST things you do before putting it into production mode.
 

InFlight

User
Local time
Tomorrow, 05:58
Joined
Jun 11, 2015
Messages
130
yes SectionID does exist in both tables
Both tables are the same except some of the data is different. I need to keep the judges names before the data is removed from [Sections] and replaced with the data from [Section_new]
 

Users who are viewing this thread

Top Bottom