VB.Net (ADO): Thoughts on Copying 7 tables to 1 table

2 Part Question:

I've a VB.net application where I read information from 7 Tables in a MS
Access Db. There is a performance hit when this happens (about a 1-2 second
hit per table). So I'm going to combine the relavent parts of each table into
a single table in a "new' MDB file.

I've experimented with this and found that the best way that I can see is to
gather the information from each table into an Array and then dump that info
into my new MDB file. I want all the 7 tables starting with the FIRST row in
the new table.

I first get the number of rows for a table, re-dim my array and gather the
info. Then I repeat for the next table - I compare table row lengths to make
sure my Array does not get smaller than the maximum table row found.

All appears to work fine.

Question #1:

Can someone suggest a better way (if there is one) to take the "selected"
columns from a single MDB (having 7 tables) into a new single MDB with 1
table. I tried reading each of the 7 tables into my new MDB, but this doesn't
work as each table adds new rows past the previous table information.

Question #2:
Every now and then, I find one of my columns (about mid way down) has a bunch
of NULL cells when the data should be continuous in the column. I run the
application again (maybe a third time) and all is OK. All the other columns
are OK (and the code is similar). A head scratcher for me. Any thoughts?

Thanks in advance.

Regards,

Bruce

Unicorn
Thu Oct 21 22:59:07 CDT 2004


"Mr. B" <User@NoWhere.com> wrote in message
news:aakfn0t8qfp7qgnar007gu42ct30evs43b@4ax.com...
> 2 Part Question:
>
> I've a VB.net application where I read information from 7 Tables in a MS
> Access Db. There is a performance hit when this happens (about a 1-2
second
> hit per table). So I'm going to combine the relavent parts of each table
into
> a single table in a "new' MDB file.
>
> I've experimented with this and found that the best way that I can see is
to
> gather the information from each table into an Array and then dump that
info
> into my new MDB file. I want all the 7 tables starting with the FIRST row
in
> the new table.
>
> I first get the number of rows for a table, re-dim my array and gather the
> info. Then I repeat for the next table - I compare table row lengths to
make
> sure my Array does not get smaller than the maximum table row found.
>
> All appears to work fine.
>
> Question #1:
>
> Can someone suggest a better way (if there is one) to take the "selected"
> columns from a single MDB (having 7 tables) into a new single MDB with 1
> table. I tried reading each of the 7 tables into my new MDB, but this
doesn't
> work as each table adds new rows past the previous table information.
>
> Question #2:
> Every now and then, I find one of my columns (about mid way down) has a
bunch
> of NULL cells when the data should be continuous in the column. I run the
> application again (maybe a third time) and all is OK. All the other
columns
> are OK (and the code is similar). A head scratcher for me. Any thoughts?
>
> Thanks in advance.
>
> Regards,
>
> Bruce

I am not a .Not person, so I don't know if the old tried and true access
insert query would work.

INSERT INTO Table_Allowance IN "C:\MyMDB.MDB" SELECT Table_Allowance.* FROM
Table_Allowance;

Matt

Mr
Fri Oct 22 09:48:41 CDT 2004

With Deft Fingers, "Unicorn" <unicorn@somewhere.com> wrote:

>I am not a .Not person, so I don't know if the old tried and true access
>insert query would work.

You don't know what you are missing !!!! (grin)

>INSERT INTO Table_Allowance IN "C:\MyMDB.MDB" SELECT Table_Allowance.* FROM
>Table_Allowance;

Hmmm... Interesting... I'll see if this (or something like it) will work!
Thanks!

Bruce

rogsonl
Fri Oct 22 12:49:03 CDT 2004

Whenever I need to gather information from a variety of tables, I create a
query that does it for me. You can then "Export" the query to Excel, and
import it into the new database.
This will create a table with the same name as the query (unless you change
it) .

You can also create a temporary table using the query (convert it from
select to create table) and then import the table, eliminating Excel.
Leon

"Mr. B" wrote:

