[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2012-09-16
11
Medium Priority
?
545 Views
Last Modified: 2012-09-17
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
Comment
Question by:jameskane
  • 5
  • 3
  • 3
11 Comments
 
LVL 40

Expert Comment

by:als315
ID: 38402821
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
 

Author Comment

by:jameskane
ID: 38403081
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
 
LVL 40

Expert Comment

by:als315
ID: 38403293
Can you upload your DB (without data)? Only tables structure is important
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:jameskane
ID: 38403319
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
 
LVL 40

Assisted Solution

by:als315
als315 earned 400 total points
ID: 38403338
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
 
LVL 52

Expert Comment

by:_agx_
ID: 38403632
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
 

Author Comment

by:jameskane
ID: 38403647
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
 
LVL 52

Accepted Solution

by:
_agx_ earned 1600 total points
ID: 38403686
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
 

Author Comment

by:jameskane
ID: 38403709
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
 
LVL 52

Expert Comment

by:_agx_
ID: 38403733
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
 

Author Comment

by:jameskane
ID: 38404495
Thanks very much _agx_ will do
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question