Importing new entries only from .csv file

[George Yeh]

Hi There!

Currently I am importing a .csv file that eventually grows over
time. I have setup my table with an unique id field that is the
primary key and indexed. I have also indicated in the import
specifiaction that this field is indexed and that no duplicates
should be accepted.

My problem right now is that each time I import this file into
my database, it will overwrite what already exists and I would
have to go back and redo all my corrections.

Here is the script that runs when we click to download the
information:

Private Sub DownloadWaiver_Click()
On Error GoTo Err_DownloadWaiver_Click

Dim strFileName As String
Dim strPathExample As String
Dim strImportSpec As String
Dim strTableName As String
Dim strQueryName1 As String
Dim strQueryName2 As String

strPathExample = "Save File as i:\arfeedtobedone\waiver_data.csv"
strImportSpec = "Waiver Import Specification"
strTableName = "tblWaiver"

strFileName = "i:\arfeedtobedone\waiver_data.csv"
strQueryName1 = "01_updateqryWaiverSSN"
strQueryName2 = "02_updateqrytblWaiverSystemPIDM"
' Set warnings to off
DoCmd.SetWarnings False

' Import Health Waiver file
DoCmd.TransferText acImportDelim, strImportSpec, strTableName, strFileName,
False, ""

' Change the Health Waiver file info into a format to feed Banner
DoCmd.OpenQuery strQueryName1, acNormal, acEdit
DoCmd.OpenQuery strQueryName2, acNormal, acEdit

' Reset warnings to on
DoCmd.SetWarnings True

MsgBox "Health waivers have been added to tblWaiver."

Exit_DownloadWaiver_Click:
Exit Sub

Err_DownloadWaiver_Click:
MsgBox Error$
Resume Exit_DownloadWaiver_Click

End Sub

Is there anyway to modify this so that Access will not import
the entries that already exist?

Thanks for your help!

--
Regards,

George Yeh

[Terry Kreft]

Why not link to the file instead of importing it. You can then use a query
to transfer the new records to your working table.
--
Terry Kreft
MVP Microsoft Access
"George Yeh" <ge**@lneohsipgahm.edu> wrote in message
news:cb**********@fidoii.CC.Lehigh.EDU...

Hi There!

Currently I am importing a .csv file that eventually grows over
time. I have setup my table with an unique id field that is the
primary key and indexed. I have also indicated in the import
specifiaction that this field is indexed and that no duplicates
should be accepted.

My problem right now is that each time I import this file into
my database, it will overwrite what already exists and I would
have to go back and redo all my corrections.

Here is the script that runs when we click to download the
information:

Private Sub DownloadWaiver_Click()
On Error GoTo Err_DownloadWaiver_Click

Dim strFileName As String
Dim strPathExample As String
Dim strImportSpec As String
Dim strTableName As String
Dim strQueryName1 As String
Dim strQueryName2 As String

strPathExample = "Save File as i:\arfeedtobedone\waiver_data.csv"
strImportSpec = "Waiver Import Specification"
strTableName = "tblWaiver"

strFileName = "i:\arfeedtobedone\waiver_data.csv"
strQueryName1 = "01_updateqryWaiverSSN"
strQueryName2 = "02_updateqrytblWaiverSystemPIDM"
' Set warnings to off
DoCmd.SetWarnings False

' Import Health Waiver file
DoCmd.TransferText acImportDelim, strImportSpec, strTableName, strFileName, False, ""

' Change the Health Waiver file info into a format to feed Banner
DoCmd.OpenQuery strQueryName1, acNormal, acEdit
DoCmd.OpenQuery strQueryName2, acNormal, acEdit

' Reset warnings to on
DoCmd.SetWarnings True

MsgBox "Health waivers have been added to tblWaiver."

Exit_DownloadWaiver_Click:
Exit Sub

Err_DownloadWaiver_Click:
MsgBox Error$
Resume Exit_DownloadWaiver_Click

End Sub

Is there anyway to modify this so that Access will not import
the entries that already exist?

Thanks for your help!

--
Regards,

George Yeh

[George Yeh]

Hi Terry!

Just had someone else suggest this also and I think it will work.

Thanks!!

George

"Terry Kreft" <te*********@mps.co.uk> wrote in message
news:R8********************@karoo.co.uk...

Why not link to the file instead of importing it. You can then use a query to transfer the new records to your working table.
--
Terry Kreft
MVP Microsoft Access
"George Yeh" <ge**@lneohsipgahm.edu> wrote in message
news:cb**********@fidoii.CC.Lehigh.EDU...

Hi There!

Currently I am importing a .csv file that eventually grows over
time. I have setup my table with an unique id field that is the
primary key and indexed. I have also indicated in the import
specifiaction that this field is indexed and that no duplicates
should be accepted.

My problem right now is that each time I import this file into
my database, it will overwrite what already exists and I would
have to go back and redo all my corrections.

Here is the script that runs when we click to download the
information:

Private Sub DownloadWaiver_Click()
On Error GoTo Err_DownloadWaiver_Click

Dim strFileName As String
Dim strPathExample As String
Dim strImportSpec As String
Dim strTableName As String
Dim strQueryName1 As String
Dim strQueryName2 As String

strPathExample = "Save File as i:\arfeedtobedone\waiver_data.csv"
strImportSpec = "Waiver Import Specification"
strTableName = "tblWaiver"

strFileName = "i:\arfeedtobedone\waiver_data.csv"
strQueryName1 = "01_updateqryWaiverSSN"
strQueryName2 = "02_updateqrytblWaiverSystemPIDM"
' Set warnings to off
DoCmd.SetWarnings False

' Import Health Waiver file
DoCmd.TransferText acImportDelim, strImportSpec, strTableName,

strFileName,

False, ""

' Change the Health Waiver file info into a format to feed Banner
DoCmd.OpenQuery strQueryName1, acNormal, acEdit
DoCmd.OpenQuery strQueryName2, acNormal, acEdit

' Reset warnings to on
DoCmd.SetWarnings True

MsgBox "Health waivers have been added to tblWaiver."

Exit_DownloadWaiver_Click:
Exit Sub

Err_DownloadWaiver_Click:
MsgBox Error$
Resume Exit_DownloadWaiver_Click

End Sub

Is there anyway to modify this so that Access will not import
the entries that already exist?

Thanks for your help!

--
Regards,

George Yeh

[Bernard Peek]

In message <cb**********@fidoii.CC.Lehigh.EDU>, George Yeh
<ge**@lneohsipgahm.edu> writes

Hi There!

Currently I am importing a .csv file that eventually grows over
time. I have setup my table with an unique id field that is the
primary key and indexed. I have also indicated in the import
specifiaction that this field is indexed and that no duplicates
should be accepted.

My problem right now is that each time I import this file into
my database, it will overwrite what already exists and I would
have to go back and redo all my corrections.

The problem looks to be a fundamental one in that you are using a
surrogate key in your table in place of any natural key in the data
itself. Theoretically the best solution is to abandon your existing
unique ID field and use a natural key from the data as your primary key.
If you do that duplicates will always be rejected, so only new entries
will be added.

I suggest that you reconsider your database structure to try to avoid
using a surrogate key if it isn't necessary.

The suggestion that has already been made, creating a linked table, will
only work if you have a suitable key in the data. This is because that
is the field (or combination of fields) that you will have to use to
join the existing table with the linked table. Since you need a key
anyway, consider using it as the primary key on your tables. There are
often good reasons why this impractical but it is always worth
considering.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.