Import Fixed Text File into existing table

[tanya74]

Hi, I am very new to Access 2010 so I'm sorry if these questions seem a little simple.

I am wanting to update a table with data from a fixed length text file (programmatically). Some of the rows in the text file may be new, some old. As far as I can tell in my research... I have a few options

1. Use TransferText - Is there a way to may use TransferText to update existing rows or add new rows into the table?

2. Import somehow using a Scheme.ini file. I have just created an Import Spec but can I convert the Import Spec to a Schema.ini file? any hints on how to do this please? I think I will need these to loop thru my text file data and check the existence of row and do an update or an add.

3. Import using TransferText into a Staging table then compare rows somehow? I am leaning towards this option.

Any advice?

Thanks
Tanya

 

[bconner]

Option 3 seems like the easiest way "Import using TransferText into a Staging table then compare rows somehow? I am leaning towards this option."

Import using TransferText into a staging table and use an update query to update the Main table based on some parameter.

DoCmd.TransferText acImportDelim, "Import Spec Here ", "Table Name Here", "Path if File to be imported here"

 

[gemma-the-husky]

i would definitley go the suggested way - you can easily use this to validate the correctness of the imported table, before updating the main database with it - just in case there is a problen with it.

you are also likely to find that a flat fixed width file will actually affect more than 1 table in a normalised database - so you may need a series of processes to completely handle the import.

 

[tanya74]

Thank you all for your help. I have the PatientStaging table populated now but wish to now compare the two tables and run either updates or additions depending if the record already exists or not. I am a little confused about how to do this. Can someone help?

Do I create a loop or cursor to get each record ?
Once I have a record , do I use the DoCmd.FindRecord on the real Patient table using the primary key? If this record exists, should I then use DoCmd.RunSql to update the record? If the record doesnt exist, use DoCmd.RunSql to Insert the record?

Is this method the quickest way? The dataset to insert\update may be 20000 records. Should I run this in the background? Currently, I am planning on running in the foreground.

Sorry for all my questions. I am new to Access.

Thanks in advance.
Tanya

 

[gemma-the-husky]

use queries

use matched, or unmatched queries to find and compare existing records and/or new records

once you get the queries sorted, then you can automate the procedure and put a sequence of queries on a button click

this sort of logic


test things

if everything is ok then ask whether users wants to continue, and if so, load the new data.

if everything is not ok, then allow the user to amend, reimport etc.

 

[tanya74]

thanks for your reply. Sorry I'm a bit confused still. I think I have the logic right. Just not sure about the functions I should use. I have included my code below so far and some of the logic.

What is the difference between DBEngine(0)(0).Execute and DoCmd.RunSQL?

I am leaning towards DBEngine(0)(0).Execute as I can use a transaction. Is this the most efficient way?

If you could just give me some clues as to the functions that I should probably use. I think I can work out the rest.

Private Sub btnLoadPatients_Click()

    Dim strMsg As String
    
    DoCmd.TransferText acImportFixed, "PATIENTS_Spec_v3", "PatientStaging", "C:\Users\Tanya\Documents\PATIENTS3.txt", False

    If TableExists("PATIENTS3_ImportErrors") Then
        
        'TODO: Open Report of import Errors
        strMsg = "Error Importing Patients into Staging table. " + vbCrLf + "Please view the Importing Patients Report."
        MsgBox strMsg, vbExclamation, "ERROR"
        
    Else
        
        ' cursor? or loop? thru staging table (OpenRecordset?)
            'Check if row already exists (will only be 0 or 1 row)
                'If exists, update row in real table
                'If doesnt exist, copy row to new table
            'Delete row in staging table
        'Next Row in staging table

        
        MsgBox "Patients have been successfully loaded.", vbInformation, "Load Patients"
    End If

End Sub

thanks again
Tanya