Updating SQL from Excel

[llan_man]

Hi,

I have quite a complex excel vba budgeting model that has in built workflows. There's still loads of little bits that I need to iron out but the big "crease" I have at the moment is that I need to take a few fields and from within the spreadsheet and add a new record to an external SQL database.

I've gathered that I need to use the "INSERT" and "INTO" commands but haven't got a clue how. I would be very grateful for any advice and any experience users have had.

Thanks

Harv

 

[acw]

Harv

Hope this will help. There is a database c:\temp\test.mdb with a table called MyData. There are 6 fields (cusip, Company, Industry, Coupon, Rating and Price). Coupon and Price are Number, while the rest are Text.

In the existing sheet range A2:F2 are the values to be added to the table.

Sub ccc()
' references
' Microsoft DAO 2.5 or above compatibility library

 Dim wrkjet As Workspace
 Dim db As Database
 Dim rst As Recordset
 
 Set wrkjet = createworkspace("", "admin", "", dbusejet)
 Set db = wrkjet.OpenDatabase("c:\temp\test.mdb")
 Set rst = db.OpenRecordset("Mydata", dbOpenDynaset)

 With rst
  .AddNew
   !cusip = Range("a2")
   !company = Range("b2")
   !industry = Range("c2")
   !coupon = Range("d2")
   !ratings = Range("e2")
   !price = Range("f2")
  .Update
 End With

 rst.Close
 db.Close
End Sub

You will have to add in the relevant reference. In the VBE, go tools, references and scroll down until you find the DAO reference and select it.

HTH

Tony