Copy a value from a field of 1 table into a field of another table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,

I need to code a value in a field of 1 table (Target) from a field of
another table (Source), from the same mdb.
Additionally, this coding has to be done on the records that has the ID
values greater than a known value from the table into which the value is to
be coded.

I assume that there are 3 tasks in this operation:
1. Join the Source table to Target table temporarily.
2. Select the rows that has ID values greater than a given value.
3. Code the value from the field of Source table into the Target table.
4. Remove the Join.

Could any one help me in performing these tasks programatically with some
hints / method to follow / sample codes.

Any help much appreciated.

Thanks in advance,

Raghavendra
 
i think you can try to do this using Update query (assuming that with "code"
you mean "copy")
 
Thanks for the reply Alex.
Yes, I meant copy using the word 'code'.

Could you little bit elaborate on how to temporarily join 2 tables and on
the basis of a field from each table?

Thanks,

Raghavendra
 
create a new query, add both table there, join them, add condition that one
field greater then other as you described, run it to check that it returns
what you need. now change it type to update query using menu, and set there
what field you what update to what
 
Alex,

I could manage only the following, which does not work properly:
(I am new to VB environment)

Private Sub join_calc(mainFC, annoFC, GDB, StartOID)
'mainFC = "Hydrant"
'annoFC = "Hydrant_Number_Anno"
'GDB = "E:\Calam\data\convert\APP-15\MontereyGDB.mdb"
'StartOID = 1

Dim DBFile As database
Dim mainRC, annoRC, newAnnoRC As Recordset

Set DBFile = OpenDatabase(GDB)
Set mainRC = DBFile.OpenRecordset(mainFC, dbOpenDynaset) 'open main fc
Set annoRC = DBFile.OpenRecordset(annoFC, dbOpenDynaset) 'open anno fc

Dim strUpdate, strSelAnno As String
'to select records that has DUMMY >= the given value
selanno = "SELECT * FROM " & annoFC & " WHERE " & "DUMMY >= " & StartOID

'copy a value from newannoFC's FeatureID field from FID field of mainFC
table.

strUpdate = "UPDATE " & newannoFC & " INNER JOIN " & newannoFC & " ON " &
newannoFC & ".DUMMY = " & mainFC & ".FID SET " & newannoFC & ".FEATUREID = ["
& mainFC & "].[" & "OBJECTID];"

'*********please look here - 'unable to set the newAnnoRC using the
'selanno' SQL statement
'*********error - data source name too long
'Set newAnnoRC = New Recordset
'newAnnoRC.Open selanno, GDB, adOpenDynamic

'*********error - syntax error in update statement
DBEngine(0)(0).execute strUpdate, dbFailOnError
mainFC.Close
annoFC.Close

DBFile.Close

End Sub

Could you please help in correcting this code.

Thanks,

Raghavendra
 
So, as far as i understand this, and path to DB with both tables to pass
with GDB then is should look like this:

Private Sub join_calc(mainFC, annoFC, GDB, StartOID)

Dim DBFile As database
Dim mainRC, annoRC, newAnnoRC As DAO.Recordset

Set DBFile = OpenDatabase(GDB)

Dim strUpdate, strSelAnno As String
selanno = "SELECT * FROM " & annoFC & " WHERE " & "DUMMY >= " & StartOID


