Alpha DevCon 2018
Results 1 to 12 of 12

Thread: external database

  1. #1
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    650

    Default external database

    I have 5 similar databases (except for data).
    Each database has a table named CONSULTANTS.DBF

    I want to create a 6th database with only the combined data from
    the 5 CONSULTANTS tables.

    When a consultant record is added / deleted / edited in one of the 5
    databases I want the change to be propagated to the new 6th database.

    How can i accomplish this??

  2. #2
    "Certified" Alphaholic Ray in Capetown's Avatar
    Real Name
    Ray Hendler
    Join Date
    Jan 2009
    Location
    South Africa
    Posts
    2,036

    Default Re: external database

    If dealing additions only then I would use "last record number updated" register for each of the five tables to append later additions.
    However, for possible Added/Deleted/edited - the time taken to check every record to update would be outweighed by a clear and append all which is very fast.
    Assuming on a local network.

  3. #3
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Re: external database

    My, what complicated webs we weave.

    Is each database in its own (separate) folder?

    Will the 6th database be in its own (separate) folder?

    We are talking databases here, right? i.e. not tables, right?

  4. #4
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    650

    Default Re: external database

    Thanks Ray...

    Tom:
    Each database is in it's own folder (~90 tables each).... The 6th database will consist of one or two tables, mainly the CONSULTANTS table. It will also exist in it's own folder.
    All folders are on a fileserver which is accessed over a LAN.

    I took a look at Active-Link and Mapped tables... but I'm not sure if they will be of any use.
    I also tried attaching the new table to multiple databases but Alpha does not seem happy with this.

  5. #5
    "Certified" Alphaholic
    Real Name
    Tom Cone Jr
    Join Date
    Apr 2000
    Location
    Florida
    Posts
    23,299

    Default Re: external database

    Does the 6th database have to be available to all the workstations?

  6. #6
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,408

    Default Re: external database

    If your 6th table contains all the data from the other 5, I would think it could provide a lookup link to the other databases. But I would not try to do data entry into that table directly. Instead you could add new consultants to the local table then append to the 6th.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  7. #7
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    650

    Default Re: external database

    Quote Originally Posted by Tom Cone Jr View Post
    Does the 6th database have to be available to all the workstations?
    Well... that would be nice... but I guess that what I am trying to discover is what the best way to accomplish this would be.
    The main reasons for doing this is 1) because I run a series of 10 - 12 complex scripts that operate on the Consultants table 2) my client insists that individual databases remain separate.

    The Consultants tables in the 5 production databases contain different data. Consultants in a particular country are tracked in each database.
    BUT
    Consultants in one country can 'sponsor' consultants in other countries. The earn $ from the sales of these consultants. So...once per month I would like to export (or by some means) combine all of the active consultants in each country (along with their sales totals) into a 6th ('master-consultant') database and run the scripts from the 6th database.

  8. #8
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,894

    Default Re: external database

    Maybe I am not fully understanding all this, but:
    Clarifying for myself here:
    5 identical adb's? and 5 set of identical tables? if so is there a defining field in those tables that identify that table record as belonging to the database it is in?
    If so, an append x 5 could be accomplished on each table and still have the records separated by the identifying field. Then run the scripts you need and zap what is left so all is empty for the next month.

    I may be way off on this, but is a rudimentary idea.

    I am sure the client has a reason for wanting the databases set separate and it usually come from some bad experience in life about the same, but I'm sure you are getting paid well for all the extra work the client has caused.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  9. #9
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    650

    Default Re: external database

    Thanks DaveM

    Your approach is probably a good working solution.
    I created a new field in the Consultants table in each of the databases. This field is a calculated field and concatenates the current auto-increment key field with a prefix like USA-, CAN-, ITA- etc.
    This allows (as you pointed out) the identification of each record with its associated database.
    My plan is to prompt the user for the location of the international database... duplicate the table, naming it ConsultantsUSA, ConsultantsCDN etc... pack the table... drop the table... copy to the destination folder...
    open the international database... add the table(s)... append each one... drop the tables.
    Does this seem reasonable to you?

    BTW...
    'I am sure the client has a reason for wanting the databases set separate and it usually come from some bad experience in life about the same, but I'm sure you are getting paid well for all the extra work the client has caused'...
    very insightful... yes there is a reason... (a questionable one... but nevertheless a reason)... 'well paid'... hmmm... how does about $8/hr sound to you?? I started this 12 years ago for a friend... ever heard of 'scope-creep'???

  10. #10
    Member
    Real Name
    David Boomer
    Join Date
    Apr 2000
    Location
    Brampton Ontario, Canada
    Posts
    650

    Default Re: external database

    Tom
    no... the 6th database willl be available to one person on one workstation.

  11. #11
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    5,894

    Default Re: external database

    The approach to adding the field was not quite what I had in mind. 1 field in each table numeric 3 with only one number in it like first data base as a 1 for every record, db 2 has a 2 for every record, db 3 has a 3 for.....

    Leaves little possibility for a mess up.

    for later each record in any db would have a number placed in that field 1,2,3,4,or 5 depending on the db.

    But, if you have a failsafe way??? OK


    db 6 just sees the number in any table and knows where the info came from.
    Dave Mason
    dave@aldaweb.com

    Skype is dave.mason46

  12. #12
    Member
    Join Date
    Dec 2014
    Posts
    2

    Default external dat

    Thank you for your reply Oliver.
    Until now i actually used external scripts to pull data into the CP.

    But if i do understand you creect, it would be possible to create my own CP with de required fileds allready included ?
    If that is right, i will give it a try for sure.

Similar Threads

  1. Can't update date field in external database
    By Don McElroy in forum Alpha Five Version 10 - Desktop Applications
    Replies: 0
    Last Post: 11-05-2010, 03:37 PM
  2. Connection String and Loading an External Database.
    By Newander in forum Alpha Five Version 9 - Desktop Applications
    Replies: 21
    Last Post: 06-01-2008, 10:42 PM
  3. Pushing data to an external database
    By om21 in forum Alpha Five Version 7
    Replies: 1
    Last Post: 05-14-2007, 10:22 PM
  4. Get External shadow database's master adb
    By csda1 in forum Code Archive
    Replies: 3
    Last Post: 03-13-2006, 01:13 AM
  5. Database Access from External Application
    By Red Abicht in forum Alpha Five Version 4
    Replies: 1
    Last Post: 04-22-2004, 05:32 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •