Re: merge multiple databases
From: Jim Carlock (anonymous_at_127.0.0.1)
Date: 05/10/04
- Next message: Graeme Richardson: "Re: "OR" IN QUERY"
- Previous message: Bill Gargan: ""OR" IN QUERY"
- In reply to: DIOS: "Re: merge multiple databases"
- Next in thread: DIOS: "Re: merge multiple databases"
- Reply: DIOS: "Re: merge multiple databases"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 10 May 2004 15:44:02 -0400
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
- Next message: Graeme Richardson: "Re: "OR" IN QUERY"
- Previous message: Bill Gargan: ""OR" IN QUERY"
- In reply to: DIOS: "Re: merge multiple databases"
- Next in thread: DIOS: "Re: merge multiple databases"
- Reply: DIOS: "Re: merge multiple databases"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
