Access Vba to copy table from one database to another

[BalloutMoe]

Hello all,

I have a .dat file that opens up in an access 97 application. I would like to copy one table from the file and certain columns to a new access database. I was able to copy the whole table in excel however I am stuck on how to do this in access so that way I can skip excel all together. I cannot insert a query in the .dat file or do anything just need to copy 5 columns to the database. If I can have it just update new data as well that would be even perfect instead of having to clear the whole table and insert all of the data. I would like to stay away from DoCmd as that was causing an error in one of the locations. Any help would be appreciated.

So copy Fields 1,2,3,4,5,6 from oldDatabase into newDatabase Fields 1,2,3,4,5,6! If it can be only new data that would be great unless I can clear all of the data in the table before.

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & fileName & "'"
 
    strSQL = TableName

    Set Rs = CreateObject("ADODB.Recordset")
    Rs.Open strSQL, strConn, adOpenDynamic, adLockOptimistic

For i = 0 To Rs.Fields.Count - 1
            Ws.Cells(1, i + 1).Value = Rs.Fields(i).Name
        Next
        Ws.Range("A1").Resize(ColumnSize:=Rs.Fields.Count).Font.Bold = True


Ws.Range("A2").CopyFromRecordset Rs

    Rs.Close
    Set Rs = Nothing