Hello, I’m currently trying to create a function that will look at each record from a single column database table and the record will be copied and distributed into another table depending on the content. I probably haven’t explained myself very well so I’ll demonstrate.
The single column table contains a number of counters, it is written by a machine into a text file. This text file will be automatically imported into my database with an autonumber. We can see the ID column and each counter surrounded by brackets, below each counter is a cycle and a runtime. Each counter is separated by a blank row.
[<Counter1>]
TotalCycles=0
TotalRunTime=000000
[<Counter2>]
TotalCycles=0
TotalRunTime=000000
[<Counter3>]
TotalCycles=0
TotalRunTime=000000
[<Counter4>]
TotalCycles=0
TotalRunTime=000000
However this is no use to me and I want to copy and turn each counter horizontally into an individual record in another table, like so.
Counters Cycles Runtime
[<Counter1>] 0 0
[<Counter2>] 0 0
[<Counter3>] 0 0
[<Counter4>] 0 0
I want to write some VBA code that will look at the source table and copy the records to create the second table. I’ll probably triggering this with the autoexec macro as the database fires. I’ve looked at some DAO codes that will run through the first recordset, but I was wondering if there is a simple way of doing this. I was going to use mid,left or right codes to chop off the titles of the cycles and runtimes. I was also thinking of creating the second table before hand and simply running a delete query everytime the database was opened. Any help in the right direction will be much appreciated.
Thanks,
Rob
The single column table contains a number of counters, it is written by a machine into a text file. This text file will be automatically imported into my database with an autonumber. We can see the ID column and each counter surrounded by brackets, below each counter is a cycle and a runtime. Each counter is separated by a blank row.
[<Counter1>]
TotalCycles=0
TotalRunTime=000000
[<Counter2>]
TotalCycles=0
TotalRunTime=000000
[<Counter3>]
TotalCycles=0
TotalRunTime=000000
[<Counter4>]
TotalCycles=0
TotalRunTime=000000
However this is no use to me and I want to copy and turn each counter horizontally into an individual record in another table, like so.
Counters Cycles Runtime
[<Counter1>] 0 0
[<Counter2>] 0 0
[<Counter3>] 0 0
[<Counter4>] 0 0
I want to write some VBA code that will look at the source table and copy the records to create the second table. I’ll probably triggering this with the autoexec macro as the database fires. I’ve looked at some DAO codes that will run through the first recordset, but I was wondering if there is a simple way of doing this. I was going to use mid,left or right codes to chop off the titles of the cycles and runtimes. I was also thinking of creating the second table before hand and simply running a delete query everytime the database was opened. Any help in the right direction will be much appreciated.
Thanks,
Rob
