RE: Moving data back and forth between Excel and SQL Server

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: K Dales (KDales_at_discussions.microsoft.com)
Date: 01/07/05 

Date: Fri, 7 Jan 2005 04:59:02 -0800

The import part is fairly easy, as you seem to have discovered. Are you
using MSQuery? Regardless, I think if you want two-way communication with
your database you should look into using ADO. It will give you the most
flexibility in importing and exporting the data.
The basics:
ADO proides an object model to let you work with data via SQL/ODBC. It can
recognize any data source Windows recognizes through the ODBC administrator.
The main objects you will work with are the connection object and the
recordset object. The connection establishes a connection with your
database, and you can pass it any SQL command you need to (either to retrieve
records, or to update/add records, or even to create or modify tables - all
assuming you have the proper rights). The recordset will contain the results
of a query and you can step through and modify the records, then update them
on the server if desired. Example:

Dim MyCn as ADODB.Connection
Dim MyRS as ADODB.Recordset

Set MyCn = New ADODB.Connection
MyCn.Open(ConnectString) ' Need to use the proper format connect string for
your db

Set MyRs = MyCn.Execute(SQLString) ' executes the SQL command contained in
SQLString

While Not MyRs.EOF ' loop through records until end of file
   For i = 1 to MyRs.Fields.Count ' loop through fields in each record
      ' Process fields here (e.g. write to Excel sheet, have user process
changes, read back into recordset)
   MyRs.Update ' updates recordset on server
   ' see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthupdate.asp for more info on updating records with ADO
   Next i
MyRs.MoveNext ' next record
Wend

That is only a brief outline, but gives an idea how you can use ADO. Hope
it helps. Here is the gateway to the MS support page for data access
technologies, including ADO:
http://msdn.microsoft.com/data/default.aspx

K Dales

"Robert Pfister" wrote:

> Hello all -
>
> This is what I need to do:
>
> Query an SQL server which imports data into Excel (this seems pretty
> straightforward), modify the data and post it back to the server such that
> any data modified in the Excel sheet overwrites the /old/ data on the server
> (I'm clueless). This ay, the next time I run the query, I see the updates.
> I have admin access to the server and the pc i'm using.
>
> I'm new to this stuff so any information and/or links to information would be
> appreciated. We're on Excel 97/2000.
>
> Kind regards,
>
> Bob
>
>

Relevant Pages

  • Re: Building a website using FrontPage as the front-end and C++ as the back-end.
    ... uses ASP for Server side stuff including interfacing with my Access Database ... I'm wondering if David shouldn't think about giving his Database ... an ADO interface so the rest of the world's software can 'talk' to it. ...
    (microsoft.public.frontpage.programming)
  • Re: Accessing A Large MS SQL Table Over ADO Components
    ... > If we want to display the data on a readonly grid wich component is the ... Because I had some performance loss when I set it up to Server ... > And also ADO Components are a good choice? ... Database design tips.. ...
    (borland.public.delphi.database.ado)
  • Re: Accessing A Large MS SQL Table Over ADO Components
    ... >> If we want to display the data on a readonly grid wich component is the ... >> And also ADO Components are a good choice? ... >> Server Performance issues? ... > Database design tips.. ...
    (borland.public.delphi.database.ado)
  • Re: What technology to use?
    ... "Dean Mitchell" wrote in message ... > I have to write the data access layer of our server application and am ... > in Borlands CppBuilder I am loathe to use the Borland Database Engine, ... ADO was just emerging around that time and I did ...
    (microsoft.public.data.ado)
  • Re: What technology to use?
    ... "Dean Mitchell" wrote in message ... > I have to write the data access layer of our server application and am ... > in Borlands CppBuilder I am loathe to use the Borland Database Engine, ... ADO was just emerging around that time and I did ...
    (microsoft.public.data.odbc)