How to write back a SQL Server table from Excel

[Belinda]

I want to read a SQL Server table into Excel and then make changes to
it and then update the Excel range back to SQL Server. Also, I want to
be able to add new rows or delete rows in a range and reflect back the
same into SQL Server. In simple words I want to do the same thing what
Access Link table does from SQL Server. One of the main problems I
face is how to identify the range the query populated in Excel ?

Then how to track what rows were deleted and what were inserted newly
and what was updated.

If any of you have a VBA / t-SQL code for this it is highly
appreciated.

Thanks
Belinda

 

[Joe]

Belinda

Why not use Access linked tables

In Excel VBA, use ADO to connect to the tables in the SQL Server database. Populate your worksheets (or whateevr) using the recordsets that you create from the DB via the ADO connection object. It isn't hard

Joe

 

[Roger Whitehead]

Belinda, This little lot updates from Excel to the Source
RST1_ADO is an ADO.Recordset
It uses the same SQL string to UPDATE, as the Query that SELECTED the data
into Excel. I colour-fill cells that require update, in a vain attempt to
save time.

----------------------------------
For r = 2 To lastRow Step 1
Application.StatusBar = "Editing Record " & r - 1 & " of " &
lastRow - 1
Debug.Print "Editing Record " & r - 1 & " of " & lastRow - 1

'trim the data, make sure that additional rows added since the
original SELECT query are not updated-

trmfld = Trim(RST1_ADO.Fields(iPK - 1))
'If Not IsNull(trmfld) Then
If Not IsNumeric(trmfld) Or Left(trmfld, 1) = 0 Then
btrmnum = False
Else
btrmnum = True
trmfld = CLng(trmfld)
End If
'End If

Debug.Print "'" & trmfld & "' - '" & ws.Cells(r, iPK) & "'"

If btrmnum = True Then
Do While trmfld <> CLng(ws.Cells(r, iPK))
RST1_ADO.MoveNext
trmfld = CLng(Trim(RST1_ADO.Fields(iPK - 1)))
Loop
Else
'strange but true, the primary key MAY be empty!:
' If Not IsEmpty(trmfld) And Ws.Cells(r, iPK) <> "" Then
Do While trmfld <> ws.Cells(r, iPK)
RST1_ADO.MoveNext
Debug.Print RST1_ADO.Fields(0) & ":" &
RST1_ADO.Fields(1) & ":" & RST1_ADO.Fields(2)
trmfld = Trim(RST1_ADO.Fields(iPK - 1))
Loop
' End If

Debug.Print RST1_ADO.Fields(0) & ":" & RST1_ADO.Fields(1) & ":" &
RST1_ADO.Fields(2)
End If

Debug.Print "'" & trmfld & "' - '" & ws.Cells(r, iPK) & "'"

'\>Primary Key Check End
'rst1_ado.Edit

For C = iPK + 1 To lastCol Step 1

' bEditRec = False
'Only update colour-filled cells-
If Cells(r, C).Interior.ColorIndex <> xlNone Then
RST1_ADO.Edit
Debug.Print "Updating RST: " & RST1_ADO.Fields(C - 1) &
" - WS: " & ws.Cells(r, C)
RST1_ADO.Fields(C - 1) = ws.Cells(r, C)
bEditRec = True
'----------------------------Where more than one Table
in query, Needs Update after each .Edit command:
RST1_ADO.update dbUpdateCurrentRecord
'----------------------------
End If

Next C
If bEditRec = True Then
'rst1_ado.update dbUpdateCurrentRecord
bEditRec = False
bAdded = False
End If
RST1_ADO.MoveNext

Next r
----------------------------------
USE THIS ON TEST DATA ONLY until you're confident.

I dont use this for Deleting rows - look at OLH for .Execute, and run that
command based on a DELETE query, eg:

strSQL = "DELETE FROM myTable Where CustNo = '999999'"
..execute strSQL


--
HTH
Roger
Shaftesbury (UK)

 

[onedaywhen]

I've often seen this question posed here and I've yet to see a good
generalized answer. After a quick glance at your post, my opinion
hasn't changed.

Can you explain how you have anticipated the OP using your code? There
seems to be so many premises (the variable iPK, only one column may
comprise the primary key, datatypes, etc) I don't think it can be
generalized for the OP's needs. Please, please, prove me wrong.