Re: merge multiple databases

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: DIOS (sindizzy.pak_at_softhome.net)
Date: 05/10/04 

Date: Mon, 10 May 2004 17:30:53 GMT

The database merge is going to be done completely through code.
The end user does not even know that that databases are Access97
databases. I will keep the same table structure in the new database.
I just want to dump all database records into the new database but because
some IDs may be the same then i just want to account for that. Again,
i have a plan but its based on iterating through each record. There should
be
an easier way to do this.

AGP 

-- 
**********************************************************************
Unpak my email address before sending me personal email
**********************************************************************
"Jim Carlock" <anonymous@127.0.0.1> wrote in message
news:uVoycxkNEHA.1004@TK2MSFTNGP10.phx.gbl...
> Create links to the tables. If you need to move data, then via
> Access create a make table query that uses the link to create
> a new table.
>
> I typically name the links, lnkNames and then create the new
> table and give it name, tNames. You'll have to be careful with
> the links inside of Access. Make sure you're working with
> backups while you're playing around. You don't want to delete
> a whole table on accident.
>
> I tend to use DAO to do everything, but it can be done in ADO
> as well.
>
> If you plan on reusing the tables over and over, say, you're
> going to delete everything in the table, and recreate it, you'll
> need to set up a loop to go through the tabledefs to delete
> a specific table, before executing the make table query.
>
> The make table queries are ran with a DAO.Execute statement,
> as it's an action query. Select queries can be run opened as
> recordsets. Action queries need to be executed.
>
> Let me know if you need any other help.
>
> -- 
> Jim Carlock
> http://www.microcosmotalk.com/
> Post replies to the newsgroup.
>
>
> "DIOS" wrote:
> I have multiple Access97 databases that i want to merge into one single
big
> database. I have some ideas on how to do it but would like any suggestions
> on doing the mrege more efficiently. Currently i am using a VB app to
> connect to
> the various databases via DAO. The tables look like so:
>
> tblDrive
> --------
> DriveID (primary key)
> fldTitle
> fldDesc
>
> tblFiles
> --------
> FileID (primary key)
> DriveID
> fldFTitle
> fldFSize
>
> tblSubFiles
> -----------
> SubFileID (primary key)
> FileID
> DriveID
>
> Basically each drive has many files and each file has many subfiles. I was
> going to iterate through each record in
> tblDrive and then do a query with the DriveID and get all proper records
> from tblFiles and add those to the new database
> with a new DriveID. Then for my tblFiles recordset I was going to perform
a
> query with the same DriveID and the same
> FileID and take those records and add them to the new database. This seems
> like rather inefficient to me but i cant
> see how to do it in a more elegant manner.
>
> tia
> AGP
>
>
>
>
> -- 
>
> **********************************************************************
> Unpak my email address before sending me personal email
> **********************************************************************
>
>
>

Relevant Pages

  • Re: Query an external database...
    ... > Create a Query in the Access 2K file. ... > The reason I put 0001 on the front is because if it's the first> query in a set of queries, you'll be able to know what it's doing,> how many queries there are, etc.> ... > Im working within an Access2000 database and writing code in VBA. ... > Unpak my email address before sending me personal email ...
    (microsoft.public.vb.database.dao)
  • Re: Query an external database...
    ... > Create a Query in the Access 2K file. ... > The reason I put 0001 on the front is because if it's the first> query in a set of queries, you'll be able to know what it's doing,> how many queries there are, etc.> ... > Im working within an Access2000 database and writing code in VBA. ... > Unpak my email address before sending me personal email ...
    (microsoft.public.vb.general.discussion)
  • Re: Query an external database...
    ... > Create a Query in the Access 2K file. ... > The reason I put 0001 on the front is because if it's the first> query in a set of queries, you'll be able to know what it's doing,> how many queries there are, etc.> ... > Im working within an Access2000 database and writing code in VBA. ... > Unpak my email address before sending me personal email ...
    (microsoft.public.access.queries)
  • Re: merge multiple databases
    ... Access create a make table query that uses the link to create ... DriveID ... from tblFiles and add those to the new database ...
    (microsoft.public.vb.database.dao)
  • Re: merge multiple databases
    ... Access create a make table query that uses the link to create ... DriveID ... from tblFiles and add those to the new database ...
    (microsoft.public.vb.general.discussion)