CODEWITHSUNDEEP
sqlms-accessvba
kb_sou
kb_sou

Reputation: 1057

Access SQL Query to Add or Update Values

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?

Upvotes: 0

Views: 665

Answers (2)

Simon1979
Simon1979

Reputation: 2108

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:

  1. Create your table with appropriate field names and data types
  2. Manually import the data from your text file and save the import spec
  3. 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

Upvotes: 0

Jeff Rosenberg
Jeff Rosenberg

Reputation: 3572

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.

Upvotes: 3

Related Questions