Murray Brown
asked on
Excel VBA Update Access table
Hi
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?
(1)
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").Val ue & "','" & 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
'Initialize Variables
Name = Worksheets("Sheet1").Range ("A2").Val ue
Number = Worksheets("Sheet1").Range ("B2").Val ue
'myDB = "C:\Users\username\Documen ts\EMP.acc db"
myDB = "replace with the fully qualified path to your Access Db"
Set cn = CreateObject("ADODB.Connec tion")
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0" 'For *.ACCDB Databases
.ConnectionString = myDB
.Open
End With
strQuery = "INSERT INTO Employees ([Name], [Number]) " & _
"VALUES (""" & Name & """, " & Number & "); "
cn.Execute strQuery
cn.Close
Set cn = Nothing
End Sub
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?
(1)
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
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
'Initialize Variables
Name = Worksheets("Sheet1").Range
Number = Worksheets("Sheet1").Range
'myDB = "C:\Users\username\Documen
myDB = "replace with the fully qualified path to your Access Db"
Set cn = CreateObject("ADODB.Connec
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = myDB
.Open
End With
strQuery = "INSERT INTO Employees ([Name], [Number]) " & _
"VALUES (""" & Name & """, " & Number & "); "
cn.Execute strQuery
cn.Close
Set cn = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your welcome
gowflow
gowflow
ASKER