Moving data back and forth between Excel and SQL Server

[Robert Pfister]

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

 

[onedaywhen]

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

For me, this is the great unanswered Excel question i.e. once you have
imported data to an Excel workbook, how do you detect which rows have
been amended/inserted/deleted so that you can update the source?

The only sensible answer I can give is that you must use a key
column(s) and do a post-edit comparison e.g. by creating JOINs between
the Excel data and the source data. I contributed to a thread a while
ago about comparing changes between two workbooks (search google groups
for the exact phrase "basic 'template' for comparing"). The same
'template' could be used with the Jet driver to compare a workbook with
an odbc/oledb data source e.g. SQL Server.

An alternative view is that an Excel worksheet is unsuitable because of
the unsupervised nature of its editing functionality e.g. row
continuity can easily (and inadvertently) by destroyed by shifting
cells down by when inserting a single cell. In order to assert more
control over editing, I prefer to present the data in a grid on a
userform bound to a disconnected ADO recordset.

Jamie.

--

 

[Guest]

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]

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

Thank you both for your suggestions. I'm well on my way now.

Regards,

Bob