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
"TZoner" <tz****@hotmail.com> wrote in message
news:3f***********************@news.optusnet.com.a u...
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.
