What are your reasons for not making a frontend in access? It would make development much easier.
Anyway, you can use DAO or ADO in your excel vba to manipulate the access data.
I'm comfortable with DAO, here is some untested template code that I would use.
You'll need to add a reference to the DAO object library: from the vba editor go to Tools -> References -> Microsoft DAO
Code:
Sub GetSomeData()
On Error Goto ErrHandler
Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim qry as String
qry = "SELECT * FROM some_table;"
Set db = OpenDatabase("C:\Full_Path_To_DB\My_DB.accdb")
Set rs = db.OpenRecordset(qry)
If Not (rs.BOF And rs.EOF) Then 'make sure we got some records
'Returned some records, lets loop through them
rs.MoveFirst 'make sure I'm at the beginning of the data
Do While Not rs.EOF 'start looping
'process some data here
rs.MoveNext
Loop
Else
'query didn't return any records
End If
rs.Close
db.Close
ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Msgbox Err.Description, , "Error #" & Err.Number
Resume ExitHandler
End Sub
Code:
Sub SaveSomeData()
On Error Goto ErrHandler
Dim db as DAO.Database
Dim qry as String
qry = "INSERT INTO some_table (field) VALUES ('value');"
Set db = OpenDatabase("C:\Full_Path_To_DB\My_DB.accdb")
db.Execute qry, dbFailOnError
db.Close
ExitHandler:
Set db = Nothing
Exit Sub
ErrHandler:
Msgbox Err.Description, , "Error #" & Err.Number
Resume ExitHandler
End Sub