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

[K Dales]

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/de...prg01_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/de...mthupdatex.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.

--
- K Dales


"Jim" wrote:

> 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
>
>
>
>

[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.



"K Dales" <[email protected]> wrote in message
news:[email protected]...
> 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/de...prg01_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/de...mthupdatex.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.
>
> --
> - K Dales
>
>
> "Jim" wrote:
>
>> 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
>>
>>
>>
>>

[David]

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


"Jim" wrote:

> 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.
>
>
>
> "K Dales" <[email protected]> wrote in message
> news:[email protected]...
> > 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/de...prg01_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/de...mthupdatex.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.
> >
> > --
> > - K Dales
> >
> >
> > "Jim" wrote:
> >
> >> 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
> >>
> >>
> >>
> >>
>
>
>