current community

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

OK, so I have some VBA I am wanting to use to update and add data to a table on an SQL server. I have been muddling through with limited knowledge of this functionality within VBA all day, searching various sites and not really getting any answers to make things click into place and not getting any response when posting it elsewhere. Hopefully I can get this solved here.

So, I have the following code that I have cobbled together:

Sub connectsqlserver()
Dim conn As ADODB.Connection
Dim recset As ADODB.Recordset
Set conn = New ADODB.Connection
Set recset = New ADODB.Recordset
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim msgstrng As String
Dim newstring As String
 If conn.State <> 0 Then
 conn.Close
 End If
 With conn
    .ConnectionString = "Driver={SQL Server};server=sage500;Database=CS3Live;Uid=sa;Pwd=pass; ReadOnly=False;"""
    .ConnectionTimeout = 5
    .Open
  End With
 recset.Open Source:="custinfosheetdata", ActiveConnection:=conn, CursorType:=adOpenKeyset, LockType:=adLockOptimistic


 If Sheets("Changes").Range("A1").Value <> 0 Then
 For i = 1 To Sheets("Changes").Range("A1").Value
    recset.Find "Col2 = " & Sheets("Changes").Cells(2, i + 2) 'find the value in B from B3 onwards
    'Do something
 Next i
 Sheets("Changes").Rows("3:" & i + 2).Delete xlUp
 Else
 i = 0
 End If
 If Sheets("New").Range("A1").Value <> 0 Then
 For j = 1 To Sheets("New").Range("A1").Value
    newstring = ""
    For k = 1 To 38
    If k = 38 Then
    newstring = newstring & "'" & Cells(j + 2, k).Value & "'"
    Else
    newstring = newstring & "'" & Cells(j + 2, k).Value & "', "
    newstring = Format(newstring, "")
    End If
    Next k
    Debug.Print (newstring)
    With recset
    .AddNew (newstring)
    .Update
    End With
 Next j
 Sheets("New").Rows("3:" & j + 2).Delete xlUp
 Else
 j = 0
 End If
 recset.Close
 conn.Close
 If i = 0 And j = 0 Then
 msgstring = "No Changes/New Data to add"
 Else
 If i = 0 And j <> 0 Then
 msgstring = "No Changes and " & j & " New Customers added"
 Else
 If i <> 0 And j = 0 Then
 msgstring = i & " Changes and no New Customers added"
 Else
 msgstring = i & " Changes and " & j & " New Customers added"
 End If
 End If
 End If
End Sub

Part 1: This currently throws out an error at "With recset.AddNew..." (3001) saying that arguments are of the wrong type. The table it is going to is formatted as nvarchar(255) and all the data is formatted as text in the various fields so I am not entirely sure whats happening there.

Part 1 code:

If lastrow <> 0 Then
 For j = 1 To lastrow
    For k = 1 To lastfield
    If k = lastfield Then
    newstring = newstring & "'" & Cells(j + 2, k).Value & "'"
    Else
    newstring = newstring & "'" & Cells(j + 2, k).Value & "', "
    newstring = Format(newstring, "")
    End If
    Next k
    With recset
    .AddNew (newstring)
    .Update
    End With
 Next j
 End If

Part 2: As my knowledge of VBA for ADODB connections is awful at best, I cannot figure out how to continue once I have found the row I require, hence the "'Do something" line. What I need this to do is find the record matched from column B in the "Changes" excel table and then edit that row in the SQL table to match it. I can't figure out how to do this though. Part 2 code:

 If lastrow <> 0 Then
 For i = 1 To lastrow
        recset.Find "Col2 = " & Sheets("Changes").Cells(2, i + 2) 'find the value in B from B3 onwards
' Do something
 Next i
     End If

Any help people could give with this would be awesome.

Cheers

Ben

EDIT: I have this from the debug.print which may help some people visualise this a bit more:

"23/07/13","TEST123","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test","Test"

This is for a full line (so therefore the Field List should not be required as this is data for every column in the correct order).

share|improve this question
 
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. –  mehow Jul 22 '13 at 14:15
 
Addnew takes two arguments: msdn.microsoft.com/en-us/library/windows/desktop/… –  Layman Coder Jul 22 '13 at 14:24
 
@mehow, I have modified it slightly to explain what I am getting at a bit better. –  bmgh1985 Jul 22 '13 at 14:27
 
