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

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

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

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.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!


"Angus Comber" wrote in message
...
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