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 23:59:14 GMT

Jim,

Thanks for all the info. i appreciate it. i have a very good understanding
of how
Acess databases are structured. In the end what i need is just an algorithm
to
dump each table from each database into another new database. the
autu-increment
on the primary keys is what the problem is. I can make links but in the end
i still
have to iterate through each record like Ken halter stated. I dont see any
way around
this and thats why i was asking for suggestions. Since this will be done not
very often i
may just stick to my original plan of looping through records and processing
the merging
that way.

Also like i said, all this has to be done through code and I have yet to do
any linking
through code so i may just INSERT each tabel into my new database, do the
processing
on that table, and then DROP it.

Again thanks for the info.

AGP 

-- 
**********************************************************************
Unpak my email address before sending me personal email
**********************************************************************
"Jim Carlock" <anonymous@127.0.0.1> wrote in message
news:OPLKncsNEHA.1400@TK2MSFTNGP10.phx.gbl...
> Okay, first some comments on the process I'm describing below.
> You need to create a new Access datafile (mdb) to store all in.
> This file can be installed into the folder that other access files are
> located in. This will work not only with Access databases, but
> with just about any type of data system in the world.
>
> With that in mind:
>
> (1) Create the new mdb file.
> (2) Open the newly created Access database. Create the links
> inside the new Access mdb. Keep in mind that having all the
> mdb's in the same folder will help greatly. Name the links:
>
> lnkDrive
> lnkFiles
> lnkSubFiles
>
> If there are any other files, continue creating these links.
> The goal is to gather all the information into one file.
>
> (3) Once all the links are created, you are ready to start
> creating the Make tables. Do this inside Access. It's very
> easy to do. Click on the Queries icon. Click on
> "Create new query", then select lnkDrive as the table to
> work with. Click on the (*) asterisk inside the field list.
> Right click inside the query and click on Query Type...,
> then click on Make Table. You'll be prompted for a file
> name. Type in tDrive. Save the query and exit it.
>
> (4) Test it out inside of Access, but NOTE: when working with
> queries inside Access, make sure you have back ups of the files
> you are going to play around with. When playing with these queries,
> it's way to easy to delete a whole table, or modify and totally
> obliterate a whole table. I can't emphasize it enough. Make sure you
> have a valid backup before you start playing. :-)
>
> The power of the Internal Access queries should be classified as
> the 8th wonder of the world.
>
> (5) Once you have your set of Make Table queries created, it's
> time to start setting up the code inside of VB to run the action
> queries.
>
> (6) Once all the tables are localized into the new access file,
> you can start messing with the queries there to present the data
> to yourself. Once you have a proper query in Access, DAO
> works extremily efficiently and fast handling the internal Access
> queries.
>
> I don't know what your full understanding is of Access, so
> pardon me if I am oversimplifying things.
>
> Take it one step at a time, and break it up into the proper
> steps, it's really amazing.
> ---------------------------------------
> Some important concepts to think about and understand
> when working with Access:
> ---------------------------------------
> When you work with Access, think of tables as tables of
> information, and think of queries as one of two types. They
> are either Action queries or Select queries. Action queries
> perform some operation (modification) on the data, and the
> Select queries just present the data (including sorting and
> sorting is not considered an action). It's important to keep
> the concepts of Action vs (I think it might be called Passive)
> as separate notions in your head.
>
> One other thing I'll mention is that I've used such methods in
> extracting data from ODBC sources, and they were very very
> large tables of data, which covered accounts receivables
> where one particular Home Depot account had thousands
> of Items that were sold, and amounted to millions of dollars.
> I learned some great things about Access when I was doing
> such things:
>
> (1) Access is probably the best file extraction organism in the
> universe and I would classify it as the 8th wonder of the world.
> (2) Internal Access queries, rather than using SQL syntax
> through VB is much much faster.
> (3) I've set and configured 30 or so internal access queries to
> be called in sequence and they all start with the initial make
> table query to copy data from an original database to a local
> access mdb.
>
> I'll wait for a reply from you before I continue with this. Let me
> know if this helps at all.
>
> -- 
> Jim Carlock
> http://www.microcosmotalk.com/
> Post replies to the newsgroup.
>
>
> "DIOS" wrote:
> 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
>
>

Relevant Pages

  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... synchronize combo boxes to the detail section or the parameter form query ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would ...
    (microsoft.public.access.conversion)
  • Re: Query In BE database
    ... that would give the users "live" access to the underlying queries. ... When selected from the drop-down list box, I what the the query that is ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
  • Re: merge multiple databases
    ... You need to create a new Access datafile (mdb) to store all in. ... Open the newly created Access database. ... Click on the Queries icon. ... "Create new query", then select lnkDrive as the table to ...
    (microsoft.public.vb.database.dao)
  • Re: merge multiple databases
    ... You need to create a new Access datafile (mdb) to store all in. ... Open the newly created Access database. ... Click on the Queries icon. ... "Create new query", then select lnkDrive as the table to ...
    (microsoft.public.vb.general.discussion)