@LaymanCoder they are in fact optional, so one line should suffice. You only need to use two if you are updating one or two values and need to column match. I am replacing the entire row FieldList- Optional. A single name, or an array of names or ordinal positions of the fields in the new record. Values- Optional. A single value, or an array of values for the fields in the new record. If Fieldlist is an array, Values must also be an array with the same number of members; otherwise, an error occurs. The order of field names must match the order of field values in each array. –  bmgh1985 Jul 22 '13 at 14:29
 
This is a monolithic piece of code where everything is mixed up together: extraction of data from cells, connecting to database, modifying Recordset, printing informational messages. I would suggest you to write functions and subroutines to do individual tasks and test each of these on their own. Doing otherwise will make your code difficult to maintain in the future. –  Tarik Jul 22 '13 at 14:49
show 4 more comments

2 Answers

up vote 2 down vote

From what you posted, I believe you've been trying to concatenate all the values into a string separated by ','. (correct me if I'm wrong)

This answer is only useful if you wanted to append new data, if you want to find a specific record in the database and update it then its a completely different story.

The "Add New" method takes in two arguments.

  1. The list of fields in array format
  2. The list of values in array format

Unless you have only one field or one value to add you should put them into array before using the "Add New" method.

A possible way of constructing the arrays:

For i = 0 to count_of_fields
    aryFields(i) = field_value
Next
For i = 0 to count_of_values
    aryValues(i) = value
Next
recset.AddNew aryFields,aryValues
recset.Update

Let me know if that helps!

share|improve this answer
 
Yes thats right, I am concatenating in there. Appending New Data is what I am doing in that part. The code is in fact in two seperate parts. One doing changes, one doing new data to be appended. I shall try adding the fields to the data string I already have, but as I am setting a full line with the correct columns, it should not make a difference. –  bmgh1985 Jul 22 '13 at 14:37
 
Afraid that makes no difference after adapting it to put in. Still getting an error. –  bmgh1985 Jul 22 '13 at 14:48
 
+1 for your code. The other possibility would be to use parametrized Command objects. See support.microsoft.com/kb/181734 on how to create ADO parametrized commands. –  Tarik Jul 22 '13 at 14:55
 
Was thinking about using those earlier on, but assumed they would be slower (having to actually run outside of the VBA rather than natively) so was trying to avoid that if possible, but good to know it is a viable option. –  bmgh1985 Jul 22 '13 at 15:17
 
As I said, you cannot put a concatenated string as the argument for the input. Let's say you have 5 values you have to put in all with value "Test". You need to create an array to hold that 5 values not a concatenated string. something like: Array("Test","Test","Test","Test","Test") or Dim aryValue() as variant Redim aryValue(4) for i = 0 to 4 aryValue(i) = "Test" next –  Derek Cheng Jul 22 '13 at 15:34
show 2 more comments
up vote 0 down vote accepted

Will post this now actually instead of Monday or else I may forget.

Ended up being the neatest solution as working with arrays in this case seemed to fail a lot and they are a lot harder to debug. This at least made it a lot simpler.

Also, was good finding out that once you have found the row (my part 2 question), that it is in fact the same process as with .addnew (which was what I was not sure of)

With conn
    .ConnectionString = "Driver={SQL Server};server=sage;Database=CS3Live;Uid=sa;Pwd=pass; ReadOnly=False;"""
    .Open
  End With
 recset.Open Source:="custinfosheetdata", ActiveConnection:=conn, CursorType:=adOpenKeyset, LockType:=adLockOptimistic

 If Sheets("Changes").Range("A1").Value <> 0 Then
    For i = 3 To LastRow
        With recset
            .Find "Col2 = " & "'" & Sheets("Changes").Range("B" & i) & "'"
            For k = 1 To 38
                strField = Sheets("Changes").Cells(2, k).Value
                varValue = Sheets("Changes").Cells(i, k).Value
                .Fields(strField).Value = varValue
            Next k
            .Update
        End With
    Next i
 Else
 i = 0
 End If

 If Sheets("New").Range("A1").Value <> 0 Then
     For j = 3 To LastRow
        With recset
           .AddNew
            For k = 1 To 38
                strField = Sheets("New").Cells(2, k).Value
                varValue = Sheets("New").Cells(j, k).Value
                .Fields(strField).Value = varValue
            Next k
            .Update
        End With
    Next j
 Else
 j = 0
 End If
... etc

So anyway, thanks to all that tried helping on here. I still cannot understand why arrays were not working though.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.