This is the sort of query I was envisaging

sSQL = "SELECT Location FROM [Sheet1$A1:B20] WHERE Location LIKE
'Dallas%'"
oRS.Open sSQL, sConnect, adOpenForwardOnly, _
adLockReadOnly, adCmdText

' Check to make sure we received data.
If Not oRS.EOF Then
ary = Application.Transpose(Application.Transpose(oRS.ge trows))
On Error Resume Next
iMax = ActiveSheet.Evaluate("MAX(--SUBSTITUTE({""" & Join(ary,
""",""") & """},""Dallas "",""""))")
On Error GoTo 0
MsgBox iMax +1
Else
MsgBox "No records returned.", vbCritical
End If

I ran it against an Excel workbook, but the principles are the same. First I
queried against any location start with Dallas. I then extracted the MAX
value from the returned array, using array handling if none found and add 1
to it.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Doctorjones_md" wrote in message
...
Bob,

Thank you for your quick reply -- let me see if I understand you correctly
...
When I open Excel, and run the Sub ProductData() routine, I should insert
a query just prior to the Insert Statement?

Could you give me an example of what the code might look like?

When I open Excel, I have the current value, I just need to append a
suffix value (preferable an alpha suffix if possible)

================================
"Bob Phillips" wrote in message
...
Do a query prior to the insert to get the MAX value where the field
contains Dallas, and then just add 1 to the count.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Doctorjones_md" wrote in message
...
I have the following INSERT statement (which works fine) -- what I need
is to modify it so that the value of the field "Location" will be passed
to SQL server with a one-up suffix (example: on the 1st pass, append "1"
to the location Dallas, to get "Dallas 1" on the 2nd pass, Dallas 2,
ect -- ) to track versions. I'm not sure how I would write the code to
automatically affix the next higher number. How would I go about doing
this?

Many thanks in advance.

Here's my code:
========================================
Sub ProductData()

Dim oConn As Object
Dim sSQL As String

Application.ScreenUpdating = False
Set wsSheet = ActiveWorkbook.Sheets("Products")
Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=xxx.xx.xx;" & _
"Initial Catalog=Products;" & _
"User Id=xxxxx;" & _
"Password=xxxxx"

For i = 2 To Range("A65536").End(xlUp).Row
sSQL = "INSERT INTO Upload_Specific " & _
"([Location], [Product Type], [Quantity], [Product Name], [Style],
[Features]) " & _
" VALUES ('" & Range("A"&i).Value & "', '" & Range("B"&i).Value & "',
'" & _
Range("C"&i).Value & "', '" & Range("D"&i).Value & "', '" & _
Range("E"&i).Value & "', '" & _
Range("F"&i).Value & "')"
oConn.Execute sSQL
Next i


oConn.Close
Set oConn = Nothing


End Sub