Access SQL Query to Add or Update Values

Question

I have an Access database that I need to update every week based on a fixed length text file

The file contains some new records and some updates.

Currently, I am using an ADODB connection to treat the file as a recordset, looping thru its records and adding or updating my records if needed.

The problem is that this process is very slow, complicated and can generate some errors.

Is there a way to achieve the same results using Access SQL?

Jeff Rosenberg · Accepted Answer · 2013-04-15 14:57:40Z

3

Since I don't believe Access has any sort of "upsert" functionality, my first inclination would be to create two queries -- an insert query and an update query -- and add a WHERE clause to each to limit the insert and the update to the appropriate records. Then you can combine then under a single transaction, something like this:

With conn 'Assuming you keep using the ADO recordset; you could use DAO instead
  .BeginTrans
  .Execute "UpdateQuery"
  .Execute "InsertQuery"
  .CommitTrans
End With

Maybe not ideal, but better than a loop.

answered Apr 15, 2013 at 14:57

Jeff Rosenberg

3,5821 gold badge21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Honorez Over a year ago

+1 That is a good idea, but if the db is properly normalised and defined, there will be no need to "limit the insert with a WHERE". Potential duplicates will be discarded thanks to the PK and unique index(es).

Simon1979 · Accepted Answer · 2013-04-15 22:16:51Z

I do quite a lot of importing from Excel and have found the easiest way is to import the file to a table and run all the updates/ inserts from this as it is materially quicker once you have the data in a local table.

You could create the table on the fly but I prefer to have the table structure all setup and use TransferText to import, where you can use an import spec.

Loosely to set this up:

Create your table with appropriate field names and data types
Manually import the data from your text file and save the import spec
Use VBA to import future text files and trigger the update/ insert queries

Code could look something like this:

' Folder where files to be imported are saved
strFilePath = "C:\myFolder\"

' Look for a .txt file in that folder

strFileName = Dir(strFilePath & "*.txt")

' Loop through all .txt files until each is imported
Do Until strFileName = ""



 strFile = strFilePath & strFileName

' Import the file to a temporary table where "myImportSpec" is what you saved
    ' the import spec as when you did the manual import and "tbl_IMPORT_Temp"
    ' is the table you created to run your queries from
    'NOTE: This is what i use for .csv files and haven't tested on .txt but
    ' I think it should work
DoCmd.TransferText acImportDelim, "myImportSpec", "tbl_IMPORT_Temp", strFile

DoCmd.OpenQuery "qryUpdateQuery", acViewNormal
DoCmd.OpenQuery "qryAppendQuery", acViewNormal

' Copy the .txt file to a new folder
FileCopy strFile, strFilePath & "Successful\" & strFileName
' Delete the original file so it isn't imported again
Kill strFile

NextFile:
    ' Clear your temp import table
    DoCmd.RunSQL "DELETE * FROM tbl_IMPORT_Temp"
    ' Get the next file to be imported
    strFileName = Dir(strFilePath & "*.txt")
Loop

Hope this helps. Simon

Collectives™ on Stack Overflow

Access SQL Query to Add or Update Values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related