Writing data to SQL server

[Jim]

Hello all, I have need to be able to:
- upon openning up an excel spreadsheet, read data from a SQL server to
populate an excel spreadsheet "form"
- allow the use to modify or add to the data
- be able to save the data back to the SQL server

Essentially, I need to be able to read and write excel data to and from a
SQL server using Excel VBA.

I've googled and can't find anything similar to what I've described yet I'm
sure someone has already done something similar and don' need to reinvent
the wheel.

Can anyone point me in the right direction or offer some suggestions?

Any help will be appreciated.

Thanks
Jim

 

[Guest]

This can be done and there are examples out there. First, though, this type
of application lends itself better to MS Access than to Excel, so if you have
a choice consider doing this in Access. But if you need to use Excel you can.

The best way to accomplish this is with ADO - ActiveX Data Objects. You
need to add a reference to it into your VBA project (Tools... References from
the VBA editor - you will find it under Microsoft ActiveX Data Objects). ADO
lets you connect to and query an external database (query includes both
looking up info and updating/appending info - as long as you have a
userID/password with the necessary permissions). Here is a general reference
for using ADO - there is a lot to it but the features you would need to
implement what you want to do are not terribly difficult
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adosql/adoprg01_1kwv.asp

Here is a more specific example of ADO used to retrieve and update data from
a SQL server database
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthupdatex.asp

For more examples, try a search on this site for the terms "ADO" or "SQL
server" - then try in general the MSDN Library for "ADO Connection", "ADO
Recordset", "ADO Update" - and then try a general Web search on any related
topics.

 

[Jim]

Unfortunately, I have to use Excel because I have a good deal of VBA
customization in the spreadsheet in the form of utilities and customization
that drives the form. I just need to be able to pull data from SQL server
and then push it back once done.

Thanks for the tips!
I'll check them out.

 

[Guest]

Jim,

If you were able to find a solution to your problem, I am experiencing the
exact same issue currently. I would love some help!

David