Alpha Video Training
Results 1 to 24 of 24

Thread: Multiple users with multiple databases

  1. #1
    Member
    Real Name
    Jon
    Join Date
    Jan 2009
    Posts
    343

    Default Multiple users with multiple databases

    I would like to design a database application that can be used by multiple salespeople. A sales person would collect data from there various clients out in the field and would on an daily or weekly bases, somehow (preferably by email) send the data collected to a someone at the main office who in turn would then update this data to an existing database. The salesperson in the field would always have there information on there laptop, but the main computer would have information from several salespeople in it's database, and as individual data is collected, it is updated to the main computer's database. Can this be done?

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Multiple users with multiple databases

    as individual data is collected, it is updated to the main computer's database
    Do you mean in real time or as a batch process?

  3. #3
    Member
    Real Name
    Jon
    Join Date
    Jan 2009
    Posts
    343

    Default Re: Multiple users with multiple databases

    The salesperson would collect there individual data, then lets say every Sunday would submit there previous weeks data which in turn would be "added" to an existing database on a central computer. When the salesperson is in the field, they often have no access to the internet, so trying to login into a web application would next to impossible.

  4. #4
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,654

    Default Re: Multiple users with multiple databases

    Using xbasic (maybe Action Scripting?), create some code that converts the data into common a ascii text file. Also, create some code that reads the ascii and populates the db. That way your sales people can send it by email, no problemo.

  5. #5
    Member
    Real Name
    Jon
    Join Date
    Jan 2009
    Posts
    343

    Default Re: Multiple users with multiple databases

    Any example programs you can think of.

  6. #6
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Multiple users with multiple databases

    create some code that converts the data into common a ascii text file.
    This could be an export operation.

    Also, create some code that reads the ascii and populates the db
    This could be an import operation.

    The operations genie can design both for you.

  7. #7
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,034

    Default Re: Multiple users with multiple databases

    Jon,

    If you write the tables the same for the sales people as the main office. You can send a text email from the sales people to the main office and the main office canrecieve it, fill in the new form in the main database. there would need to be no interference from or very much time taken by anyone at the main office.

    Each customer info would be sent as a seperate email to one main email account. Same email account for all sales people.

    This works! I do something similar now, but not similar enough for you to use.

    This way the office is updated imediately if needed.

    I think this is what the others are trying to tell you.

    .
    Dave Mason
    dave@aldadesktop.com
    Skype is dave.mason46

  8. #8
    Member
    Real Name
    Jon
    Join Date
    Jan 2009
    Posts
    343

    Default Re: Multiple users with multiple databases

    Does anyone know of any examples for A5 that may be similar to what I'm trying to accomplish.

  9. #9
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,654

    Default Re: Multiple users with multiple databases

    Quote Originally Posted by Stan Mathews View Post
    ...The operations genie can design both for you.
    Hey Stan,

    You make it sound so easy. ;)

  10. #10
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,654

    Default Re: Multiple users with multiple databases

    Quote Originally Posted by jbk View Post
    Does anyone know of any examples for A5 that may be similar to what I'm trying to accomplish.
    Dude,

    Stan gave you the answer. Go to the operations tab of the control panel...

  11. #11
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: Multiple users with multiple databases

    In the Help file there is a whole page which explains about Email Automation.

    This should do just what you want.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  12. #12
    VAR
    Real Name
    Bill Parker
    Join Date
    Apr 2000
    Location
    Dallas, TX
    Posts
    1,734

    Default Re: Multiple users with multiple databases

    I also have a remote data entry feature to an application. As with Dave, it is so application specific that it would not be of use to anyone else.

    I would certainly keep thinking if there is a way to do this with a web app. If they are going to email the data, they have internet access. A cell based data cards will also give you internet access anywhere you can make a cell call.

    A web app solves many synchronization issues you can have with a batch process. Salespeople still have access to only their data. I would try hard to see if there is a way for a web app to work.

    Bill.

  13. #13
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,869

    Default Re: Multiple users with multiple databases

    If I were doing this I would create an "Update Database" for each salesperson. They'd have their sales database on their PC plus an empty Update Database. When they're ready, they'd push a button in thier app and any new records (flagged as new, or updated - because current info could change) could be copied to the update database. Each time the sale person goes to copy records to the update database, code checks to see if it's empty... it not the sales person get to choose to empty it.

    The Update database tables are added and dropped via code.

    Once updated, the update database could be emailed to the central office where a person would take that database, unzip it, put it in a "sales person" folder - to keep it unique - and run an update routine on the main app to add the tables from the update database, copy the records over, and drop the tables.

  14. #14
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: Multiple users with multiple databases

    Hi,

    I just realised, Jon said
    somehow (preferably by email)
    in which case the sales people do have access to the web.

    This then would be a web app without all of the issues of who does what and when.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  15. #15
    Member
    Real Name
    Jon
    Join Date
    Jan 2009
    Posts
    343

    Default Re: Multiple users with multiple databases

    Peter G, sorry for the inconvenience I have caused you. I'm obviously new at this and was simply asking for some "professional" direction.

  16. #16
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,654

    Default Re: Multiple users with multiple databases

    Quote Originally Posted by jbk View Post
    Peter G, sorry for the inconvenience I have caused you. I'm obviously new at this and was simply asking for some "professional" direction.
    It's not an inconvenience. With respect to my comment to Stan ("You make it sound so easy"). I was poking fun at myself - because, Stan gave you the best answer (at least I think he did) - direct & straightforward and better than my original generic suggestion. Just go to the controlpanel's operations tab and create a new operation. Obviously some trial and error and exploration is required on your part. Like Stan said, the Genie will guide you through it. I wasn't trying to give you a hard time.

  17. #17
    Member
    Real Name
    Jon
    Join Date
    Jan 2009
    Posts
    343

    Default Re: Multiple users with multiple databases

    David K, thank you for your response. I fully understand what and how to do what you have suggested. Instead of have "unique" databases for each salesperson on the main computer, I would like to have each sales person's data merged on a weekly bases to ONE database without interfering with previous data, therefore anyone on the main (office) computer can lookup a customer say by there city and all customers (no matter who the salesperson) would be displayed. The salesperson on there laptops would only have there data, but the office computer would have all 6 salespersons data. I would like to make it as easy as possible. A office worker may have to lookup a particular customer and may not know who the customer's sales rep is, this way they would simply type in the customer's business name and data would be displayed.

  18. #18
    Member
    Real Name
    Jon
    Join Date
    Jan 2009
    Posts
    343

    Default Re: Multiple users with multiple databases

    Peter G, thank you....

  19. #19
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: Multiple users with multiple databases

    Jon,

    Although a web based app may be the ideal, it seems you do not want to go down that road (and I am not sure I would either if your aim is simplicity and low cost). In that case, Stan's advise is what I would follow up on. If at the main office side you do an import to a just zapped temp file and then an append operation set to check for duplicates and replace existing records in your existing main office table, you would not even have to worry about whether you are sending just new records (unless file size gets too large, in which case just send the new ones determined by whatever). In this scenario the key part of what you need can be done with Genies in the operations tab.

    A bit more detail: The "just zapped temp file" can be an empty duplicate of your main file, probably without fields rules if the main file has any. Why not just import directly into the main table? Two reasons: 1) If there are problems in the middle of the import, there is no risk of messing up the main table; you can always zap the temp file and start over, no harm, no foul, and if data makes it into the temp duplicate of the main table, there is almost no chance of the subsequent append running into problems. 2) Appending from the temp file to the main table allows you to check for duplicates and update old records with new, updated data--you can't do this with a direct import.

    Virtually everything in this scenario can be done with either built-in operations (e.g., duplicating the main table), Genies in the Operations tab, and Action Scripting (e.g., zapping the temp and then running the import and append operations) on buttons of forms or even in an autoexec script. Very simple, easy to build and test and low cost. Which I suspect is what you are looking for!

    Ray Lyons

  20. #20
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,869

    Default Re: Multiple users with multiple databases

    Quote Originally Posted by jbk View Post
    David K, thank you for your response. I fully understand what and how to do what you have suggested. Instead of have "unique" databases for each salesperson on the main computer, I would like to have each sales person's data merged on a weekly bases to ONE database without interfering with previous data, therefore anyone on the main (office) computer can lookup a customer say by there city and all customers (no matter who the salesperson) would be displayed. The salesperson on there laptops would only have there data, but the office computer would have all 6 salespersons data. I would like to make it as easy as possible. A office worker may have to lookup a particular customer and may not know who the customer's sales rep is, this way they would simply type in the customer's business name and data would be displayed.
    Sorry... didn't mean that separate databases be kept at the Central office.

    "Update Databases" would be emailed to head office.
    A person would attach to the Update database, transfer the information, then drop the tables.
    The "Update" database would be stored in a folder unique to that salesperson, just to keep them separate from other people.

    The information from each salesperson would be transfered to the central database.

  21. #21
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: Multiple users with multiple databases

    Quote Originally Posted by Raymond Lyons View Post
    Jon,

    Although a web based app may be the ideal, it seems you do not want to go down that road (and I am not sure I would either if your aim is simplicity and low cost). In that case, Stan's advise is what I would follow up on. If at the main office side you do an import to a just zapped temp file and then an append operation set to check for duplicates and replace existing records in your existing main office table, you would not even have to worry about whether you are sending just new records (unless file size gets too large, in which case just send the new ones determined by whatever). In this scenario the key part of what you need can be done with Genies in the operations tab.

    A bit more detail: The "just zapped temp file" can be an empty duplicate of your main file, probably without fields rules if the main file has any. Why not just import directly into the main table? Two reasons: 1) If there are problems in the middle of the import, there is no risk of messing up the main table; you can always zap the temp file and start over, no harm, no foul, and if data makes it into the temp duplicate of the main table, there is almost no chance of the subsequent append running into problems. 2) Appending from the temp file to the main table allows you to check for duplicates and update old records with new, updated data--you can't do this with a direct import.

    Virtually everything in this scenario can be done with either built-in operations (e.g., duplicating the main table), Genies in the Operations tab, and Action Scripting (e.g., zapping the temp and then running the import and append operations) on buttons of forms or even in an autoexec script. Very simple, easy to build and test and low cost. Which I suspect is what you are looking for!

    Ray Lyons
    Lost my ability to edit the above, so let me add that I was talking about importing a text file (or maybe excel) on the presumption that for emailing a text file would be smaller (zipped or not) than a table, much less an entire database. In any case you may want to encrypt and/or password protect files sent as email (easy to do with zips, I believe) and of course tables can be encrypted in A5 (maybe text files can too?).

    But if you were to decide to use an A5 table, I would use the A5 table duplication ability to make it an exact duplicate of the main table (different name) and then you could skip importing to a "temp file" as described above and just run an append as described. Were it me, I would still import it into a temp file just in case the table gets fouled up in transit over the Internet (for the reason described in the above).

    Ray Lyons

  22. #22
    Member SNusa's Avatar
    Real Name
    Robert Tupper
    Join Date
    Dec 2007
    Location
    Northeast, USA
    Posts
    893

    Default Re: Multiple users with multiple databases

    Here's a neat idea....
    Why are you messing around with zipping, copying, sending etc...

    Why not just create the application to use native a5 database based tables while they are on the road to collect the data.

    But also have (in office) main active link tables (ie. MS sql Server) which can be connected to as needed through a simple append query? (Using the AlphaDAO connection along with the new features available in a5 v9.)

    Then all your sales guys have to do is connect the pc to the network (via cable or wireless) and press a button. "BAM" the data uploads (merges) into a single database in the main office.

    What you want to do sounds like a perfect fit for the new capabilities in a5 v9. If you want to go the free way (for the back-end database) I believe you can still use mySQL absolutely free. (instead of MS sql Server)

    As long as a5 doesn't try to connect to the back-end database (until the user asks for it) I would think this would be a neat solution that makes it simple for the sales guys to use!

    I'm kind of a rookie here with a5, thus additional feedback from "Certified AlphaHolics" would be a good idea.

    But I think you could put the whole update thing together with only a bit of active scripting, and probably no x-basic coding whatsoever.

    And as for the benefits... All that data sitting in an industry standard relational DB...
    Forget about obsolescence and connectivity issues!
    Last edited by SNusa; 02-26-2009 at 01:21 AM.
    Robert T. ~ "I enjoy manipulating data... just not my data."
    It's all about the "framework." (I suppose an "a5-induced" hard drive crash is now in order?)
    RELOADED: My current posting activity here merely represents a "Momentary Lapse Of Reason."

  23. #23
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: Multiple users with multiple databases

    Quote Originally Posted by SNusa View Post
    Here's a neat idea....
    Why are you messing around with zipping, copying, sending etc...

    Why not just create the application to use native a5 database based tables while they are on the road to collect the data.

    But also have (in office) main active link tables (ie. MS sql Server) which can be connected to as needed through a simple append query? (Using the AlphaDAO connection along with the new features available in a5 v9.)

    Then all your sales guys have to do is connect the pc to the network (via cable or wireless) and press a button. "BAM" the data uploads (merges) into a single database in the main office.

    What you want to do sounds like a perfect fit for the new capabilities in a5 v9. If you want to go the free way (for the back-end database) I believe you can still use mySQL absolutely free. (instead of MS sql Server)

    As long as a5 doesn't try to connect to the back-end database (until the user asks for it) I would think this would be a neat solution that makes it simple for the sales guys to use!

    I'm kind of a rookie here with a5, thus additional feedback from "Certified AlphaHolics" would be a good idea.

    But I think you could put the whole update thing together with only a bit of active scripting, and probably no x-basic coding whatsoever.

    And as for the benefits... All that data sitting in an industry standard relational DB...
    Forget about obsolescence and connectivity issues!
    While on the road I was assuming they would use an A5 Db using dbf tables. When you say "Then all your sales guys have to do is connect the pc to the network (via cable or wireless) and press a button. "BAM" the data uploads (merges) into a single database in the main office." it sounds like you mean when they come back into the main office--assuming they all do that. But if that is the case, you do not need to use anything but dbf tables--unless you just want to for some reason. The one button "BAM" thing is just as easily done with Genie based saved operations and a few simple Action Scripts--no Xbasic required at all. On the other hand, yes you could effectively do the same thing with an MS SQL or MySQL database and Alpha Five. But unless you are talking about huge files or some other ????, I don't think that is what I would recommend. And yes, I have done quite a bit of work with Alpha Five and MySQL, so it is not as if I am stuck with doing things the "old" based dbf way. By the way, I could be wrong, but this sounds like a commercial use and I do not believe it is legal to use MySQL for commercial purposes. And MS SQL is not exactly cheap.

    Raymond Lyons

  24. #24
    Member SNusa's Avatar
    Real Name
    Robert Tupper
    Join Date
    Dec 2007
    Location
    Northeast, USA
    Posts
    893

    Default Re: Multiple users with multiple databases

    Wasn't sure about the cost of mySQL in a business environment. I thought I remembered reading (years ago) when I was interested in possibly going down that road (which I didn't) that there was a free version (without support) for business use. I may have been wrong, and that may have changed also. (As mentioned above, I never went down the mySQL path due to it's limitations at the time.)

    The reason I was thinging about a relational DB such as sql Server is the way you can remotely connect to it (at least I think you can with a5) using the connection strings right over the internet. (ie: the sales rep is sitting at a coffee shop 1500 miles away, and minutes later, the data is current at the home office.

    I just thought that would be a neat client / server scenario where it is so easy and convenient to "merge" the data. Consequently, the users would be much more likely to "keep the back end up to date." And with multiple sales reps, I'd suspect that will be an issue.

    Sure, setting up tables in MS sql Server takes a bit more work. But for just basic tables, it's no more difficult today than MS Access was a decade ago. (IMHO) And in a business enviornment where multiple sales reps are involved, spending a few hundred bucks for MS Small Business Server (which includes MS sql Server) is chump change in the great scheme of things.

    Also, you are no longer exclusively limited to just working with a5, which obviously opens up endless possibilities. (Again, IMHO.)

    Regardless, it was just an idea. I've been watching the "training videos" and working with MS sql Server. I'm amazed at how easily a5 appears to connect (and work) with "the heavy hitters" in the relational DB world.

    This "fully functional" connectivity (via active links) is what finally got me on-board with a5 after all these years (back to 1996) of purchasing but only "tinkering" with the a4 - a5 product line. I strongly believe that this feature in a5 is key to the products future viability.
    Robert T. ~ "I enjoy manipulating data... just not my data."
    It's all about the "framework." (I suppose an "a5-induced" hard drive crash is now in order?)
    RELOADED: My current posting activity here merely represents a "Momentary Lapse Of Reason."

Similar Threads

  1. new to databases - multiple files at once?
    By Chris Lucas in forum Alpha Five Version 6
    Replies: 3
    Last Post: 09-29-2004, 04:12 AM
  2. multiple databases
    By lkatz1 in forum Alpha Five Version 5
    Replies: 14
    Last Post: 07-02-2004, 09:49 AM
  3. Multiple Databases/Reports
    By Robyne in forum Alpha Five Version 5
    Replies: 2
    Last Post: 07-19-2003, 02:14 PM
  4. multiple databases
    By tbrians in forum Alpha Five Version 4
    Replies: 3
    Last Post: 03-02-2001, 10:44 AM
  5. Multiple Databases
    By Arthur Casse in forum Alpha Five Version 4
    Replies: 2
    Last Post: 11-24-2000, 10:50 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
  •