Prev: Late Binding Issue
Next: Conditional formating question
From: Salad on 21 Jan 2010 18:00
s wrote:

> The word 2007(.docx) is saved as a plain text(.txt) file first. Then,
> I add a delimiter(#) to each row
> manually. The tables in the document look like below
>
> Table 1
> Row 1 data#
> Row 2 data#
> Row 3 data#
> Row 4 data#
> .
> .
> .
>
> Table 2
> Row 1 data#
> Row 2 data#
> Row 3 data#
> .
> .
> .
> .
>
> Table 3
> Row 1 data#
> Row 2 data#
> Row 3 data#
>
> .
> .
> .
>
>
> and so on
>
>
>
> Then, go into Access 2007, External Data, Text File to import the text
> file data into Access choosing # as the delimiter
>
> The table which is created in Access 2007 is as follows
>
> Field1
> Row 1 data of Table 1
> Row 2 data of Table 1
> Row 3 data of Table 1
>
> .
> .
> .
> Row 1 data of Table 2
> Row 2 data of Table 2
> Row 3 data of Table 2
> Row 4 data of Table 2
> .
> .
> .
> Row 1 data of Table 3
> Row 2 data of Table 3
> Row 3 data of Table 3
> Row 4 data of Table 3
>
>
>
>
> whereas I want the table in Access to look like
>
> Field 1 Field 2 Field
> 3 Field 4 .....Field N
> Row 1 data of Table 1. Row 2 data of Table 1 Row 3 data of Table 1 Row
> 1 data of Table 2 .....
> .
> .
>
>
> Can someone please advise if that can be done or do I need to develop
> some VBA code for that?
> If I need to use VBA code, some pointers in that direction would be
> appreciated.
>
>
> Thanks

Three newsgroups? Oh well.

With VBA, you have 3 recordsets and you want to merge them together.
Don't know if you have an autonumber, no idea if the record counts are
the same, or if there's 3 recs in the first file and 4 in the other 2.
Maybe a query can work, maybe better to read line by line via vba.

So lets say you did a FileImport using Docmd.TransferText.

There's only 1 column (field) in each table. I called the column
FieldName, change code below to reflect true field name

Lets say they are called table1, table2, table3. Change those names as
welll.

Lets assume the fields are something like T1R1, T1R2, T1R3, T2R1, T2R2,
etc, the T for the table number R for the row.

The tables as Table1...Table2, and Merged is the table name holding all
data from the 3 tables

YOu could have code similar to this (untested/uncompiled) in a routine
Dim intF1 As Integer
Dim intF2 As Integer
Dim intF3 as Integer

Dim intC1 As Integer
Dim intC2 as Integer
Dim intC3 as Integer

Dim s As String

Dim rst1 as Recordset
Dim rst2 as Recordset
Dim rst3 as Recordset
Dim rst as recordset

'Change to reflect your table names
Set rst = currentdb.Openrecordset("Merged",dbopendynaset)
Set rst1 = Currentdb.Openrecordset("Table1",dbopensnapshot)
Set rst2 = Currentdb.Openrecordset("Table2",dbopensnapshot)
Set rst3 = Currentdb.Openrecordset("Table3",dbopensnapshot)

Do While True
rst.AddNew
For intC1 = 1 to 3 '(assumes 3 recs for each row in Table1)
s = "T1R" & intC1
IF not rst1.EOF() then
'change fieldname to your fieldname
rst(s) = rst1("FieldName")
rst1.moveNext
Endif
Next
For intC2 = 1 to 4 '(assumes 4 recs for each row in Table2)
s = "T2R" & intC2
IF not rst2.EOF() then
'change fieldname to your fieldname
rst(s) = rst2("FieldName")
rst2.moveNext
Endif
Next
For intC2 = 1 to 4 '(assumes 4 recs for each row in Table3)
s = "T3R" & intC3
IF not rst3.EOF() then
'change fieldname to your fieldname
rst(s) = rst3("FieldName")
rst3.moveNext
Endif
Next
rst.Update
Loop

rst1.Close
rst2.Close
rst3.Close
rst.Close

Set rst1 = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
Set rst = Nothing






From: Salad on 24 Jan 2010 21:41
s wrote:
>>With VBA, you have 3 recordsets and you want to merge them together.
>>Don't know if you have an autonumber, no idea if the record counts are
>>the same, or if there's 3 recs in the first file and 4 in the other 2.
>
>
> The record counts can vary, don't have an autonumbering.
>
The code I posted should work. If you had 1 record in table 1, 2 in
table2, and 3 in table 3 the end result should be
T1Rec1 T2Rec1 T3Rec1
T2Rec2 T3Rec2
T3Rec3


 | 
Pages: 1
Prev: Late Binding Issue
Next: Conditional formating question