Excell 2007 - SQL 2008R2

You could create a button to READ in the data from the server, effectively refreshing the list (or do that automatically when the spreadsheet is opened).

I presume OrderNo is the PK, so that column needs to be readonly.

Track the edits done to Col B:D, and indicate any changes in another column, say H.
So now if any rows are modified, the corresponding H column will have a 1 or true or something.

When they click update, you check for the rows with values in H, and do an update Query using the primary key A.

Then they can refresh to ensure all changes were done.

Hello,
I follow this logic, but I'm having problem setting this up.

Are you able to help.

Thanks

Do you have any of this coded?  I can't develop the queries for you, as I can't access the data.

The rest I can do though.

There are only 2 queries to execute

Select * from Acttiveorders where accountnumber in (3333)
or
Select * from Acttiveorders where Ordernumber in (Excel Range A2:A5000) -- NOT sure if this is even possible.

Update Activeorders set WaybillNumber = test.Code where ActiveOrders.Orderno = test.OrderNo

I'm not sure if any other queries are required.

thanks

Here is most of the coding.  You need to provide the connection string.

I didn't test any of this code.

For those reading and don't want to open the file.
Module Code

Option Explicit

Dim sh As Sheets
Dim cnn As Connection

Public Sub RefreshData()
    Set sh = Sheets("Orders")
    Dim rs As New ADODB.Recordset
    Dim sql As String
    Set cnn = New Connection 'Need to set the connection string to your database here
    cnn.Open
    
    sql = "SELECT * FROM Acttiveorders WHERE accountnumber in (3333)"
    'Delete old data
    sh.Range("A2:H65000").ClearContents
    
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Public Sub UpdateData()
    Set sh = Sheets("Orders")

    Set cnn = New Connection 'Need to set the connection string to your database here
    cnn.Open
    Dim i As Integer
    
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
        End If
    Next i
    cnn.Close
End Sub

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
    
    Dim rs As New ADODB.Recordset
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    rs.Close
End Sub

Select allOpen in new window

Worksheet Code

Private Sub Worksheet_Change(ByVal Target As Range)
'Save that column was modified
If Target.Column = 4 Then
    Cells(Target.Row, 8) = "Modified"
End If

End Sub

Select allOpen in new window

Test.xls

thank you for your time and help.

I added my string

Set cnn = New Connection 'Provider=SQLOLEDB.1;Password=PASSWORDXXXX!;Persist Security Info=True;User ID=sa;Initial Catalog=CourierComplete;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False

When I hit Refresh:
error: Compile Error: Method or Data member not found
-----          sh.Range("A2:H65000").ClearContents

thanks

very first line should be
Dim sh As Worksheet

I get connection error

Run-time error:'-2147467259 (80004005)

cnn.Open

I get the same error when I run the refresh.
Run-time error:'-2147467259 (80004005)
cnn.Open

My connection to the server is working, I tested on some other sheets.

thanks

Tested it on other sheets? You used similar code? Same connection string?

I'm guessing either there's something wrong with the connection string.

The one you've posted won't work, as it's commented out, it needs to be a string (surrounded by double quotes).

I tested the connection with a different code.(not this code).

here is the connection string

    Set cnn = New Connection  "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
  cnn.Open

if I put double qoutes,I get error
Compile error
Expected: end of statment.

Thanks

Sorry, doing a lot from memory, and I'm usually programming in VB.NET, not VBA.

I don't know if it knows Connection is ADODB.Connection, so change line 4 to
Dim cnn As adodb.Connection

Set the connection via:

Set cnn = New adodb.Connection

cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"

I appreciate your help,
I still get errors.
(Compile error: Method or data member not found)
Here is the code

Option Explicit

Dim sh As Sheets
Dim cnn As Connection

Public Sub RefreshData()
    Set sh = Sheets("Orders")
    '' Dim rs As New ADODB.Recordset
    Dim cnn As adodb.Connection
    Dim sql As String
    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
    cnn.Open
   
    sql = "SELECT * FROM Acttiveorders WHERE accountnumber in (3333)"
    'Delete old data
    sh.Range("A2:H65000").ClearContents
   
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Public Sub UpdateData()
    Set sh = Sheets("Orders")

    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
    cnn.Open
   
    Dim i As Integer
   
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
        End If
    Next i
    cnn.Close
End Sub

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
   
    Dim rs As New adodb.Recordset
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    rs.Close
End Sub

dim sh as worksheet   (not sheets)

I don't know what line the error is on, but I'm guessing its sh.range, since range doesn't belong to sheets

I changed to
dim sh as worksheet  

now I get error

Compile error: Variable not Defined
 rs.Open sql, cnn, adOpenDynamic, adLockOptimistic

thanks

Hello,
I'm pulling my hair OUT, can someone please help.
Please see attached,
I'm able to get the data from SQL to the excel sheet.

I just can't figure out how to update back my SQL.

Update Activeorders set WaybillNumber = test.Code where ActiveOrders.Orderno = test.OrderNo

Thanks.
Test.xls

The code has changed quite a bit from what I supplied. I don't even see the Update Method that I started for you in there.

