Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    need web based & local db - which way to go?

    Hi,

    I've got a new client who has an Access db to keep data on members of a society. Right now it is local. People actually print a form from a web site, fill it out, fax it in, and all the data gets copied by hand into the database.

    The database is currently a mix of code and tables, which will be separated. It's not a very complicated db, but there are a few one to many relationships.

    What I want to eventually have is a copy of just the tables on the web server with a form that populates records. Then once a day or so, the secretary would download the server based db, append to the tables in the local db and empty the server based db.

    Having 2 copies of the db sounds risky, but I don't know of any other way. We need to keep the local db as it is used for many functions in the office. I just need the web based db for new members to enter their data.

    What are your thoughts on this?

    Do I necessarily have to have an Access db on the server? Does anyone have any experience pumping data from a MySql db to Access?

    I would appreciate any help you can give on this.

    Thanks!

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Create a local database that contains your core tables required for general operations & data management. Within this database create the tables that you require to manage your form feedback. Next, create the web application to allow customers to send their data to your database. Following you have many options that can range from email's alerting your staff when new feedback is recieved to an application that handles this information and executes the appropiate actions you require.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    Thanks for your reply. Sorry, but I'm missing the part on how the data gets transferred from the web to the local db.

    I don't need to create the local db, it already exists. I just need to separate logic from table structure. I planned to have an exact copy of the table structure on the web just to hold the data until it gets transferred from the web server to the local db. This is the step I am missing - the transfer of the data. The web based db will just be a temporary container for the data until it gets copied over to the local db. Then the web based server needs to be emptied. I guess I might need some sort of flag to note that a record has indeed been copied before I delete. Or maybe all this can be done in a transaction.
    Last edited by wbrook; 01-27-04 at 06:08.

  4. #4
    Join Date
    Jan 2004
    Posts
    2
    If you retain the table structure in the web based DB, you can probably get a SQL dump of the table, then import the generated SQL into the Access DB. Easy to do if you use MySQL on the web, I think phpMyAdmin does the job (i need to check)

    I would attempt to use one single database for everything though. Maybe you can put that local DB on the server and do some web programming, for potential members to add their details to the DB directly instead of someone having to do it by hand.
    Last edited by synth; 01-27-04 at 06:26.

  5. #5
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I would recommend a single database on the local side, that contains the tables required for the form feedback. These tables are accessed from the web application through user profiles meaning that users to form only have access to the form related tables within the local database. You can then create triggers that execute specific actions once information has been inserted into these tables. Such actions may involve the notification of appropiate staff members who may then examine the data. Should the data be acceptable the user can activate a function to manage the accepted information and delete the corresponding records from the form related tables.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  6. #6
    Join Date
    Jan 2004
    Posts
    8
    Thanks everyone.

    r123456 - I still don't understand how you propose to move data from a web based form to a local db.

    synth - unfortunately, having a server only db isn't an option. They use the local db constantly and their internet connection is dial-up which costs by the minute here. They also could not tie up the phone line all day in order to stay connected to the server based db.

    One thing I was thinking would be to have the form just create SQL insert statements. That way, the office staff could just download the sql files and run them in Access to insert new records. The problem with this is that there are some look-up tables that I would need use to populate fields on the web form.

    Something that I did not spell out but probably became clear from this post is that I don't need to visualize the db on the web, just need a way to get data from a form to a local db. Even a csv file would be OK but again, I have this problem of lookup tables.

    Any other ideas?
    Last edited by wbrook; 01-27-04 at 06:43.

  7. #7
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    The web form connects to the local database and once submitted inserts the form data directly into the local database tables. There is only one database. If, however you still want to have two databases you can easily transfer table data from one database to another from within a macro.
    Last edited by r123456; 01-27-04 at 06:51.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  8. #8
    Join Date
    Jan 2004
    Posts
    8
    r123456 - I hate to keep going round and round on this, but I'm afraid I'm missing something. How would the web based form connect to the local db which is, well, local and off-line. Could you please explain?

  9. #9
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    I assumed your local database would be made availible online.

    To transfer data from one access database to another the TransferDatabase Action can be executed from within a macro.
    Last edited by r123456; 01-27-04 at 07:16.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  10. #10
    Join Date
    Jan 2004
    Posts
    8
    OK, that explains it. I was hoping for some magic I didn't know about...

    I guess my only choice, considering the circumstances, is to have a copy of the tables on the server and try to make it as easy as possible to download the data.

    Thanks.

Posting Permissions

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