> 2 Part Question:
>
> I've a VB.net application where I read information from 7 Tables in a MS
> Access Db. There is a performance hit when this happens (about a 1-2 second
> hit per table). So I'm going to combine the relavent parts of each table into
> a single table in a "new' MDB file.
>
> I've experimented with this and found that the best way that I can see is to
> gather the information from each table into an Array and then dump that info
> into my new MDB file. I want all the 7 tables starting with the FIRST row in
> the new table.
>
> I first get the number of rows for a table, re-dim my array and gather the
> info. Then I repeat for the next table - I compare table row lengths to make
> sure my Array does not get smaller than the maximum table row found.
>
> All appears to work fine.
>
> Question #1:
>
> Can someone suggest a better way (if there is one) to take the "selected"
> columns from a single MDB (having 7 tables) into a new single MDB with 1
> table. I tried reading each of the 7 tables into my new MDB, but this doesn't
> work as each table adds new rows past the previous table information.
>
> Question #2:
> Every now and then, I find one of my columns (about mid way down) has a bunch
> of NULL cells when the data should be continuous in the column. I run the
> application again (maybe a third time) and all is OK. All the other columns
> are OK (and the code is similar). A head scratcher for me. Any thoughts?
>
> Thanks in advance.
>
> Regards,
>
> Bruce
>

Mr
Fri Oct 22 17:51:11 CDT 2004

With Deft Fingers, "rogsonl" <rogsonl@discussions.microsoft.com> wrote:

>You can also create a temporary table using the query (convert it from
>select to create table) and then import the table, eliminating Excel.

I guess I'd rather not go the Excel route (why bother when you can go direct,
eh?). But can you do this taking 7 different tables and importing them as One
table? That's the key for me.

Regards,

Bruce

NIL
Sat Oct 23 11:31:02 CDT 2004


You can write a QUERY that uses UNION and execute it to copy all the data in
7 Tables to the new table.

select col1, col2, col3 from table1
UNION
select col1, col2, col3 from table2
UNION
..
..
select col1, col2, col3 from table7



"Mr. B" wrote:

> 2 Part Question:
>
> I've a VB.net application where I read information from 7 Tables in a MS
> Access Db. There is a performance hit when this happens (about a 1-2 second
> hit per table). So I'm going to combine the relavent parts of each table into
> a single table in a "new' MDB file.
>
> I've experimented with this and found that the best way that I can see is to
> gather the information from each table into an Array and then dump that info
> into my new MDB file. I want all the 7 tables starting with the FIRST row in
> the new table.
>
> I first get the number of rows for a table, re-dim my array and gather the
> info. Then I repeat for the next table - I compare table row lengths to make
> sure my Array does not get smaller than the maximum table row found.
>
> All appears to work fine.
>
> Question #1:
>
> Can someone suggest a better way (if there is one) to take the "selected"
> columns from a single MDB (having 7 tables) into a new single MDB with 1
> table. I tried reading each of the 7 tables into my new MDB, but this doesn't
> work as each table adds new rows past the previous table information.
>
> Question #2:
> Every now and then, I find one of my columns (about mid way down) has a bunch
> of NULL cells when the data should be continuous in the column. I run the
> application again (maybe a third time) and all is OK. All the other columns
> are OK (and the code is similar). A head scratcher for me. Any thoughts?
>
> Thanks in advance.
>
> Regards,
>
> Bruce
>

Mr
Sat Oct 23 14:02:40 CDT 2004

With Deft Fingers, NIL <NIL@discussions.microsoft.com> wrote:

>You can write a QUERY that uses UNION and execute it to copy all the data in
>7 Tables to the new table.
>
>select col1, col2, col3 from table1
>UNION
>select col1, col2, col3 from table2
>UNION
>..
>..
>select col1, col2, col3 from table7

Verrrry interesting. My ADO bood (by David Sceppa) doesn't have anything on
UNION. I'll dig into this a bit more and find out about it as it sounds just
what I need...

Many thanks for the tip!

Bruce