I moved data from a MS-Word Table into a MS-Access table using
VBA code. Almost all of the word documents had five rows for first table
in the document, but few Word documents had six rows for their first
table which I did not know earlier. I created the Access table with five
columns to hold data from each corresponding row of the first table of
the Word document. Now, the documents which had six rows in their first
table have their data flowing into sixth column of the Access table
which was for storing data of table 2 from the Word document.

I had something like Word Document A which has table 1 having five rows

Table 1

Row 1 Row 1 Data
Row 2 Row 2 Data
..
..
Row 5 Row 5 Data


But, Word Document B has a table 1 having six rows

Table 1

Row 1 Row 1 Data
Row 2 Row 2 Data
..
..
Row 5 Row 5 Data
Row 6 Row 6 Data

Access Table "mytable" has a structure like

Column for Row 1 of Table 1,
Column for Row 2 of Table 1,
Column for Row 3 of Table 1,
Column for Row 4 of Table 1,
Column for Row 5 of Table 1,
Column for Row 1 of Table 2,
Column for Row 2 of Table 2,
..
..
..
Column for Row N of Table N,

Access Table "myTable" has some rows like

Column for Row 1 of Table 1 contains Row 1 Data of Table 1
Column for Row 2 of Table 1,contains Row 2 Data of Table 1
Column for Row 3 of Table 1,contains Row 3 Data of Table 1
Column for Row 4 of Table 1,contains Row 4 Data of Table 1
Column for Row 5 of Table 1,contains Row 5 Data of Table 1
Column for Row 1 of Table 2,contains Row 6 Data of Table 1 ' which is
not correct
Column for Row 2 of Table 2,contains Row 1 Data of Table 2 ' which is
not correct


1. Is exporting the Access table data into MS-Excel, adding a column to
the Access table for the sixth row and then reimporting the data back
from Excel an option or are there better alternatives?

2. Also, there are some characters which appear as question marks in
the Access table cells. If a column in the Word table had a blank space
before the sentence started, the corresponding access table cell has a
question mark character. I know I should have added some filtering in
the VBA code before storing it in a Access table, but as I did not is
there a quick way I can remove the question mark characters? Can I run
some VBA which will traverse all columns of all rows of the Access table
and remove the question mark character?

I am using Access 2007.

Any advice would be welcome.

g wrote:

or does not address your needs.
This link may offer some iterest to you
http://msdn.microsoft.com/en-us/libr...datafromatable
Look for topic "Extracting Data from Word Tables Programmatically" at
the link.

You could do something like
intFldCount = currentdb.TableDefs("YourTableName").Fields.Count
to get the field count of a table.

You could compare the counts of elements of the array from the link above
Ubound(array) + 1
and compare to intFldCount to determine if you have enough columns.

Maybe you want to alter the table.
http://www.blueclaw-db.com/alter_table_ddl.htm

On 7/2/2010 7:17 PM, Salad wrote:
I will try explaining it again. I moved data from Word document tables
into a Access Table, but some tables in the Word document had more rows
than fields allocated to them in the Access table so they flowed into
the field reserved for next Word Table.

For e.g. lets assume my word document has two tables, Table 1 and Table
2 each having five rows. I want data from each of the five rows to be
stored in a Access table(we can call it Dbtable) which has a column for
each row of the table in the Word document so it has 10 columns/fields.
I did that which was fine. But, there is another word document whose
Table 1 has 6 rows instead of five so data from the sixth row of that
table is flowing into a column 6 of Dbtable. Column 6 of Dbtable is for
storing data from first row of Table 2 not for sixth row of table 1. I
can increase the number of columns by doing a alter table as you
suggest, but for word documents having Table 1 and Table 2 having five
rows, there would be an extra column which means data from row 1 of
Table 2 of the Word document will go into column 6(which is created for
handling Word document whose Table 1 has 6 rows). How can i fix this issue?

Another problem is when I imported data from a Word table into a field
in a Access table(Dbtable) if the data in the Word table cell had a
blank space before the text in it, now there is a question mark
character present in the Access table cell. How can I fix this? This is
similar to running some VBA code for all columns of all records and
removing the leading and trailing blank spaces present in the columns.


Thanks for the links and your time.

I hope my question is a bit more clearer now and you can guide me in the
proper direction.

On 7/2/2010 8:04 PM, g wrote:
This got solved. I needed to add a Replace(String, Chr(13), "") in the
VBA which was moving data from the Word table to the Access table. And,
now there are no question mark characters in the Access table. The
leading and trailing blank spaces present in the Word table cells were
carriage returns before and after the data.

g wrote:
No. For me, a table consists of fields. In a datasheet display, the
fields are the columns, the rows consist of a related set of fields. So
if a table in word has 1 row or two rows of 5000 rows, it would make to
difference to me...I'd simply add a record, update the fields, and
update the table by committing the add.

Is your word table 1 column? And each row of the word table is a column
in a database field?

I don't know. Did you read the link I presented?

Have you used the Split() function before?

On 7/2/2010 9:30 PM, Salad wrote:
No, but I am reading only the last column of each table in Word as I
need only that data.

And each row of the word table is a column
Yes, each row of the word table has a corresponding field in the Access
table.
So, for table 1 in word I have five database fields, for table 2 another
five fields and so on.

I went over the part Extracting Data from Word Tables Programmatically
in the link
and my issue is not how to extract from Word to Access table. That is
done. Issue is about the inconsistent way rows are present in word table
for which there is no corresponding database field.

Yes, but in this case it is not that which is being incorrectly used.

My issue is it once the data is in Access, can it be manipulated in some
way like in Excel you can add an empty column, between two columns
having data and then move the rightmost column data leftwards in the
newly created column. I can alter the table structure to add another
column, but the data still needs to be moved to the right database field.

Thanks for your advice.

g wrote:
you like. You could even add a "NumCols" field to indicate the number
of columns necessary for the row. (Ex: Dmax or a GroupBy query).

You could then roll your own SQL and present the rows/cols that you need
via a query.

Or maybe you could present the data using a CrossTab query.

I notice you cross post. Is there a reason why? FYI, sometime in the
very near future the microsoft.public newgroups are going away.

On 7/3/2010 1:36 AM, Salad wrote:
Thanks for the suggestions.


I will look into this option. I have to generate a report using the data
when a button on the form is clicked.

You mean I post to comp.databases.ms-access and microsoft.public.access
at the same time? I do it because some people read only one forum and
others another so I guess if I post in both, I can get advice from both
people.

Thanks for your advice and time.