Excel to SQL Server

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

"TZoner" <tzoner@.hotmail.com> wrote in message
news:3f1132e5$0$31925$afc38c87@.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.|||John

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

Thanks for your previous reply!

"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:3f12643a$0$15033$ed9e5944@.reading.news.pipex. net...
> 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
> "TZoner" <tzoner@.hotmail.com> wrote in message
> news:3f1132e5$0$31925$afc38c87@.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.|||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" <tzoner@.hotmail.com> wrote in message
news:3f1132e5$0$31925$afc38c87@.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.
>