Excel VBA SQL Data

Question

Hi All,

I have a small excel program.

I would like to be able to use this program to update a SQL table.

What would be the function to say update line 2 in SQL table Test in Database ABC

Thanks

Answer 1

I see you have other questions open that deal with actually connection to the SQL Server, so I won't add any more to that discussion.

Relational database tables don't think of things as being in a certain order, so you can't really say that a certain record is "record 2" or "line 2" just because you added it to the table second. Unless of course you use a field to create an ID number that increments with each new record added.

Then you can access that record by saying

UPDATE Test SET YourField=NewValue WHERE IDfield=2

Here's more information on the UPDATE command, if you need it.

Answer 2

First of all you need to add a reference to the ActiveX Data Objects library, which contains the set of objects that allow you to do database access - in the Excel Visual Basic editor, go to Tools|References... In the dialog box, scroll down until you find Microsoft ActiveX Data Objects 2.8 Library. Check the box next to the library name.

Your code to update the database should then look something like this (using the SQL from JohnK813's answer):

'Declare some variables
Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New ADODB.Connection

'Set the connection string
cnn.ConnectionString = myDatabaseConnectionString 'See http://connectionstrings.com if you need help on building this string for your database!

'Create a new Command object
Set cmd = New ADODB.Command

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE Test SET YourField = NeValue WHERE IDField = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL

'Open the Connection to the database
cnn.Open

'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing