Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2009
    Posts
    13

    Unanswered: Update DB tables from physical and remote locations

    Hello all,

    I'm in the midst of creating an access db for my company. The access DB will be used by my agents to keep track of our customers, who to call back, who hasnt paid ect.. Our customer database will be kept at a remote location. I want access to update specific tables automatically at start based on the information on the remote locations DB. This way i can manage accounts by removing them customers, replacing some and so on per agent. And when an agent logs in, his access DB/Table is up to date. Now what i also want is to have the remote locations database to up date while my agent enters in new information like notes, invoice status... How would i do this?

    Heres what i'm thinking of doing:
    Create an autoexec macro that links data from a sharepoint site to a specific table. That way my agents are always up to date with current customers.

    Problem:
    If my agents add new information about a client, and closes the access file, next time he opens the file the new information he entered last time will be overwritten with the information from the sharepoint site.

    Another question: What is the best way to link a table to a remote location? I'm thinking sharepoint site.

    Note: I have no experience with SQL databases, and very minimal experience with access(Have read books and video trainings).

  2. #2
    Join Date
    Mar 2009
    Posts
    13
    Sorry for the bump, but i really need to know how to do this or if it is even possible. Any help would go a long way, thanks

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I haven't used Sharepoint yet, but for remote location access I use MySQL or SQL Server tables and the application just links to that.
    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

  4. #4
    Join Date
    Mar 2009
    Posts
    13
    Quote Originally Posted by StarTrekker
    I haven't used Sharepoint yet, but for remote location access I use MySQL or SQL Server tables and the application just links to that.
    Hey thanks for the input. In you case, do the tables in both access and sql update frequently? Lets say im in access and i make a change to customer B. Will that change be shown in the sql table instantly? If not what command do i need to execute to update the sql database with the new data?

  5. #5
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    If you need to update the table remotely, SQL Server is a MUCH better choice for your database 'back end.'

    When you're got an access front end, linked to a SQL Server back end, you make a change on the access linked 'table' from the remote site, and the change shows up in the live database table on the server immediately.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agreed a central (server) db is the best way to go

    if a server isn't an option then you need to find a way of making each remote site have a unique ID across the whole application so sharepoint update doesn't overwrite data. that may involve something like siteID adding to the primary key.

    you then need to find a way of the local sites posting central data to the central db, and after making that post resynchronise the local sites with the consolidated data.... nah it just sounds plain messy to me.. if you cna go the central server route
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    13
    The only reason why i shun away from using and SQL database is because i am a complete noob in regards to SQL. Sharepoint however, looks more user friendly. How long would it take me to learn and incorporate an sql database for my backend? It is not going too be a complex Database, probably only going to have 5-6 linked tables(Per Agent). Can i import a excel/csv file into microsoft SQL? is it user friendly at all?

    Thanks for the informative posts, real good information for me

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    In case you didn't know: Sharepoint runs off of a SQL Server database
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Time to get your hands dirty with SQL by the sound of it
    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

  10. #10
    Join Date
    Mar 2009
    Posts
    13
    Oh well, gives me an excuse to learn something new i guess
    Any links to mssql for beginners

  11. #11
    Join Date
    Sep 2006
    Posts
    265
    You could avoid this aggro with a Terminal Server at your site and VPN security.

    Simon

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Agreed... Terminal Services are cool
    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
    Mar 2009
    Posts
    13
    So after 1 day or so of looking into sql.. i think im just going to link my agents tables to another managed server access table. It seems like the easiest setup and does what im looking for. SQL is like chinese to me, i don't feel like learning chinese now, but maybe in the near future.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I didn't find it *that* foreign to Access tables... it was going to unbound forms that made me go a little greyer.
    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

Posting Permissions

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