Need to use ADODB.Command instead of recordset to use an update query.  I mixed it up with editing values from a SELECT query.

     Dim cmd As ADODB.Command
     cmd.ActiveConnection = cnn
     cmd.CommandText
     cmd.Execute

Hello,
here is the code you supplied,

I still get errors.

Compile error: Variable not Defined
 rs.Open sql, cnn, adOpenDynamic, adLockOptimistic


Option Explicit

dim sh as worksheet
Dim cnn As Connection

Public Sub RefreshData()
    Set sh = Sheets("Orders")
    '' Dim rs As New ADODB.Recordset
    Dim cnn As adodb.Connection
    Dim sql As String
    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
    cnn.Open
   
    sql = "SELECT * FROM Acttiveorders WHERE accountnumber in (3333)"
    'Delete old data
    sh.Range("A2:H65000").ClearContents
   
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Public Sub UpdateData()
    Set sh = Sheets("Orders")

    Set cnn = New adodb.Connection
    cnn.ConnectionString = "Provider=SQLOLEDB.1;Password=PASSWORDXXXX!!;Persist Security Info=True;User ID=sa;Data Source=Wassim-Laptop;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=WASSIM-LAPTOP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Databasecc"
    cnn.Open
   
    Dim i As Integer
   
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
        End If
    Next i
    cnn.Close
End Sub

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
   
    Dim rs As New adodb.Recordset
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    rs.Close
End Sub

There are multiple corrections I have made that are not in this code.

Connection is not adodb.
rs in UpdateRow should be cmd.

The error is because you commented out the rs declaration in RefreshData

Hello,
ok, I got the connection going,
I had to change your connection code a tiny bit

    Set cnn = New adodb.Connection
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=Password!;Initial Catalog=Database;Data Source=Wassim-Laptop;"

Now, here is the issue.
it doesn't seem to be doing any updates in SQL,but rather it is doing the reverse.

it is UPDATING/replacing  Excel Columns (A,B,C,D), with the select Statement results. from SQL.

Here is the code,


Option Explicit

Dim sh As Worksheet
Dim cnn As adodb.Connection

Public Sub RefreshData()
    Set sh = Sheets("Orders")
    Dim rs As New adodb.Recordset
    Dim sql As String
    Set cnn = New adodb.Connection
   
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=ccdbsa1515!;Initial Catalog=CourierComplete;Data Source=Wassim-Laptop;"

    sql = "select OrderNO,OrderDate,CustomField1,CustomField2, WaybillNumber from ActiveOrders WHERE accountnumber in (3333)"
    'Delete old data
    sh.Range("A2:H65000").ClearContents
   
    'Get new data
    rs.Open sql, cnn, adOpenDynamic, adLockOptimistic
    sh.Range("A2").CopyFromRecordset rs
    rs.Close
    cnn.Close
End Sub

Public Sub UpdateData()
    Set sh = Sheets("Orders")

    Set cnn = New adodb.Connection
    cnn.Open "Provider=SQLOLEDB.1;User ID=sa; password=ccdbsa1515!;Initial Catalog=CourierComplete;Data Source=Wassim-Laptop;"

    Dim i As Integer
   
    'Find rows that have been modified
    For i = 2 To Range("A1").End(xlDown).Row
        If Cells(i, 8) = "Modified" Then
        UpdateRow (i)
        End If
    Next i
    cnn.Close
End Sub

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
   
    Dim cmd As New adodb.Recordset
    cmd.Open sql, cnn, adOpenDynamic, adLockOptimistic
    cmd.Close
End Sub


thank you

Then your button is executing the RefreshData function.

The button to update the database should call UpdateData function.

UpdateRow function still needs updated to ADODB.Command as I pointed out prior. Right now it just changed the variable name from rs to cmd.

Hello,
I appreciate your time and patience.
I'm newbie here.

I attached the excel.
the buttons are linked to the proper macro.

still not working.
I'm not sure how to add (UpdateRow function still needs updated to ADODB.),.

Thanks again.
Excel2SQL.xls

replace UpdateRow with this.

Private Sub UpdateRow(rowIndex As Integer)
    Dim sql As String
    sql = "UPDATE Activeorders SET WaybillNumber = '" & sh.Cells(rowIndex, 4) & "' WHERE ActiveOrders.Orderno = " & sh.Cells(rowIndex, 1)
   
    Dim cmd As New adodb.Command
    cmd.ActiveConnection = cnn
    cmd.Execute
End Sub

I get Error
Run-Time error'-2147217908 (80040e0c)
Command text was not set for the command object.

cmd.Execute

I'm making careless errors in haste

cmd.ActiveConnection = cnn
cmd.CommandText = sql
cmd.Execute

Beautiful, thank you.
small question,,
is there any line you can add to tell the records updated?

to the end of UpdateData add the line

msgbox "Update Completed"

ok,  but the number of records? is that possible?

Perfect,
Thank you very very much for all your time and help.
 you have no idea how much time and effort this will save me..

Thanks a lot.

Perfect,
Thank you very very much for all your time and help.
 you have no idea how much time and effort this will save me..

Thanks a lot.