Need help running SQL commands in a Excel VBA Macro

[Merennulli]

We have an ASP.NET project that spits out an Excel form for each user
based on their username. It's not secure data, their username just
limits it to the data relevant to them. They do so much manipulation
with it that they needed a spreadsheet, hence why it's not in HTML form
output.

The catch is, there's a field that the users want to be able to update,
unique to themselves. The solution we came up with for this is to
populate a separate table on MS SQL Server that holds their username, a
unique identifier off the spreadsheet, and the comment. Obviously, no
problem putting this in the generated spreadsheet at the server level,
but we're at a loss as to how to get it back up. The plan was to use a
macro to "DELETE FROM Comments WHERE username = inputboxresult" and
then loop through the fields with "INSERT INTO Comments (username, uid,
comment) VALUES (inputboxresult, $B#, $AZ#)". Of course, then we
realized neither of us knew how to run SQL commands in Excel VBA, and
Google results have been less than helpful so far.

The macros are being placed on the machines by hand, since they include
a lot of individual customizations. (I know, I know...) The upshot of
this is, if we need to distribute a File DSN with this, we can. I would
prefer ADO, but I've not seen any VBA code that hinted at that working.

Can someone give me a good resource or some sample code for running SQL
commands in our Excel macro?

 

[Bob Phillips]

Here is some code to insert into an SQL server table

Sub AddData()
Dim oConn As Object
Dim oRS As Object
Dim sSQL As String

Set oConn = CreateObject("ADODB.Connection")
oConn.Open = "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"User Id=myUsername;" & _
"Password=myPassword""

sSQL = "INSERT INTO Comments(UserName, uid,comment) " & _
" VALUES ('" & inputboxresult "', '" & B$ &"', '" AZ$ &
"')"
oConn.Execute sSQL

oConn.Close
Set oConn = Nothing
End Sub

This is VBA so you will need to adap to Net. Net might need a different
connection string, so checkout
http://www.carlprothman.net/Default.aspx?tabid=86#SQLClientManagedProvider


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Merennulli]

Thank you. That got us connected.

Sadly, I think it's my recent exposure to .NET that made this so hard.
Most of the differences between this and what we tried before are
differences between VB6 and VB.NET