Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Animated Gifs and MS Access...

Posted on 2004-09-29
40
Medium Priority
?
1,802 Views
Last Modified: 2008-01-09
I'm trying to figure out how to use an animated gif on my MS Access form.  

When my form initially loads across the network it takes about 30 seconds or so, so I'd like to display an animated gif so that the user doesn't think the program is not responding.  I have displayed a "Loading please wait" dialog and the status bar says "Calculating"... but this doesn't seem to be enough and some users get "confused".  So they hit the X in the upper right hand corner and b/c Acccess is "Calculating" it thinks that Access is not responding and they hit "End Now" and it corrupts the database.

So I was thinking it would be a good idea to put some nice little animated gif so that the user can see that access is doing something and has not crashed.  But everything that I've been reading says access can't do it without using a third-party ActiveX control.  I would like to do it without using any type of external controls and have it all within the database.  

Can this be done using the webbroswer control (as done in VB) and what would the syntax be in VBA? .... Any help or suggestions would be greatly appreciated.

I thought I remember someone developing an Access 97 module for animated gifs.... I've also tried using the timer event to have it switch through images which does work normally, but does not work when access is "Calculating"...

Anyhow, any help would be appreciated...
0
Comment
Question by:jg0069_2002
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 7
  • 6
  • +6
40 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 12183530
msgbox "Our company's network is slow as hell.  Probably because we're too cheap too.  Please wait", vbInformation + vbOkOnly, "Wait"
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12183588
Thanks for the comment... But I like my job... and I don't the folks in the front office would appreciate that too much... but its the truth.... :-)
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12184314
Something that would probably help is changing the cursor to an hourglass instead of the usual arrow pointer:

docmd.hourglass true

Set it to false when you are done loading.  Most people don't know they can still click around while the hourglass is showing.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 2

Author Comment

by:jg0069_2002
ID: 12184373
I have the mouse pointer already turned to an hourglasss....

Something else I was considering was maybe locking the mouse cursor in a certain position...
0
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12184522
Place two or more picture boxes on the screen and flash from one to the other
use a timmer control to get the rate correct
you could use three picture boxes and use the folder images rotated and make it look as if the folders are moving acroosed the screen
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12184639
Excalibur... Thanks for the comment.  I tried that and it appears that Access does not fire the OnTimer events when it is "Calculating".  

What happens is I am loading this form and it goes out and fetches like 50 thousand records and stores it in a global recordset (yea, this seems kinda dumb, but its across a network, so the user sees a delay for about 30 seconds and all the records are then stored in this global recordset.  Since all of the records are loaded, all of the filter and save operations are pretty much as fast as the user can click, rather than having to wait each time they try to perform an operation on the records).  

So initially when the form opens Access is trying to load all 50 thousand records into this global recordset and the Access Status Bar says "Calculating...".  When it is "calculating" it will not fire any of the onTimer events.
0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12185104
This is a tough one because Access completely ignores all other events while it is fetching data. That means that you can't tie into any events (like timers) until after it completes, and then you don't need to. I think that this is actually something that you can't do without a specialized activex control, and I am not so sure that will work.

That said, if you have Internet Explorer installed there is an ActiveX control called the Microsoft Web Browser control that you can use to display an animated gif on your form. It has some aggrivating display control limitations though. If you want to try it, do this:

1. Click on the bottom right button on the toolbox (the one with a hammer and wrench)
2. Select Microsoft Web Browser from your list.
3. Select the web browser control in the toolbox and draw one on your form.
3. In your form Load event, add this code:

Private Sub Form_Load()
    WebBrowser1.Navigate2 "c:\myapp\loading.gif"   ' (Use your own filename)
End Sub

This will display the GIF, but it will probably have a scrollbar on it that looks weird. You may be able to get around that but I am not sure how. I hope this helps.

M@
0
 
LVL 2

Expert Comment

by:Rich292
ID: 12189210
Hi jg

Just a thought. Have you considered a progress bar which updates progress whilst the form loads. I've had a quick look around and there a few examples which could be adapted.

http://www.mvps.org/access/resources/downloads.htm (Progress Bar)
http://ourworld.compuserve.com/homepages/attac-cg/AFormTip.htm#METER
http://www.oreilly.com/catalog/accesscook/chapter/excerpts.html#t3

Another option might be a splash screen such as:
http://www.experts-exchange.com/Databases/MS_Access/Q_21079188.html

Rich
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12190892
i guess my comment would be that it sounds like you are trying fix a design issue by adding even more things which could slow down your form load. I would suggest the proper thing to do would be to look at why the form is taking so long to open...

things to check...

are you returning more data than you need
are your tables indexed and optimized
do you have too many controls on one form
could your form be split into smaller chunks to provide quick load
is your network and hardware operating properly

search EE or google for more database optimizination tips.
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12192819
Thanks for the Comment SidFishes, but its not a design issue..... I've tried to optimize the performance of the system the best that I could.  The system hold alot of data for an access database and the data is going across a network.  It probably has about 100,000 records or so to fetch.  I've found that it is much much much faster to open up all the records and store them in a global variable and then just perform operations on that stored recordset (.addnew, .update, .filter, etc.) rather than having it reopen just that one record each time.  

The network is slow and I have no control over that so I must design around that.  So there is about a 1-2 mintue wait up front for all the records to load and then after that it is pretty much as fast as the user can click.  
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12192965
ok...(altho 100,000 records isn't that much...i've had as many as 500,000 in a straight mdb FE/BE setup...)

you could set your startup form to a modal form with "PLease Wait" and closebutton property set to no

in the on_load of this form fire the loading of your real form...

then add some code to close the Wait form to the real form...maybe using a timer or triggered by someevent on the form

this way the modal form won't let the user do anything (except hit ctrl_alt_del i suppose)


0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12193257
Another thing you could do is put a progress bar on a form and open this form before you run the process.  If you set the progress bar to increment every couple of seconds on a form timer, at least the user would see something happening.  The progress bar would have to be set to only increment maybe half its length in the estimated time so that it never reaches the end before the process is finished loading (otherwise users might try to click out as you described).

Once the process is done loading, stop the timer event, set the progress bar to full, then close the progress bar form.

If you want to go this route, I'll post some code - took me a little while to get the order of screens to co-operate with the memory-hog process that loads at startup (I have something similar to your problem)

0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12193330
I think the problem here was that the timer event doesn't fire.
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12193390
Okay the progress bar and all that seem to be good ideas... but none of that will work in the manner that I wish to make it work...

I want something to be moving on the screen when access is retrieving records.... when the status bar says "Calculating...." which is internal to access ... I would like for an animated gif file to be displayed on the screen.  I've tried all the progress bars and all the timer events, but Access does not process any of these events while it is "Calculating..." or retrieving records...

So I guess my orginal question stands, how can I display an animated gif using access and not using a third party ActiveX control?

I think I can use the webbrowser control, but I can't figure out how to get rid of the scroll bars and size it the size that I'd like it to be...

0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 12193418
jg0069_2002 - Holy cow this is a lot of solid advice you're getting.  I hope these experts are getting at least a case of beer out of the deal...  -Jim
0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12193440
It just may be that this is not possible. Anyone want to challenge that? Any API gurus out there who may be able to hook into the form with an animation or something? I am out of ideas.
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12193448
0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12193487
OK...I have a wild and crazy idea. What if you first opened a dialog form (modal) and then opened your main form. underneath it....or in your form's load event, did a DoCmd.Open to open a modal dialog form.

On the modal form, you have some scrolling text or a series of still images that swap out on the timer event in a loop. In your form's Activate event, you run a DoCmd.Close on the dialog form.

Just may work?

M@
0
 
LVL 36

Expert Comment

by:SidFishes
ID: 12193507
hmmm...sounds familiar (^^^^^^) ;)
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12193530
WillCode,

That was the first idea that I had... I actually opened a modal form with a clock spinning on the form_timer event.  That form was opened before the form that takes like 2 mintues to open.  However when the other form opens the clock stops spinning and just sits there.  

Which goes back to my point that Access doesn't fire any events when it is "Calculating..."

~~~~~~~~~~~~~~~~~~~~~~~~~~

Sid,

The link that you posted I had already seen, but the MDE file is in Access 97 format.  Anyone have a copy of that same MDE in A2K format?  It won't let me convert the 97 copy to 2000, b/c its an MDE file...



0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12193559
Oh. I should learn how to read.

M@
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12193726
Another question while I'm thinking about it.....

I found a Gif89.dll file that I'm going to try to use in the interim.  Can I just register the DLL through code and not reference it in the project references??

I'm using the following code:

       'Register Gif89 Dll
        Dim strFileName As String
        Dim strRegister As String
        Dim dblRunApp As Double
       
        strFileName = "c:\Library Files\Gif89.dll"
        strRegister = "Regsvr32.exe , " & strFileName
       
        If FileExists(strRegister) Then dblRunApp = Shell(strRegister, vbHide)

The file Gif89.dll will be located on a network drive and the fileexists function works fine, but I guess my question is will this be sufficient to ensure that the DLL is registered on the end-users machine.  I can't copy it to the Windows\System Directory that's why I'm doing it this way.  
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12193841
I haven't tried this yet... but maybe use a media player control and play an .mpg in the player (if you can find a movie of a progress bar :)  ... I'm sure someone could create one pretty easily in flash...   Its not the best idea but it might play in the foreground while your process is loading.
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12194736
And just so everyone knows.. I did trying the code above which seemed to work on my local machine, but when I tried running from someone elses machine using the run-time version of access, it crashed... WHich is kind of what I figured it would do...

Looks like I'm still looking for someway of doing the animated gif thing while Access is "Calculating..." with no third party controls...
0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12607224
Is the question still open?

M@
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12607379
Yep... the question is still open...
0
 
LVL 4

Expert Comment

by:willcode4coffee
ID: 12607442
OK. I know that this is probably not going to help, but does it HAVE to be an animated gif? Would some other method of indicating some operation be equally sufficient? I don't know of any right this minute, but I think you will have a much better chance of finding a solution if some other type of notification is acceptable.

M@
0
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12607495
You know that you can turn on the hour glass in the formload event and turn off the hour glass when you are done. That way you have some way of knowing your job is complete
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12615904
Excalibur_Softwar,

Yes, I am aware of the docmd.hourglass command.. and I am already doing that.  But the problem with that is that the wait is pretty substantial (1 to 2 minutes) depending on network speed ... and users think the system has locked up.  

WillCode,

At this point, anything that is moving woud make me happy! ... doesn't have to be an animated gif.... but like I said the problem is that the traditional moving things (i.e. - progress bar) will not move when Access is "calculating.." (setting up my recordsets)....

0
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12620136
Ok I have a way for you to make items move but you may need to rebuild your form
how are you connecting to your database?
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12621345
I'm using the standard VB ADO syntax to open the tables.  

Set rstTest = New ADODB.Recordset
rstTest.CursorLocation = adUseClient
rstTest.Open "tblTest", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

It is not an external database.  Basically what I am doing is storing a table that is in the local database into a global variable.  The problem comes in b/c there are multiple tables with 50K plus records each.  Yea, this seems kinda silly but the form that I have built is kind of a windows-explorer type tree heirarcy where users click on a node and then a list is populated in a listview below the tree.  When they click an item in the list, the program fetches that record out of the 50K or so.  

Since the database is going across a network, what I do is open the recordset and store it as a global variable.  This makes it faster for the end user.  When the use clicks on a item in the list, all you have to do is apply a filter to the global recordset (rather than having to reopen the recordset and then filter it) .  So the inital delay of 1 to 2 minutes up front is kind of my way of circumventing the issues with working across a network.  

The problem with not being able to make anything "move" comes in when the above code is run.  The status bar in Access says "Calculating..." and it stops processing all commands (i.e. - form timer events, etc.)

So that's pretty much where I'm at now..... Any help would be greatly appreciated.
0
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12625358
have you tried doing something like this
MyRecordCount = 0

With rstTest.Open
    Do While Not .EOF
    ActiveCompanyName = rstTest.Open!CompanyName
    ActiveCompanyId = rstTest.Open!CompanyId
    MyRecordCount = MyRecordCount + 1
   Select case MyRecordCount
      Case 1 to 100
         'show your first image
         me.repaint
      Case 101 to 299
         'show your Second image
         me.repaint
      Case 300 to 399
         'show your Thrid image
         me.repaint
      Case 400 to 600
      '   show your Fourth image
         me.repaint
      Case Else
      MyRecordCount = 0 ' reset counter and start over
  End Select

    .Update
    .MoveNext
    Loop
End With
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12625885
Excalibur_Software,

I'm kind of confused with your code shown above.  Ulitmately, I want to end up with a global variable called "rstTest" that has all holds all records from tblTest.

In your code above you show a loop, but where exactly are you telling it where to open and how does it loop on the .open command?  I've never seen it used like that.

As I said before, I'm using the standard ADO syntax:

Set rstTest = New ADODB.Recordset
rstTest.CursorLocation = adUseClient
rstTest.Open "tblTest", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

The 1-2 minute wait that I'm experiencing is on the rstTest.open line.  

Can you explain your code a little further please?
0
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12628379
I am using the with statement, the loop start with  Do While Not .EOF ' this line reads in all the data until it gets to the end of file marker for your record set the data is stores rstTest.filename in this case I am storing data of the Active Company  but you can replace the variable to store any data you need or want. Change the strSQL to select your data

Dim conDatabase As ADODB.Connection
Dim rstUser As ADODB.Recordset
Dim strSQL As String

strSQL = "SELECT tblTest.UserId, tblTest.UserName, tblTest.Password, tblTest.AccessLevel " & _
" FROM rstTest " & _
"WHERE (((tblTest.UserName)='" & UserName & "') AND ((tblTest.Password)='" & UserPassword & "'));"

Set conDatabase = CurrentProject.Connection

Set rstUser = New ADODB.Recordset
rstTest.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

MyRecordCount = 0

With rstTest.Open
    Do While Not .EOF
    ActiveCompanyName = rstTest!CompanyName
    ActiveCompanyId = rstTest!CompanyId
    MyRecordCount = MyRecordCount + 1
   Select case MyRecordCount
      Case 1 to 100
         'show your first image
         me.repaint
      Case 101 to 299
         'show your Second image
         me.repaint
      Case 300 to 399
         'show your Thrid image
         me.repaint
      Case 400 to 600
      '   show your Fourth image
         me.repaint
      Case Else
      MyRecordCount = 0 ' reset counter and start over
  End Select

    .Update
    .MoveNext
    Loop
End With

rstTest.Close      ' Close user record
conDatabase.Close  ' Free up memory Space

Set rstTest = Nothing
Set conDatabase = Nothing
0
 
LVL 4

Expert Comment

by:Excalibur_Software
ID: 12628494
Ps the rstTest.Open was a typeo
0
 
LVL 2

Author Comment

by:jg0069_2002
ID: 12628611
Excalibur,

The code you've show above will not help the 1-2 minutes delay that I am experiencing.  The line of code:

rstTest.Open strSQL, conDatabase, adOpenDynamic, adLockOptimistic

is the syntax that I am using as well.  This is where the delay occurs.  When you tell it to open the recordset using the code above there will be a noticable delay (1 - 2 mintues in my case) if you are working across a network with a large number of records (in my case 50K plus records) ....

There is no need for me to use the code you have shown that shows images based on a looping through the recordset.  Once the line above rstTest.Open ..... has been processed I have what I need -- a global variable rstTest that I can perfom .filter, .addnew, .delete, etc. on while the form is loaded.  

What I'm trying to do is have something "happen" while Access is process that one line of code.  

An animated gif was my first idea, but I'm open to any suggestions....






0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12853491
Do not accept this as an answer.

Reading through this whole thread, I was amazed that the only workable solution *I* could think of was immediately discounted.  There are perhaps several hundred 3rd party ActiveX controls that are made to do one thing - display animated GIFs.  At least one of my clients uses one of them in his own VB project connecting to SQL Server, and he uses it in a method very similar to the problem posed by the OP: eye-candy during initial load delays.  I can normally understand a company not wanting to shell out the bucks for an in-house project such as this, but when you're talking about $5-$15 to legally purchase a design-time license (with distribution rights for end products), there are NO valid reasons to not take the risk.

And speaking of risk, there is one notable brought up by WillCode4Coffee (http:#12185104): Access does not provide a "DoEvents" functionality while it is fetching data.  This means even if you DO use a 3rd party control, it may stop cycling the image once Access begins pulling data from the source.  You'll need to find an ActiveX control that attaches to the system timer, versus any API hooks into the system timer that Access might provide...I don't often work with API, so I cannot provide too much detail here.

As it is, any solution found within Access will not work because Access is part of the problem.
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 12853660
Another thing that might help regarding this problem:  If the users are clicking the 'x' to exit, or right-clicking the access taskbar item to close it, preventing them from doing these things would be a start.  Put the following code into a module and put "Call SetEnabledState(False)" in the load event of your startup form.  It will disable all abilities to shut down your database except the user hitting control-alt-delete.  Then if you just place a "Loading" message and an hourglass, the user will have to wait until the process is completely loaded.

Option Compare Database
Option Explicit

Private Declare Function GetSystemMenu Lib "user32" (ByVal hWnd As Long, _
    ByVal bRevert As Long) As Long

Private Declare Function EnableMenuItem Lib "user32" (ByVal hMenu As _
    Long, ByVal wIDEnableItem As Long, ByVal wEnable As Long) As Long

Const MF_GRAYED = &H1&
Const MF_BYCOMMAND = &H0&
Const SC_CLOSE = &HF060&

Public Function SetEnabledState(blnState As Boolean)
    Call CloseButtonState(blnState)
    Call ExitMenuState(blnState)
End Function

'Disable the Menu Option
Sub ExitMenuState(blnExitState As Boolean)
    Application.CommandBars("File").Controls("Exit").Enabled = blnExitState
End Sub

'Disable the Close Button Option
Sub CloseButtonState(boolClose As Boolean)
    Dim hWnd As Long
    Dim wFlags As Long
    Dim hMenu As Long
    Dim result As Long
       
    hWnd = Application.hWndAccessApp
    hMenu = GetSystemMenu(hWnd, 0)
    If Not boolClose Then
        wFlags = MF_BYCOMMAND Or MF_GRAYED
    Else
        wFlags = MF_BYCOMMAND And Not MF_GRAYED
    End If
   
    result = EnableMenuItem(hMenu, SC_CLOSE, wFlags)
End Sub


0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12874490
PAQed with points refunded (150)

modulo
Community Support Moderator
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question