Solved

Animated Gifs and MS Access...

Posted on 2004-09-29
40
1,474 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
  • 13
  • 7
  • 6
  • +6
40 Comments
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:jg0069_2002
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I think the problem here was that the timer event doesn't fire.
0
 
LVL 2

Author Comment

by:jg0069_2002
Comment Utility
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 65

Expert Comment

by:Jim Horn
Comment Utility
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
Comment Utility
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
Comment Utility
0
 
LVL 4

Expert Comment

by:willcode4coffee
Comment Utility
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
Comment Utility
hmmm...sounds familiar (^^^^^^) ;)
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 2

Author Comment

by:jg0069_2002
Comment Utility
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
Comment Utility
Oh. I should learn how to read.

M@
0
 
LVL 2

Author Comment

by:jg0069_2002
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Is the question still open?

M@
0
 
LVL 2

Author Comment

by:jg0069_2002
Comment Utility
Yep... the question is still open...
0
 
LVL 4

Expert Comment

by:willcode4coffee
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ps the rstTest.Open was a typeo
0
 
LVL 2

Author Comment

by:jg0069_2002
Comment Utility
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 50

Expert Comment

by:Steve Bink
Comment Utility
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
Comment Utility
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
Comment Utility
PAQed with points refunded (150)

modulo
Community Support Moderator
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now