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

  • Thread starter Thread starter Mr. B
  • Start date Start date
M

Mr. B

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. B said:
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
Click to expand...

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
 
Unicorn said:
I am not a .Not person, so I don't know if the old tried and true access
insert query would work.
Click to expand...

You don't know what you are missing !!!! (grin)
INSERT INTO Table_Allowance IN "C:\MyMDB.MDB" SELECT Table_Allowance.* FROM
Table_Allowance;
Click to expand...

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

Bruce
 
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
 
rogsonl said:
You can also create a temporary table using the query (convert it from
select to create table) and then import the table, eliminating Excel.
Click to expand...

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
 
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
 
With Deft Fingers said:
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
Click to expand...

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
 
Back
Top