Excel VBA get data from an 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 view the data in a form or loop through it
I am used to the DataGridView in VB.net. Does anything like that
exist in VBA?
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