• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 551
  • Last Modified:

How to combine (ADD specifically) two STRUCTURALLY IDENTICAL access db's

I have a coldfusion / access db which runs on a standalone pc platform. It is used for registration of students. It has been used for several years with no problems. I would now like to run TWO instances of the application on two seperate PC's to quicken the registration process.

So, I would like, at the end of each day to run a routine which would combine the databases as above into a new database. The updated  database would be used in both machines the following day.

Clearly each of the databases are identical - EXCEPT - one database may have some extra fields to accomodate new students.

Would appreciate confirmation of the feasibility of trying this and an approach to creating the coldfusion script

Many thanks
0
jameskane
Asked:
jameskane
  • 5
  • 3
  • 3
2 Solutions
 
als315Commented:
Is your background DB an Access DB?
In this case you can add one field to your DB (foreign key - Record ID from second DB).
For example, if your Student table has two fields:
StudentID (Autonumber) and StudenName(Text), add FKStudentID (Long Integer).
At night you can run queries appending data to your tables (new record from first DB will add StudentID to PKStudentID in second DB and vise versa). This process is not so simple if you have many tables, in this case you should convert PKStudentID to StudentID in query).
Look at example (tblStudentsDB2 will be linked table from second DB). Empty PKStudentID mean record added directly to current DB and you can use it somewhere.
Your Colfusion code will be the same for both DBs
DBStudents.mdb
0
 
jameskaneAuthor Commented:
Many thanks for input. I really do not want to use Access DB for anything more than a repository. I have 8 recordsets and was thinking of an approach where I create a recordset for each table in the first db and then a recordset for each table in the second db. After that I   would run some sort record set join routine which would give me recordset for the combined tables. Something like that ???
0
 
als315Commented:
Can you upload your DB (without data)? Only tables structure is important
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jameskaneAuthor Commented:
Sorry als315, don't quite understand. If you permit me to recap a little to clarify :

The objective is to allow for two registration desks to operate instead of the current single registration desk. I will be running a coldfusion server on each registration desk (seperate) and also two instantiations of the registration application and database. They are completely exclusive, no network connection.

 At the end of a registration week I have two databases (A and B) which have identical tables, but with different sets of data from the registrations. At the end of the regisration week, I will revert to a single registration desk and therefore need to combine database A and database B into a single database C.

I am trying to write a coldfusion routine which will do this.

Many thanks for your help.
0
 
als315Commented:
It is simple in Access (join two similar DB), you should only run some queries. If you have no full Access, you can do it in free Access runtime (in this case you will not be able to modify anything). I think it is possible also from Coldfusion but I can't help you with it.
0
 
_agx_Commented:
I would now like to run TWO instances of the application on two seperate PC's to quicken the registration process.

It's technically possible, but manual "replication" can introduce other headaches. Especially if there's no connectivity between the two servers. Before you go too far down this route ...

* How many db tables are you talking about, 1, 5, 20 ?
* What's making the current process so slow that you need to do this: the server or the db? If it's the database, what about setting up an instance of ms sql server express instead? It's much more powerful - and unlike ms access - it's designed for concurrency.
0
 
jameskaneAuthor Commented:
Thanks for the response _agx_.

The length of time is just a function of chatting with the student, reviewing the courses, updating their information or just registering them for the first time.  Then there is the cheque to be written and the receipt printed out.

I have a developers coldfusion on a couple of machines and the application - so its easy to have two perfectly exact applications running at the same time. At the end of all this I do need to combine the two databases into one db which will be used for the rest of the year.

There are 8 tables, one of  3 of which do not get updated as they are not impacted by the registration process. So, I need to add the two databases - but not the 3 tables which do not change.

Seemed to me that I could use coldfusion to to this.

Jim
0
 
_agx_Commented:
Well the dev edition isn't intended for prod use. So I don't think you can use it like that anyway. Might look into one of the free CF servers like Railo or Open BlueDragon, which are free for commercial use.

     > so its easy to have two perfectly exact applications running at the same time.

Yeah, but that's using a web application like a desktop application ;-) which kinda defeats the purpose. Ideally a web app should reside on one server, and be accessed from multiple browsers. So everyone shares the same information, regardless of where they're located.  With the right license you can "replicate" the app as you describe, but personally I wouldn't recommend it. Because it's not just the matter of merging, it's there's the added headache of handling duplicates, updating id's etc... IMO you're better off setting up one server and accessing it from multiple locations.
0
 
jameskaneAuthor Commented:
I had a niggling feeling along the lines you are speaking of. Best to follow your advise. Need a rethink and a licence if I am going to use that development machine in a serious production sense.

Thanks very much.
0
 
_agx_Commented:
Btw, if you want to get a quick feel for one of the alt engines, try Railo Express. You just download, unzip and click start.bat.
http://www.getrailo.org/index.cfm/documentation/installation/railo-express/
0
 
jameskaneAuthor Commented:
Thanks very much _agx_ will do
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now