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

From: rogsonl (rogsonl_at_discussions.microsoft.com)
Date: 10/22/04

  • Next message: Jim~C: "Query file system using ADO?" 
    Date: Fri, 22 Oct 2004 10:49:03 -0700

    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
    >

  • Next message: Jim~C: "Query file system using ADO?"

    Relevant Pages

    • Re: Imitating Mail Merge on server
      ... given moment we know which data (in this case, customers) is being worked ... and concantenate each query together. ... loop the array, plug in the current recordset value. ... We have close to fifty tokens that we use in the application correspondence, ...
      (microsoft.public.inetserver.asp.general)
    • Re: Subquery Confusion
      ... Then I got this crazy idea that an Array can only contain a maximum ... number of characters, ... Then I decide that maybe I'm completely wrong with my query, ... it out of Excel VBA and spit it into Microsoft SQL Server Management ...
      (microsoft.public.excel.programming)
    • Re: Form fields array
      ... I see your point about the array being empty.. ... Dim incDays ... for counter=0 to iDays 'iDays are the count of how many days between ... >> not know how many records the query will pull up. ...
      (microsoft.public.inetserver.asp.db)
    • Re: Arrays in Access.
      ... The real question, Randy, would be "What is the compelling reason for moving ... query, the DMAX and DMIN functions, for example... ... And all a LOT easier than filling and sorting an array ... >> need to a) sort your array (bubble sort would be efficient, ...
      (comp.databases.ms-access)
    • Re: [PHP] Getting mysql_query results into an array
      ... and don't put them into an array at all. ... query is nasty and the PHP is fast and easy, ... The original question was "what's most efficient". ... even though they appear in the original order within the raw ...
      (php.general)