BalloutMoe
Board Regular
- Joined
- Jun 4, 2021
- Messages
- 137
- Office Version
- 365
- Platform
- Windows
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.
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.
VBA Code:
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