Updating SQL from Excel

llan_man

New Member
Joined
Nov 6, 2005
Messages
45
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

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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.

Code:
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
 

Forum statistics

Threads
1,078,012
Messages
5,337,724
Members
399,165
Latest member
ghoshwin

Some videos you may like

This Week's Hot Topics

Top