Monday, November 08, 2010

Excel connect to database

One of my project requires user to use Excel to fill in a form. Once it is filled, it will send the data to a database and returns a ID. Although it is not ideal for such a connection to be done in Excel, It is acceptable if the action is just to insert records. I set the access rights to just able to read and insert. The data is not sensitive and confidential.

Here is how I wrote the macro. Note that the getting and returning of ID part is not included here so as not to confuse reader.

sub updatedb()
Dim MyConn As ADODB.Connection
Dim MyRS As ADODB.Recordset

Set MyConn = New ADODB.Connection
MyConn.Open "DSN=odbcdsn;Uid=userid;Pwd=userpass"

SQL="insert int mytable (a,b) values ('" & ActiveSheet.Range("E1").Value & "','" & ActiveSheet.Range("F1").Value & "')"

Set MyRS = MyConn.Execute(Sql)

Set MyRS = Nothing
Set MyConn = Nothing

End Sub

No comments:

Post a Comment