0

I use some data in Excel that is stored in an Access database. If the user wants to add / change data, this must be done in the Access frontend (in my case). In order to provide an easy way to do so I placed an 'edit data' button inside the Excel sheet to open Database + Form:

' standard module

Const mstrDatabase = ' path & filename
Const mstrForm =     ' name of form to edit data

Private moAccessApp As Access.Application
    ' module level object to keep database open after button_click-code finished

Sub OpenDatabase_Button_Click()
    Set moAccessApp = CreateObject("Access.Application")
    moAccessApp.OpenCurrentDatabase mstrDatabase
    moAccessApp.Visible = True
    moAccessApp.DoCmd.OpenForm mstrForm
End Sub

If the user closes Access, the moAccessApp object is still set (Debug.Print (moAccessApp Is Nothing) prints out False).

Questions:

  1. How can I determine if Access has been closed by user?
  2. Is it possible to fire an event in Excel in that case?

ad (1): I like to prevent opening a second instance in case of button clicked a second time

ad (2): I want to refresh data in Excel at least after close of database (although I know the user may change data in Access and switch over to Excel again without closing Access).

Erik A
  • 31,639
  • 12
  • 42
  • 67
martin.lindenlauf
  • 382
  • 1
  • 2
  • 14
  • 1
    What's stopping you from having a form in Excel and update the Access database? It would mean that user will never have to open Access database and your workbook can then be linked to Access database and hence always have refreshed data from Access – Zac Sep 27 '19 at 09:43
  • @Martin.lindenlauf does [this](https://stackoverflow.com/a/58131935/11167163) answer to your needs ? – TourEiffel Sep 27 '19 at 09:52
  • @Zac I think implementing all data-update needs in Excel would be a clean solution, but much more costly than using existing form + subform structures in Access - too costly in my case, so I need to go with a kind of 'quick and dirty' solution, although trying to prevent operating errors like 'multiple instances' and 'missing refresh' as far as possible (preferably to user training). – martin.lindenlauf Sep 27 '19 at 12:32
  • After reading all the questions and comments, your approach is, indeed, dirty, but not quick at all. You'll need different procedures to resolve each of your questions. You should open a new question for each, not a post with a lot of questions. About inserting data, easy way would be just doing and SQL statement INSERT INTO from excel. About updating, an UPDATE statement.. Rest of questions should go in different posts – Foxfire And Burns And Burns Sep 27 '19 at 13:08
  • Not convinced that your 'quick and dirty' solution would be any quicker or less costly (and I'm really confused by the **costly** statement). But like @FoxfireAndBurnsAndBurns said, there is absolutely nothing stopping you from making the SQL calls from Excel to Access.. howerver.. your project, your call – Zac Sep 27 '19 at 13:28
  • @Zac: if I can achieve the needed results by forcing the user to utilize an already existing database frontend in case of he/she needs to update some data, *for me* that will be much faster to implement (I'm neither VBA nor SQL professional, as you may have noticed). => I'll go with Dorian's approach, this will meet my needs. I probably would prefer your approach if I had more time and knowledge. Thank you for pointing this out! – martin.lindenlauf Sep 27 '19 at 15:00
  • No problems. Good luck. Hope it works – Zac Sep 27 '19 at 15:04

1 Answers1

0

EDIT

According to microsoft documentation

I think that a way to do what you asked for is to call your macro at the close of your access :

Access Macro

Private Sub Form_Unload() 
 Call runExcelMacro("C:\YourWorkbookPath.xls", "MacroName")
End Sub

Excel Macro

Sub MacroName()
  MsgBox "Access  Form Is closed"
  ThisWorkbook.RefreshAll
End Sub

Also note that according to this answer there is not an OnClose event for Access..

Further Documentation here

.RefreshAll

.IsLoaded

TourEiffel
  • 4,034
  • 2
  • 16
  • 45
  • I thought to handle this in Excel alone, but calling Excel procedures from Access form events may be better: can handle more cases (e.g. `AfterUpdate` / `AfterInsert` => Refresh Excel). Since there are multiple workbooks linked to the same database and the forms are part of a 'stand alone use' frontend database, I'd need to tell Access (1) if the form load has been called by Excel, (2) which workbook called and (3) which procedure in Excel should handle the case(es). I think I could do that by setting some hidden unbound fields in the form from inside the calling code in Excel. – martin.lindenlauf Sep 27 '19 at 13:18