I have some functions that will allow a user to attach to a different
back-end database. I added this functionality to the program Ribbon in a
custom Access 2007 app. I have 2 ways to attach to a different back end db.
One is through a browse function. So the ribbon button option calls a macro
that runs a procedure that allows the user to browse to the back-end db,
then re-attaches the tables.
So for the browse button, A macro is launched: StartProgram("Other"). If
"Other" is the mode, I put up a browse dialogue in the StartProgram
function.
This works fine. All tables get properly re-attached, and the program
re-loads everything. Now for the problem.
I just added a new twist to the above where they can define their own
database locations into a table: tDatabaseLocations, the idea being that
they can switch to different databases at a single click, without browsing.
So, on program startup, I programatically add ribbon buttons for each db
that they define, and put an onAction call on the button (see proc below).
Each programmatic button I create is followed by a number. So when the
ribbon is built, it will have a series of buttons with button names such as
BtnOpenCustomDb1, BtnOpenCustomDb2, etc.. By finding the number at the end,
I know which db this relates to in their tDatabaseLocations entry.
The code below will show the correct db name, so the logic is working
(message box line that is commented out). The kicker is that it will get all
the way through all of the code in the proc called on the very last line:
Call StartProgram(dbnm)
However, when stepping through the code, when control is returned to this
calling proc (after running the Call StartProgram(dbnm) line), the program
just hangs. Since StartProgram uses many of the same variables, such as db,
rs, i, etc. I tried using different names in this proc, such as ddb, rrs,
ii, etc. All had no effect. I tried adding the DoEvents line, again no luck.
Lastly, I tried setting IRibbonControl to nothing and db to nothing, again
no affect.
So it will run all code in the StartProgram proc, but when it returns to the
proc below (WhichDbClicked), the program hangs. It appears to be treating
the call as a dialog (where it won't continue on to the last line until
StartProgram is complete). I think if it would simply execute the exit sub
line, while StartProgram is doing it's thing, all would be well.
Any ideas why it would be hanging?
Sub WhichDbClicked(control As IRibbonControl)
On Error GoTo ErrRtn
'
Dim ddb As Database, rrs As Recordset, recno As Integer, ii As Integer, dbnm
As String
recno = Right(control.ID, 1)
Set ddb = CurrentDb()
Set rrs = ddb.OpenRecordset("tDatabaseLocations", DB_OPEN_SNAPSHOT)
rrs.MoveFirst
ii = 1
Do Until ii = recno
ii = ii + 1
rrs.MoveNext
Loop
'MsgBox "Database Selected: " & rrs!DBInternalName
dbnm = rrs!DbLocation
rrs.Close
ddb.Close
Set ddb = Nothing
Set IRibbonControl = Nothing
DoEvents
Call StartProgram(dbnm)
Exit Sub
'
'
ErrRtn:
MsgBox Err.Number & " - " & Err.Description & ", Function: WhichDbClicked",
vbInformation
Exit Sub
End Sub
--
Andy
