Excel as front end to an Access database - is it possible?

[Angus Comber]

Hello

I want to have Microsoft Excel act as an editing front end to Microsoft
Access via an SQL query. Basically a query - eg

SELECT SubTable.*, MainTable.* FROM MainTable INNER JOIN SubTable ON
MainTable.SiteID = SubTable.CustomerID;

So that a user can for example go in and edit a postcode and then somehow
save any changes made.

Would this be possible?

Any guidance on the procedure would be most appreciated.

Angus Comber
(e-mail address removed)

 

[Jim Carlock]

Yes, you'd need to code in the VBA IDE.

If you plan on using DAO to connect to an Access database,
the code will be similar to:

Private Sub subGetData()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strConn As String

strConn = ThisWorkbook.Path & "\db1.mdb"
strSQL = "Select * From tCustomers;"
' you can substitue query names if the queries are internally
' defined queries (inside of Access in this case)
Set db = DAO.Database.Open(strConn)
Set rs = db.OpenRecordset(strSQL)

With rs
'... process ...
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub

You can use ADO in a similar manner. You'll need to include a
reference to DAO or ADO or whatever MDAC routines you prefer
to use. Microsoft tends to suggest using ADO.

Hope that helps.