strUpdate = "UPDATE " & newannoFC & " INNER JOIN " & newannoFC & " ON " &
newannoFC & ".DUMMY = " & mainFC & ".FID SET " & newannoFC & ".FEATUREID =
["
& mainFC & "].[" & "OBJECTID];"

DBFile.execute strUpdate, dbFailOnError

DBFile.Close

End Sub

if error - then try to copy value of strUpdate into new query SQL and try to
run it

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



Raghavendra said:
Alex,

I could manage only the following, which does not work properly:
(I am new to VB environment)

Private Sub join_calc(mainFC, annoFC, GDB, StartOID)
'mainFC = "Hydrant"
'annoFC = "Hydrant_Number_Anno"
'GDB = "E:\Calam\data\convert\APP-15\MontereyGDB.mdb"
'StartOID = 1

Dim DBFile As database
Dim mainRC, annoRC, newAnnoRC As Recordset

Set DBFile = OpenDatabase(GDB)
Set mainRC = DBFile.OpenRecordset(mainFC, dbOpenDynaset) 'open main fc
Set annoRC = DBFile.OpenRecordset(annoFC, dbOpenDynaset) 'open anno fc

Dim strUpdate, strSelAnno As String
'to select records that has DUMMY >= the given value
selanno = "SELECT * FROM " & annoFC & " WHERE " & "DUMMY >= " & StartOID

'copy a value from newannoFC's FeatureID field from FID field of mainFC
table.

strUpdate = "UPDATE " & newannoFC & " INNER JOIN " & newannoFC & " ON " &
newannoFC & ".DUMMY = " & mainFC & ".FID SET " & newannoFC & ".FEATUREID =
["
& mainFC & "].[" & "OBJECTID];"

'*********please look here - 'unable to set the newAnnoRC using the
'selanno' SQL statement
'*********error - data source name too long
'Set newAnnoRC = New Recordset
'newAnnoRC.Open selanno, GDB, adOpenDynamic

'*********error - syntax error in update statement
DBEngine(0)(0).execute strUpdate, dbFailOnError
mainFC.Close
annoFC.Close

DBFile.Close

End Sub

Could you please help in correcting this code.

Thanks,

Raghavendra


Alex Dybenko said:
create a new query, add both table there, join them, add condition that
one
field greater then other as you described, run it to check that it
returns
what you need. now change it type to update query using menu, and set
there
what field you what update to what
Click to expand...
Click to expand...
 
Thanks for the help Alex.
Based on your guidelines, I could modify my code that works well now.

The following codes are for your information. - Raghavendra

Private Sub join_calc(mainFC, annoFC, GDB, StartOID)

Dim dbfile As database
Set dbfile = OpenDatabase(GDB)
Dim strupdate As String

strupdate = "UPDATE " & annoFC & " INNER JOIN " & mainFC & " ON " & annoFC &
".DUMMY = " & _
mainFC & ".FID SET " & annoFC & ".FEATUREID = " & mainFC &
".OBJECTID WHERE " & _
"EXISTS (SELECT * FROM " & annoFC & " WHERE " & annoFC &
".OBJECTID >= " & StartOID & ");"

dbfile.execute strupdate, dbFailOnError
dbfile.Close
End Sub


Alex Dybenko said:
So, as far as i understand this, and path to DB with both tables to pass
with GDB then is should look like this:

Private Sub join_calc(mainFC, annoFC, GDB, StartOID)

Dim DBFile As database
Dim mainRC, annoRC, newAnnoRC As DAO.Recordset

Set DBFile = OpenDatabase(GDB)

Dim strUpdate, strSelAnno As String
selanno = "SELECT * FROM " & annoFC & " WHERE " & "DUMMY >= " & StartOID


strUpdate = "UPDATE " & newannoFC & " INNER JOIN " & newannoFC & " ON " &
newannoFC & ".DUMMY = " & mainFC & ".FID SET " & newannoFC & ".FEATUREID =
["
& mainFC & "].[" & "OBJECTID];"

DBFile.execute strUpdate, dbFailOnError

DBFile.Close

End Sub

if error - then try to copy value of strUpdate into new query SQL and try to
run it

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com



Raghavendra said:
Alex,

I could manage only the following, which does not work properly:
(I am new to VB environment)

Private Sub join_calc(mainFC, annoFC, GDB, StartOID)
'mainFC = "Hydrant"
'annoFC = "Hydrant_Number_Anno"
'GDB = "E:\Calam\data\convert\APP-15\MontereyGDB.mdb"
'StartOID = 1

Dim DBFile As database
Dim mainRC, annoRC, newAnnoRC As Recordset

Set DBFile = OpenDatabase(GDB)
Set mainRC = DBFile.OpenRecordset(mainFC, dbOpenDynaset) 'open main fc
Set annoRC = DBFile.OpenRecordset(annoFC, dbOpenDynaset) 'open anno fc

Dim strUpdate, strSelAnno As String
'to select records that has DUMMY >= the given value
selanno = "SELECT * FROM " & annoFC & " WHERE " & "DUMMY >= " & StartOID

'copy a value from newannoFC's FeatureID field from FID field of mainFC
table.

strUpdate = "UPDATE " & newannoFC & " INNER JOIN " & newannoFC & " ON " &
newannoFC & ".DUMMY = " & mainFC & ".FID SET " & newannoFC & ".FEATUREID =
["
& mainFC & "].[" & "OBJECTID];"

'*********please look here - 'unable to set the newAnnoRC using the
'selanno' SQL statement
'*********error - data source name too long
'Set newAnnoRC = New Recordset
'newAnnoRC.Open selanno, GDB, adOpenDynamic

'*********error - syntax error in update statement
DBEngine(0)(0).execute strUpdate, dbFailOnError
mainFC.Close
annoFC.Close

DBFile.Close

End Sub

Could you please help in correcting this code.

Thanks,

Raghavendra


Alex Dybenko said:
create a new query, add both table there, join them, add condition that
one
field greater then other as you described, run it to check that it
returns
what you need. now change it type to update query using menu, and set
there
what field you what update to what

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Thanks for the reply Alex.
Yes, I meant copy using the word 'code'.

Could you little bit elaborate on how to temporarily join 2 tables and
on
the basis of a field from each table?

Thanks,

Raghavendra

:

i think you can try to do this using Update query (assuming that with
"code"
you mean "copy")

--
Alex Dybenko (MVP)
http://Alex.Dybenko.com
http://www.PointLtd.com


Hi all,

I need to code a value in a field of 1 table (Target) from a field
of
another table (Source), from the same mdb.
Additionally, this coding has to be done on the records that has the
ID
values greater than a known value from the table into which the
value
is
to
be coded.

I assume that there are 3 tasks in this operation:
1. Join the Source table to Target table temporarily.
2. Select the rows that has ID values greater than a given value.
3. Code the value from the field of Source table into the Target
table.
4. Remove the Join.

Could any one help me in performing these tasks programatically with
some
hints / method to follow / sample codes.

Any help much appreciated.

Thanks in advance,

Raghavendra
Click to expand...
Click to expand...
Click to expand...
 
Back
Top