Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    May 2009
    Posts
    8

    Unanswered: Synchronizing two databases? Or tables?

    Hello! I am new to access so i apologize if my questions are silly right off the bat.

    I am creating a very simple database for a customer that includes a job board for the staff to view. When a job is complete, the owner wishes to be able to remove the job from view (the row), so it doesnt clutter up the table. But, he wants to have records of all the jobs, complete or not, back up somewhere for his records.

    So can we sync two tables or databases together so when you add a new job, it gets added to the other table or db, and when you delete a complete job it only deletes it on one of them??

    I hope that makes sense... Thanks in advance for any help here, I am open to all options to accommodate this request!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    The more recommended solution is to keep the data in one table, but add a "Completed" or "Status" field. When a job is complete, you flag it as such. The form the users access the table through can be filtered to exclude completed jobs. Leaving them in one table lets you get completed jobs, uncompleted jobs, or both very easily.
    Paul

  3. #3
    Join Date
    May 2009
    Posts
    8
    thank you pbaldy. in this case, the users wont be using forms. they will just be sharing the table on its own... is there a way to filter data shown right on the table?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    the users wont be using forms. they will just be sharing the table on its own...
    Good luck with that.

    Best advice I can give you is make one.

    is there a way to filter data shown right on the table?
    Yes. What version of Access do you have?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by StarTrekker
    Good luck with that.

    Best advice I can give you is make one.
    Why? It seems to work great this way..


    Quote Originally Posted by StarTrekker
    Yes. What version of Access do you have?
    I have 2007.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You have virtually no control with users directly in tables, and no events to work with. I never let users directly into tables. They work with forms and reports only.
    Paul

  7. #7
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by pbaldy
    You have virtually no control with users directly in tables, and no events to work with. I never let users directly into tables. They work with forms and reports only.
    theres only 4 users, and everyones trusted. we have no need for events.

    basically just a shared job board...

    back on topic though!! i need to know if my original question is posible.

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    It's not a matter of trust. In any case, I don't believe your original request is possible. As I mentioned, there are no events associated with tables, and Access does not have triggers, so there's really no way to maintain 2 tables by making entries in one. As mentioned, you can filter out the completed jobs in a table that contains them all, but you'll have to let the user do it, because again, you have no control over the user in a table.

    I would create a form. I have a database that's only for my use, and I still work through a form rather than directly in a table. It's for my company's PC inventory. I click on a checkbox to see all computers, click again to only see those currently in use. Control isn't about trust, it's about controlling the flow of the application.
    Paul

  9. #9
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by pbaldy
    It's not a matter of trust. In any case, I don't believe your original request is possible. As I mentioned, there are no events associated with tables, and Access does not have triggers, so there's really no way to maintain 2 tables by making entries in one. As mentioned, you can filter out the completed jobs in a table that contains them all, but you'll have to let the user do it, because again, you have no control over the user in a table.

    I would create a form. I have a database that's only for my use, and I still work through a form rather than directly in a table. It's for my company's PC inventory. I click on a checkbox to see all computers, click again to only see those currently in use. Control isn't about trust, it's about controlling the flow of the application.
    Thanks mate. I figured it out, I just made a column drop down box named "complete" and choose yes or no... then made a toggle filter for it so you can toggle to hid all jobs under "yes" for complete. works exactly as we wanted.

    Ya I must admit, maybe because I am new, but I dont see the advantage of using a form. I mean, why not just enter the data in the table just like an excel table? why bother with forms?

  10. #10
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'll take it one further. If you're going to use an Access table like an Excel spreadsheet, why not just use Excel? They are different animals, made to be used in different ways. You have a hammer and a screwdriver. Both are good tools, but best for different tasks. You're trying to hammer in a nail with the butt end of a screwdriver. You can make it work, but you're not using the tool for the task it was designed for.
    Paul

  11. #11
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by pbaldy
    I'll take it one further. If you're going to use an Access table like an Excel spreadsheet, why not just use Excel? They are different animals, made to be used in different ways. You have a hammer and a screwdriver. Both are good tools, but best for different tasks. You're trying to hammer in a nail with the butt end of a screwdriver. You can make it work, but you're not using the tool for the task it was designed for.
    Because it needs to be shared between 4 people that may be updating information at the same time.

    As far as I can tell, a form is just a graphical way to enter information... everyone is used to using excel so why not use access table like excel? what am i missing?

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    why bother with forms?
    Control.
    User-Friendliness.
    Control.
    Looks.
    Control.
    Freedom.
    Control.

    Did I mention control?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by StarTrekker
    Control.
    User-Friendliness.
    Control.
    Looks.
    Control.
    Freedom.
    Control.

    Did I mention control?
    User-Friendliness. - editing tables couldnt be simpler
    Control. - control of what? whats going to happen?
    Looks. - irrevelevant
    Freedom. - what?

    you guys seem hell bent on these forms, stating "control" over and over again. yet nobody has any valid reasons to use it, unless your letting children enter data and a table is too confusing for them...

  14. #14
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    One reason a person uses MSAccess is to design an interface between the user and the tables. Letting users enter data directly into the tables can lead to sloppy data (ie. Madison being entered for the City is spelled 10 different ways) and you can't run events based upon values or qc that all the requried values are entered. There's a lot of other reasons for using a form you'll discover as you start designing forms. While it's true that a lot of users are adapt at entering data into Excel, entering that data into an MSAccess table won't be the same and often leads to frustration for the user. An MSAccess table makes for a poor excel type entry system (you can't navigate as freely and format "cells", etc.. in MSAccess as you can easily do in excel.) Hence the forms. If you're using MSAccess tables to enter the data, why not simply use Excel instead? (I mean you can also link that excel sheet into the MSAccess mdb file for your queries, reports, etc...)

    Now given if you're just working in 1 MSAccess table, the temptation to forego the form and just enter data directly into the table is promising. After all, there are no limitations that prevent you from doing this and you don't have to worry about related records. But it also takes 30 seconds (using the wizard) to develop a nice easy to use form, so why not setup one up? You'll find that as you work with designing the form, you'll learn about all kinds of neat things you can do on the form (ie. listboxes, comboboxes, coloring of fields depending on values, tab order of fields, etc...etc...) Plus, with a form you now have the added luxury of adding in buttons to do almost anything and other neat stuff to make data entry/editing twice as fast versus editing a table (a form gives the user a customized interface where they can associate colors and shapes, field placements, and other type of tracking (for example, wouldn't it be nice to know who entered the record and when, or calculate certain values automatically, or check spelling, or have certain values turn colors - you can do this via a form but not when the user is editing data directly in the table.) And finally, you can make sure the values entered meet whatever standard you set as well as manipulate other data (ie. the value for FieldX can be only 1 of 3 values or set FieldZ = FieldX + FieldY.) (Plus I probably missed a bunch of other reasons other than those previously posted.)

    Keep in mind that even though 4 people may be working on this. I'll bet that all 4 people have some differences on what data gets entered into a field. You may also get a "locked by another user" error if 1 user has the mdb (and table) open for hours (without doing anything) and then another user tries to open the mdb. Something to test versus have it come up as a surprise. A form for example could be setup to close after no activity after so many minutes (or close the mdb file) and prevent the error. This is the one of the most often got error with multiple users in the same mdb file.

    Control is a good thing to have as it "guides" the user in the right direction for data editing. It makes everything easier for them and is actually the funnest part of development (for you.) You get to "create" something others will use and either find more difficult to enter data or easier to enter data. But again, you control that flow between what they enter and what's allowed to be saved into the tables to keep all your data nice and clean and that is the key on why you should design a form.

    Forms will prevent the painful (and often slow with mass records) "Data Cleaning" process which usually needs to be done on direct-table entries. It's rare to see 4 people enter data in exactly the same manner for every single field. But data-cleaning should seriously be factored into the beginning of any design project as it can become a several month type process to clean missing/incorrect pieces of data. Any prospect of data-cleaning probabilities (the need to clean data) should be eliminated BEFORE any data entry is done on the system, regardless of how that data is entered.
    Last edited by pkstormy; 05-28-09 at 01:04.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  15. #15
    Join Date
    May 2009
    Posts
    8
    Quote Originally Posted by pkstormy
    ...(ie. listboxes, comboboxes, coloring of fields depending on values, tab order of fields, etc...etc...) Plus, with a form you now have the added luxury of adding in buttons to do almost anything and other neat stuff to make data entry/editing twice as fast versus editing a table (a form gives the user a customized interface where they can associate colors and shapes, field placements, and other type of tracking (for example, wouldn't it be nice to know who entered the record and when, or calculate certain values automatically, or check spelling, or have certain values turn colors - you can do this via a form but not when the user is editing data directly in the table.)
    thank you very much for the write up on that. I do have drop boxes and comboboxes right in the table, so i dont think users entering incorrect data will be a problem.

    the only thing that catches my eye as being a reason to add a form in this case is the ability to associate colors to certain fields.

Posting Permissions

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