Results 1 to 3 of 3
  1. #1
    paulmc1981 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    2

    Apending to a linked table

    Hi and thank you in advance... as I am not very good as VBA (The code supplied was found on the internet).

    I have used some code (see code below) to link some csv files into MS Access, I need this changed so that it does the following:

    1) Checks if a table exists to log what csv files have already been used
    - If no table exists create one
    - If table exists, continue

    2) Check the table that logs the csv files that have already been imported
    - If there are no new csv files - end
    - If there are new csv file(s) - import using the below code and add the csv file to the table that logs what csv files have been imported

    The code I already have is:

    Sub DoImport()


    Dim fso As Object
    Dim fld As Object
    Dim fil As Object
    Dim FldPath As String

    Const DestTable As String = "imptable"

    FldPath = "C:\Users\paul.coan\Desktop\HistoricalData"



    Set fso = CreateObject("Scripting.FileSystemObject")
    Set fld = fso.GetFolder(FldPath)

    With DoCmd
    .SetWarnings False
    For Each fil In fld.Files
    If UCase(Right(fil.Name, 3)) = "CSV" Then
    .TransferText acImportDelim, , DestTable, fil.Path, True
    End If
    Next
    .SetWarnings True
    End With


    Set fil = Nothing
    Set fld = Nothing
    Set fso = Nothing



    MsgBox "done"


    End Sub

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    here's how to check for the existence of any type of object in the db container
    http://access.mvps.org/access/queries/qry0002.htm
    You could create a recordset based on a sql statement looking for your table name and if the record count is zero, the table doesn't exist. To make this reusable, you might create a function that accepts the sql statement, runs it, checks the count and returns true if > 0 else returns false.
    If there are no new csv files - end
    This I can't advise on since I have no idea how you know what's new, but to not execute additional code, you have a line such as Exit Sub or Exit Function at that point - likely within an IF block, like
    If not something Then
    Exit Sub
    Else
    do stuff
    End If
    P.S. for larger blocks of code like yours, please use code tags (indentation would be nice too!)
    Last edited by Micron; 02-20-2017 at 11:53 AM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    paulmc1981 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    2
    Thanks for your help and advice - I only started coding at the weekend, so that is clear and helpful.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  2. Replies: 4
    Last Post: 03-02-2015, 07:54 PM
  3. Replies: 9
    Last Post: 10-20-2014, 04:00 PM
  4. Replies: 2
    Last Post: 04-15-2014, 10:03 PM
  5. Replies: 2
    Last Post: 10-27-2009, 07:09 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums