Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    6

    Unanswered: DoCmd.TransferText - Import & Delete

    First off, I am new to access vba.
    I have been able to succesfully use the DoCmd.TransferText to import a .csv file into a table using the code below...

    Code:
    Public Sub Transfer()
    
    DoCmd.TransferText acImportDelim, "ImportSpecs", "Myfile", "F:\TWR\data\myfile.csv", False
    DoCmd.SetWarnings False
    If IsTableQuery("", "Myfile_ImportErrors") Then DoCmd.DeleteObject acTable, "Myfile_ImportErrors"
    End If
    DoCmd.SetWarnings True
    
    End Sub
    The problem is that I only want to keep the records that are in the text file. Any records that are not in the text file need to be removed from the access table when the macro is run. What is the best way to automate this?
    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Like this?
    Code:
    Public Sub Transfer()
    
        Currentdb.Execute("DELETE FROM Myfile;), dbFailOnError
        DoCmd.TransferText acImportDelim, "ImportSpecs", "Myfile", "F:\TWR\data\myfile.csv", False
        DoCmd.SetWarnings False
        If IsTableQuery("", "Myfile_ImportErrors") Then 
            DoCmd.DeleteObject acTable, "Myfile_ImportErrors"
        End If
        DoCmd.SetWarnings True
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Jan 2013
    Posts
    6
    I tried the code below and got a table full of "#delete" values

    Code:
    CurrentDb.Execute ("DELETE FROM Myfile"), dbFailOnError
    DoCmd.TransferText acImportDelim, "ImportSpecs", "Myfile", "F:\TWR\data\myfile.csv", False
    DoCmd.SetWarnings False
    If IsTableQuery("", "Myfile_ImportErrors") Then DoCmd.DeleteObject acTable, "Myfile_ImportErrors"
    End If
    DoCmd.SetWarnings True
    Other suggestions?

  4. #4
    Join Date
    Jan 2013
    Posts
    6
    I forgot to mention that I need to keep records in the access table from being overwritten by the records that reside in the text file that I am importing. So when I import the text file on a daily basis, the new records are added and old records (records that do not exist in the text file) are deleted from the access table. Would it be better to import the text file as a separate table from the working table? I will constantly be updating some values of existing records that will not match the values from the text file.
    So in the end... if the imported text file record exists in the access table, it should not be overwritten; if the record does not exist in the text file, it should be deleted from the access table. Only new records form the text file will be added to the access table.
    Hopefully this makes sense.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Quote Originally Posted by jloeff View Post
    I tried the code below and got a table full of "#delete" values
    This is normal. when you delete rows from a table that's open (which, by the way should never happen in the normal use of an Access application: you use Forms to manipulate data stored into Tables), the deleted values are displayed as #Deleted. If you close and re-open the Table, you'll see that the rows were actually deleted and that the Table is empty.

    Quote Originally Posted by jloeff View Post
    I forgot to mention that I need to keep records in the access table from being overwritten by the records that reside in the text file that I am importing. So when I import the text file on a daily basis, the new records are added and old records (records that do not exist in the text file) are deleted from the access table.
    Now it's clearer, because it's not precisely what you wrote in your first post:
    Quote Originally Posted by jloeff View Post
    The problem is that I only want to keep the records that are in the text file.
    Quote Originally Posted by jloeff View Post
    Would it be better to import the text file as a separate table from the working table?
    In such a case yes, it's a solution. When data has been imported from the file into a temporary table (let's call it Tbl_Import), you can use a set of queries to update the destination Table

    1. To remove rows in MyFile that are not in the import Table:
    Code:
    DELETE * FROM MyFile
    WHERE MyFile.SysCounter IN (SELECT MyFile.SysCounter
                                FROM MyFile LEFT JOIN Tbl_Import ON MyFile.SysCounter = Tbl_Import.SysCounter
                                WHERE Tbl_Import.SysCounter Is Null
                               );
    2. To Add rows from Tbl_Import that are not in MyFile:
    Code:
    INSERT INTO MyFile
    SELECT * FROM Tbl_Import 
    WHERE Tbl_Import.SysCounter IN (SELECT Tbl_Import.SysCounter
                                    FROM Tbl_Import LEFT JOIN MyFile ON Tbl_Import.SysCounter = MyFile.SysCounter
                                    WHERE MyFile.SysCounter Is Null
    );
    Note: In my examples, SysCounter is the name of the Identity columns, i.e. the column which contains data uniquely identifying each row (often the primary key of the destination table).
    Have a nice day!

  6. #6
    Join Date
    Jan 2013
    Posts
    6
    I keep getting a Run-time error '3075'
    "syntax error(missing operator) in query expression 'Myfile.Job# IN(SELECT...."

    on this code line:

    Code:
    CurrentDb.Execute "DELETE * FROM MyFile WHERE MyFile.Job# IN (SELECT MyFile.Job# FROM MyFile LEFT JOIN Tbl_Import ON MyFile.Job# = Tbl_Import.Job# WHERE Tbl_Import.Job# Is Null)"
    What am I missing?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Try with:
    Code:
    [MyFile].[Job#]
    and
    Code:
    [Tbl_Import].[Job#]
    Note: It's never a good idea to use the space character or other non-alphanumeric characters (@#&-, etc.), except the underscore (_), in the names of the objects in a database (tables, columns, forms, controls, etc.). If you use these characters, there are rules you must follow to use them in expressions Access can understand and, sooner or later, you'll be in trouble when using them.
    Have a nice day!

  8. #8
    Join Date
    Jan 2013
    Posts
    6
    Thanks for the help so far. I have everything updating correctly with the code below and now would like to go one step further.
    This code is currently in a module but I would like to have it autorun daily or when a form is opened... not sure the best way to go about it. Any suggestions?

    Code:
    'Import Myfile
    DoCmd.TransferText acImportDelim, "ImportSpecs", "Myfile", "F:\TWR\data\myfile.csv", False
    
    'Delete old records from Tbl_Import
    CurrentDb.Execute "DELETE * FROM Tbl_Import WHERE Tbl_Import.JobNo IN (SELECT Tbl_Import.JobNo FROM Tbl_Import LEFT JOIN MyFile ON Tbl_Import.JobNo = MyFile.JobNo WHERE MyFile.JobNo Is Null)"
    
    'Add new records to Tbl_Import
    CurrentDb.Execute "INSERT INTO Tbl_Import SELECT * FROM MyFile WHERE MyFile.JobNo IN (SELECT MyFile.JobNo FROM MyFile LEFT JOIN Tbl_Import ON MyFile.JobNo = Tbl_Import.JobNo WHERE Tbl_Import.JobNo Is Null)"
    
    'Delete Myfile Table
    CurrentDb.Execute "DROP TABLE MyFile"

  9. #9
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    You can call the procedure that imports the data in the AutoExec Macro of the application. Or you can call it of the Form_Open event handler of a form that's open every time the application starts (such as a Switchboard, for instance).
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •