Solved Confused on what Form event(s) to use that display form with only statis text before VBA code executes

[isaksp00]

I have an Access database that connects to an Azure SQL Server db, and all tables are in SQL server. I'm using a free trial tier, which pauses the SQL database after an hour. If I load a form when Access opens the accdb file that shows a combo box based on querying from a small (like 10 row) table when the db is paused Access throws an error (it can take a minute or two to unpause the db).

I Googled and got some VBA code that helps, which is in a Public module. It uses an ADODB connection object to open the connection and test if the connection state is adStateOpen, and then tries a simple SELECT statement to verify it can query. If it is connected and the query worked it exits. If the connection isn't Open it waits 5 secs and tries again, up to 15 times. This works to unpause the SQL db. The function doesn't exit until it gets a connection or the number of tries is exhausted (say up to 75 secs).

Because the delay if the DB hasn't been used in an hour can be a minute or more (per Microsoft advice on unpause performance) I want to display a simple popup form with just a static Label text box that displays a "please wait" type message. That form should become visible and then the VBA code to wake the DB should run while the form sits there. Whatever event I use to invoke the wakeup function will, after the function returns, close this form and then open another form that shows some data.

I've tried putting this code in various Form events, including OnLoad, OnOpen, AfterUpdate, OnActivate. It works to trigger the wakeup, but the form doesn't become visible. I can't figure out how to (or if it's possible to) invoke and run the wakeup code with the form visible.

Here's code in the OnLoad event. UnpauseSQL is the function that wakes the db.

Private Sub Form_Load()

Status = UnpauseSQL

If Status = True Then
DoCmd.Close acForm, Me.name, acSaveNo
DoCmd.OpenForm "Select Campus"
Else
DoCmd.Close acForm, Me.name, acSaveNo
MsgBox "Could not connect to database!"
End If

End Sub

 

[MarkK]

What data type is "Status" declared as? What data type does UnpauseSQL return? Are you sure UnpauseSQL returns a value? I routinely write functions and then forget to return the value. The upshot is: it's possible that Status, due to some other factor, will never evaluate to True. Then it doesn't matter what form event you use.

 

[GPGeorge]

I have an Access database that connects to an Azure SQL Server db, and all tables are in SQL server. I'm using a free trial tier, which pauses the SQL database after an hour. If I load a form when Access opens the accdb file that shows a combo box based on querying from a small (like 10 row) table when the db is paused Access throws an error (it can take a minute or two to unpause the db).

I Googled and got some VBA code that helps, which is in a Public module. It uses an ADODB connection object to open the connection and test if the connection state is adStateOpen, and then tries a simple SELECT statement to verify it can query. If it is connected and the query worked it exits. If the connection isn't Open it waits 5 secs and tries again, up to 15 times. This works to unpause the SQL db. The function doesn't exit until it gets a connection or the number of tries is exhausted (say up to 75 secs).

Because the delay if the DB hasn't been used in an hour can be a minute or more (per Microsoft advice on unpause performance) I want to display a simple popup form with just a static Label text box that displays a "please wait" type message. That form should become visible and then the VBA code to wake the DB should run while the form sits there. Whatever event I use to invoke the wakeup function will, after the function returns, close this form and then open another form that shows some data.

I've tried putting this code in various Form events, including OnLoad, OnOpen, AfterUpdate, OnActivate. It works to trigger the wakeup, but the form doesn't become visible. I can't figure out how to (or if it's possible to) invoke and run the wakeup code with the form visible.

Here's code in the OnLoad event. UnpauseSQL is the function that wakes the db.

Private Sub Form_Load()

Status = UnpauseSQL

If Status = True Then
DoCmd.Close acForm, Me.name, acSaveNo
DoCmd.OpenForm "Select Campus"
Else
DoCmd.Close acForm, Me.name, acSaveNo
MsgBox "Could not connect to database!"
End If

End Sub

I gave up on that free tier after the first day, for exactly this reason. The only value it has, IMO, would be as a testing environment for non-production applications.

"The function doesn't exit until it gets a connection or the number of tries is exhausted (say up to 75 secs)." Making users wait over a minute for a production application to come online seems like a bold move.

Have you had feedback from users?

 

[isaksp00]

What data type is "Status" declared as? What data type does UnpauseSQL return? Are you sure UnpauseSQL returns a value? I routinely write functions and then forget to return the value. The upshot is: it's possible that Status, due to some other factor, will never evaluate to True. Then it doesn't matter what form event you use.

Status is declared as Boolean. I have looked at it in VBA debugger and it does return a value of True (it would only ever return False if it could not connect to the DB in 15 tries).

 

[isaksp00]

I gave up on that free tier after the first day, for exactly this reason. The only value it has, IMO, would be as a testing environment for non-production applications.

"The function doesn't exit until it gets a connection or the number of tries is exhausted (say up to 75 secs)." Making users wait over a minute for a production application to come online seems like a bold move.

Have you had feedback from users?

Keep in mind this is for animal shelters, so their cost has to be minimal or ideally free. The Azure SQL license types confuse me, so I'm not sure what each nonprofit would really have to pay to get their own database. This use case is a small database, so the data storage should be minimal. That said, if the first volunteer to fire it up each day has to wait a minute, they would all live with that. What I want to avoid is an arcane Access error message or a screen that is blank and looks like nothing is happening.

 

