Can you have a spreadsheet sync with a database

[cloa]

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?

 

[SydneyGeek]

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

 

[cloa]

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

 

[SydneyGeek]

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

 

[engelbmj]

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.

 

[SydneyGeek]

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