Results 1 to 6 of 6

Can you have a spreadsheet sync with a database

This is a discussion on Can you have a spreadsheet sync with a database within the Microsoft Access forums, part of the Question Forums category; I am organising a small part of the data at a computer help charity and I'd really like them to ...

  1. #1
    New Member
    Join Date
    Jan 2010
    Posts
    28

    Default Can you have a spreadsheet sync with a database

    I am organising a small part of the data at a computer help charity and I'd really like them to be properly organised which is only on diffuse spreadsheets at the moment.
    Basically, I think for our data a database would be best however for other people they may scared by a database or some computers like laptops may not have a licence for Access so I'd like the data to able to enterred on a spreadsheet which may even be on Excel 2003 rather than the 2007 at the main work area and that is able to transfer semi-automatically that is at some time my boss or other senior person transfers the data but without much work. Is that doable?

  2. #2
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,198

    Default Re: Can you have a spreadsheet sync with a database

    It depends on your skill level. It can be done in code, and you only need one Access license (to build and maintain the database). The users on Excel do not need Access at all.
    Take a look at this tutorial and post back if you have questions.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  3. #3
    New Member
    Join Date
    Jan 2010
    Posts
    28

    Default Re: Can you have a spreadsheet sync with a database

    Just to clarify those Excel 2003 and 2007 are fine for this technique.

  4. #4
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,198

    Default Re: Can you have a spreadsheet sync with a database

    Absolutely. But I would use an Access file in the mdb format (not the newer accdb format) to make sure that both Excel versions can use it.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

  5. #5
    New Member
    Join Date
    Feb 2009
    Posts
    17

    Default Re: Can you have a spreadsheet sync with a database

    I'm need to do something similar to this, but I am leaning towards using SQL Server instead of Access as a back end. Are there any similar tutorials out there that does the same thing with SQL Server?

    What about tools that would help make this easier (instead of having to write code from scratch). Would SSIS help me out (never used, only briefly read about)? Thanks.

  6. #6
    MrExcel MVP
    Moderator
    SydneyGeek's Avatar
    Join Date
    Aug 2003
    Location
    Sydney, Australia
    Posts
    12,198

    Default Re: Can you have a spreadsheet sync with a database

    I don't have it with me right now but the main difference is the connection string, including login and password. If you search for ADO connection string you should get a huge page with a range of different ones.
    I'll see if I can dig up anything at home this evening.

    Denis
    Self-preservation: For when you've got yourself in a jam
    ------------------------------------------------------
    My site contains a number of Excel and Access Resources

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com