Excel VBA Update Access table
Posted on 2013-05-29
In my Excel VBA I am trying to interact with an Access database
I was given the following two ways to insert records.
Now I need the user to Update certain records.
What similar code would I use to do this?
You'd have to open a connection to the database. If you want to use DAO to do this:
Dim dbs As DAO.Database
Set dbs = OpenDatabase("Full path to your db")
You can then "execute" a SQL statement:
dbs.Execute "INSERT INTO Employees(Name, Number) VALUES('" & Worksheets("Sheet1").Range("A2").Value & "','" & Worksheets("Sheet1").Range("A3") & "')"
Obviously you'd need to change the path, worksheetname and Range.
(2) Sub ExportDataToAccess()
Dim cn As Object
Dim strQuery As String
Dim Name As String
Dim Number As String
Dim myDB As String
Name = Worksheets("Sheet1").Range("A2").Value
Number = Worksheets("Sheet1").Range("B2").Value
'myDB = "C:\Users\username\Documents\EMP.accdb"
myDB = "replace with the fully qualified path to your Access Db"
Set cn = CreateObject("ADODB.Connection")
.Provider = "Microsoft.ACE.OLEDB.12.0" 'For *.ACCDB Databases
.ConnectionString = myDB
strQuery = "INSERT INTO Employees ([Name], [Number]) " & _
"VALUES (""" & Name & """, " & Number & "); "
Set cn = Nothing