D
Doctorjones_md
I'm wondering how BEST to accomplish this.
I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.
I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.
I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.
I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.
Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Make Version the active sheet & Clear Data
'ThisWorkbook.Worksheets("Version").Range("A2:A150").Clear
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the XXXX database on the XXXX Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _
"User Id=xxxx;" & _
"Password=xxxx"
'Now open the connection.
cnExcel.Open strConn
On Error Resume Next
' Create a recordset object.
Dim ProdID As String
Dim sqlCommand As String
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
ProdID = InputBox("Enter Product ID.")
sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)
Sheet15.Range("D2").CopyFromRecordset rsExcel
' Tidy up
.Close
End With
cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing
End Sub
==============================
Here's what I'm tring to do ....
I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?
Thanks in advance for any assistance/advice offered.
I have a EXCEL workbook which populates data to, and retrieves data from SQL
Server.
I currently have VBA code which effectively sends the data from the EXCEL
worksheet to SQL Server tables -- no problem with this.
I currently have VBA code which effectively retrieves data from SQL Server
and displays that data back into the worksheet -- no problem with this
either.
I'm currently using a Command Button (on-click event) and the code below to
query SQL Server to determine the Max Version Number and add 1 to that
value. This value is then passed to a worksheet cell, and rolled up to SQL
Server along with the rest of the data to be submitted -- no problem with
this either.
Here's my "GenerateVersionNumber" code ...
===============================
Public Sub cmdGenerateVersionNumber_Click()
' Create a connection object.
Dim cnExcel As ADODB.Connection
Set cnExcel = New ADODB.Connection
' Provide the connection string.
Dim strConn As String
'Make Version the active sheet & Clear Data
'ThisWorkbook.Worksheets("Version").Range("A2:A150").Clear
'Use the SQL Server OLE DB Provider.
strConn = "PROVIDER=SQLOLEDB;"
'Connect to the XXXX database on the XXXX Server.
strConn = strConn & "DATA SOURCE=xx.x.xx.xx;INITIAL CATALOG=XXXX;" & _
"User Id=xxxx;" & _
"Password=xxxx"
'Now open the connection.
cnExcel.Open strConn
On Error Resume Next
' Create a recordset object.
Dim ProdID As String
Dim sqlCommand As String
Dim rsExcel As ADODB.Recordset
Set rsExcel = New ADODB.Recordset
ProdID = InputBox("Enter Product ID.")
sqlCommand = "SELECT IsNull (max(Version), 0) + 1 FROM Products WHERE
[ProductID] = '" + ProdID + "'"
With rsExcel
' Assign the Connection object.
.ActiveConnection = cnExcel
' Extract the required records.
.Open sqlCommand
' Copy the records into cell B2 on Sheet15 (Product Tracking Overall)
Sheet15.Range("D2").CopyFromRecordset rsExcel
' Tidy up
.Close
End With
cnExcel.Close
Set rsExcel = Nothing
Set cnExcel = Nothing
End Sub
==============================
Here's what I'm tring to do ....
I don't want the Sale Team to have to enter ProductID more than once.
ProductID is a field that needs to be entered on a worksheet, and it's also
a Query Parameter which I send to SQL Server to generate my One-Up Version
Number. How can I modify my "GenerateVersionNumber" sub (shown above) to
not only query SQL Server with the ProductID parameter entered in the Input
Box, but to also send the value entered in the Input Box to a particular
cell on a selected worksheet?
Thanks in advance for any assistance/advice offered.