Appending and Updating Data through Code

[Robert Neville]

Some people may be familiar with my import quest, so this question may
sound familiar. After months of developing my parsing routine, now I
want to programmatically append or update the import data into my
table. Queries do not allow the extent of control that queries and
code provide. Plus, append queries unnecessarily increment the auto
number field in my scenario. So the main objective involves append
and update import data from a repository table into the main table
while adding information whether the data is new or updated (and what
data was updated).

My research and reading has found several approaches utilizing forms;
yet my situation does not use any form. The import data goes from a
table repository to the main table; executed through code (maybe a
command button on a form in the future for execution).

Table Repository -> tblImportProdData
Main Table -> tblProj

My initial ideas involve using the Do While Loop to move through
tblImportProdData. Then check if the ProjName exists in the main
table; if it does not exist add the new record; if it exist then
update it. The second objective would involve tracking modified data;
yet this directive may be too ambition at this juncture.

Let me know if you have additional insights that may lead me into the
right direction. Here's my very crude initial code.

Private Sub UpdateProjects()

Dim dbs As Database
Dim rst As Recordset
Dim rstImport As Recordset

Const cstrProc As String = "UpdateProjects"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblProj", dbOpenDynaset)
Set rstImport = dbs.OpenRecordset("tblImportProdData",
dbOpenDynaset)


Do While Not rstImport.EOF

rst.FindFirst "ProjName =" & rstImport!ProjName ' I am STUCK
here
'should I use Dlookup instead FindFirst

If rst.NoMatch Then
With rst
.AddNew
!ProjName = rstImport!ProjName
' various fields to update
!Update = "New"
.Update
End With

rst.Close

Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
DoCmd.Echo True
Else
With rst
.Edit
!ProjName = rstImport!ProjName
' various fields to update
!Update = "Updated"
.Update
End With

rst.Close

Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
DoCmd.Echo True
End If
Loop

End Sub

 

[Bob Quintal]

My research and reading has found several approaches utilizing forms;
yet my situation does not use any form. The import data goes from a
table repository to the main table; executed through code (maybe a
command button on a form in the future for execution).

That is tghe recommended way of executing a procedure, its much better
than having the user open the debug window and typing the command.

Table Repository -> tblImportProdData
Main Table -> tblProj

My initial ideas involve using the Do While Loop to move through
tblImportProdData. Then check if the ProjName exists in the main
table; if it does not exist add the new record; if it exist then
update it. The second objective would involve tracking modified data;
yet this directive may be too ambition at this juncture.

Good approach.

Let me know if you have additional insights that may lead me into the
right direction. Here's my very crude initial code.

Private Sub UpdateProjects()

Dim dbs As Database
Dim rst As Recordset
Dim rstImport As Recordset

Const cstrProc As String = "UpdateProjects"

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblProj", dbOpenDynaset)
Set rstImport = dbs.OpenRecordset("tblImportProdData",
dbOpenDynaset)


Do While Not rstImport.EOF

rst.FindFirst "ProjName =" & rstImport!ProjName ' I am STUCK
here
'should I use Dlookup instead FindFirst

DLookUp will open another copy of the table and return a value, then
close the copy of the table. Your original rst will still be on the
first record, not where you want to be. Findfirst is the way to go,
except that "ProjName = """ & rstImport!ProjName & """" will work if
projname is text. Your way will work if projName is a number.

If rst.NoMatch Then
With rst
.AddNew
!ProjName = rstImport!ProjName
' various fields to update
!Update = "New"
.Update
End With

The code below doesn't belong here, it goes after the LOOP statement
You still have all the other records in rstimport to do

rst.Close

Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
DoCmd.Echo True

End of block to move to after loop statement.

Else
With rst
.Edit
!ProjName = rstImport!ProjName
' various fields to update
!Update = "Updated"
.Update
End With

From here to the endif goes after the LOOP statement, but since you
moved the block above down there, it just gets deleted.

rst.Close

Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
DoCmd.Echo True
End If
Loop

End Sub

Not a bad routine.

 

[Robert Neville]

Steve,

Thanks for your reply. You gave me some additional insights about
query creation. Your suggestions may help me in the near-term, yet my
stance still applies about maintaining more control over my data
imports. The reason behind this stance involves the inconsistent
nature of the data, which has been parsed and modified through regular
expression code. Second, I need to create a visual cue system that
would allow me to manually review the import data for inconsistent
information. These abnormalities could be as simple as mis-spellings;
or as complex as project shifting companies and talent pools; or even
changing project titles.

The approach should allow me to act on the import data in a timely and
effective manner. Plus, archive the updated data, so I do not lose
valuable information to inaccurate data in the import. I have been
building upon the process for a couple of years and still find
significant latitude for improvements.

Thanks for your assistance again.

 

[Robert Neville]

Bob,

Thanks for your insightful comments.

DLookUp will open another copy of the table and return a value, then
close the copy of the table. Your original rst will still be on the
first record, not where you want to be. Findfirst is the way to go,
except that "ProjName = """ & rstImport!ProjName & """" will work if
projname is text. Your way will work if projName is a number.

If rst.NoMatch Then
With rst
.AddNew
!ProjName = rstImport!ProjName
' various fields to update
!Update = "New"
.Update
End With

ProjName is text, so I'll change the FindFirst according to your
suggestion. I need to read up on FindFirst, DLookup, Seek, and ADO's
Find to fully understand their potential and pitfalls.

The code below doesn't belong here, it goes after the LOOP statement
You still have all the other records in rstimport to do
End of block to move to after loop statement.

Yeah, your suggestion makes sense. I don't even know why I placed it
here in the first place. Consider it moved.

Else
With rst
.Edit
!ProjName = rstImport!ProjName
' various fields to update
!Update = "Updated"
.Update
End With

Not a bad routine.

Thanks again. This first draft is still very crude and eventually will
incorporate some validation and archiving capabilities; yet I have
learned to take baby steps instead of asking complex question from the
get go. Allen Browne had some interesting comments in a previous post
about setting up a warning flag system, yet my database or approach is
nowhere near his complex system.