[isaksp00]

Some progress. I removed all event procedures from the form that is used to show that action is occurring (named "Loading Toast"). I am using this AutoExec macro (GetUser() is a simple function that gets the user, domain and machine names and stores them in a global variable):

Because the UnpauseSQL function returns quickly after the database unpauses I can only test this once per hour, so I added a Sleep 10000 statement before the function exits. Now the Loading Toast form pops up and has the title but the one control in it (the label with text) does not show. That control is set with Visibility = Yes. The empty form sits there for 10 secs then I get the correct form Get Campus.

I have no idea why the form appears but not the control in it.

Her's its design mode view:

Thanks for all the help!

 

[GPGeorge]

Keep in mind this is for animal shelters, so their cost has to be minimal or ideally free. The Azure SQL license types confuse me, so I'm not sure what each nonprofit would really have to pay to get their own database. This use case is a small database, so the data storage should be minimal. That said, if the first volunteer to fire it up each day has to wait a minute, they would all live with that. What I want to avoid is an arcane Access error message or a screen that is blank and looks like nothing is happening.

Ah, a non-profit animal shelter? That does influence things. My basic cost for a full SQL Azure database at the lowest cost tier is ~$6.00 (US) a month, so there's that option if they can afford $75.00 (US) a year.

 

[Gasman]

Some progress. I removed all event procedures from the form that is used to show that action is occurring (named "Loading Toast"). I am using this AutoExec macro (GetUser() is a simple function that gets the user, domain and machine names and stores them in a global variable):
View attachment 121179

Because the UnpauseSQL function returns quickly after the database unpauses I can only test this once per hour, so I added a Sleep 10000 statement before the function exits. Now the Loading Toast form pops up and has the title but the one control in it (the label with text) does not show. That control is set with Visibility = Yes. The empty form sits there for 10 secs then I get the correct form Get Campus.

I have no idea why the form appears but not the control in it.

Her's its design mode view:
View attachment 121180

Thanks for all the help!

Upload a copy of the DB (if confidential info) with just enough to see the issue.

 

[Pat Hartman]

If you want to detect the disconnect when it happens, you need to add a timer event to each form that might be left open. The timer event then checks the connection and displays a popup message that warns the user that restarting might take more than a minute.

Timer events are fraught with danger so you need to be very careful. For one thing, you NEVER want two timer events running at any time. Therefore, your app can't use a tabbed interface. You need a real menu/switchboard form and you need to prevent multiple forms from being opened at the same time. What I do is hide the switchboard when it opens a form so the user can't open any other form until he closes the open form. If your app is set up so that one form might open another form, then only one form in that stream can have a timer event.

The other thing is you need to be very careful while you are testing. If you have a form open with a timer event running and you open a different form to make code changes, the Timer event will cause you to lose your changes if it fires before they are saved. So, in the apps where I use a timer event, I have a piece of code that turns the timers off for all forms when the database opens if I am the person who is logged in. If I want to test the timers, I have two macros. One to turn the timers on and another to turn them off. So I run the macro to call the code to turn the timers on and the other macro runs the code to turn the timers back off if i want to make code changes.

Here is a link to some videos I created with the help of @Uncle Gizmo and also the database I used to show you how events work. Please watch at least one of the videos so you understand how the logging form works. Then feel free to add your own forms if you want to test with your own forms instead of mine. The test forms do not add logging to ANY mouse events because they fire so often it is totally annoying. However, if you need to test mouse event code, then add the call to the logging code to whatever events you want to see activated.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

If you need help figuring out how to keep only a single form active at one time, I may have an example but I'll have to find it so ask if you need it.

 

[isaksp00]

Upload a copy of the DB (if confidential info) with just enough to see the issue.

Here's a sanitized copy but you can see the behavior. Table links have been removed. The connect string in the UnpauseSQL function has had the user and password changed to incorrect values, so the connect action always fails. I also added code to it to try to force the caption of the label control to show. If it works as I see it, you will see the form pop up with no caption and then it'll try a number of times to connect. It is interesting that the caption does appear after the final failure with the last text inserted into it.

 

[isaksp00]

Here's a sanitized copy but you can see the behavior. Table links have been removed. The connect string in the UnpauseSQL function has had the user and password changed to incorrect values, so the connect action always fails. I also added code to it to try to force the caption of the label control to show. If it works as I see it, you will see the form pop up with no caption and then it'll try a number of times to connect. It is interesting that the caption does appear after the final failure with the last text inserted into it.

SOLVED! I just poked around using Google about refreshing forms and discovered the DoEvents VBA function. Using that right after the statements that set the control's caption causes it to appear. Not knowing how access handles processing while a form is open and it is doing background VBA tasks I would not have thought to do this.

 

[Gasman]

Please mark as Solved then. Top right of thread.

I get to see the label caption without any issue?

 

[isaksp00]

Please mark as Solved then. Top right of thread.

I get to see the label caption without any issue?

Yes, the caption shows and gets updated by the DoEvents function.

 

[Gasman]

Yes, the caption shows and gets updated by the DoEvents function.

No DoEvents on the version you uploaded?

 

[isaksp00]

No DoEvents on the version you uploaded?

I uploaded that version and a little later stumbled on the DoEvents solution. Here is that same db with the newer function code.