I have an Excel worksheet with 4 columns:
F1 F2 F3 AutoNo
A Y C 1
G C D 2
S W A 3


I have a table in SQL Server 2000 which corresponds to the above worksheet.
What's the best way to update columns F1, F2, F3 in the table using the
AutoNo from both the table and worksheet?

Thanks for any replies using ADO/VB/SQL and not DTS.

Hi

I would expect either the autonumber in the spreadsheet or the autonumber in
the database to be the master, otherwise you will probably end up with
miss-matching records.

If you use a liked server you can update/query both. If the SQL Server table
has an identity that you want to force, then SET IDENTITY_INSERT ON.

John

[quoted text, click to view]

John

Real issue I have is how do I get data from Excel into SQL the fasted
possible way?

Thanks for your previous reply!








[quoted text, click to view]

I concur, a linked server gets your data in SQL fastest. From there you
just use SQL commands to join and update the tables.
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\myexcelfile.xls',
NULL,
'Excel 5.0'
GO
SELECT * FROM ExcelSource...MyNamedRange
GO

If you are working with small tables that get dumped to Excel for an
employee to update (say prices) here is one way to make the update happen
immediately. It essentially binds the spreadsheet to the server table and
uses optomistic locking.

Matthew Martin

Dim con As ADODB.Connection

Private Sub Worksheet_Activate()
Set con = New ADODB.Connection
con.Provider = "sqloledb"
con.Properties("Data Source").Value = "MARIA" ' Your server name here
con.Properties("Initial Catalog").Value = "MyDB" ' your DB name here
con.Properties("Integrated Security").Value = "SSPI"
con.Open
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
' Ensure we have a row ID & column name
' AutoNum is in column 1, where primary keys should be.
If Target.Row > 1 _
And Worksheets(1).Cells(1, Target.Row).Text <> "" _
And Worksheets(1).Cells(Target.Column, 1).Text <> "" Then
If con Is Nothing Then
Worksheet_Activate
End If

con.Execute "UPDATE tblExportSQL " & _
"SET " & Worksheets(1).Cells(1, Target.Column).Text & " = '" & _
Target.Text & "' WHERE AutoNo = " & Worksheets(1).Cells(Target.Row,
1).Text

End If
End Sub

Private Sub Worksheet_Deactivate()
con.Close
Set con = Nothing
End Sub



[quoted text, click to view]