• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 639
  • Last Modified:

Access 2003 and 2000 Issue

I am creating a database to be used by multiple people.  Of course, the Access version that this database will be used on isn't the same on all computers (How shocking!).  I am developing it in Access 2003 (Access 2000 format) and ran into an issue about the references.  I copied the references from a 2000 machine and have referenced those instead (Outlook 0 and Word 9).  They have been placed on the same network drive as the database resides.

To see if it worked I started the database on my computer (running 2003) and then went to a co-workers computer running 2000.  I started a copy of the front end (didn't want to run the same copy) and his wouldn't start while mine was running.  He can run his if mine isn't running with no problem - a little slower than on my computer - but it starts up.

While he is running the database, if I try to run the other copy of the database, mine is very very very slow to start up (did I mention VERY) although it will eventually run.

I get no errors (like someone else is using the database and I can't open it) until I shut it down (before it starts).  Then I am told it can't find one of the referenced files for the mdb file.

I can run both the original database and a copy of the database on my computer and both start up with no problems.

Please consider being my hero for the day and solving my problem!  Thanks!
Lena
0
LenaWood
Asked:
LenaWood
  • 30
  • 16
  • 8
  • +3
4 Solutions
 
MageDribbleCommented:
Lena,

I have pretty much the same question open.  I have yet to try the suggestions that were posted (rockiroads has one I think may work for us).  Check it out and see if any of these work for you.  As soon as I can test it, I will close my question.

http://www.experts-exchange.com/Databases/MS_Access/Q_21872461.html
0
 
Rey Obrero (Capricorn1)Commented:
Lena,
I would suggest that you create two versions of your Front end, 2000, and 2003
Place them in a server for copying.

0
 
rockiroadsCommented:
Mage, Ive been searching for that link to the code I provided.
This may help u and Lena
http://support.microsoft.com/kb/194374
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
rocki,
i think the codes from the link will only work for a uniform version of access.

 the code is referring to the path for the broken reference.
 this will only work if the reference is available.

my 2ยข
0
 
LenaWoodAuthor Commented:
The problem the link is speaking of is gives an error of:

Function isn't available in expressions in query expression

I don't get an error like that.  If I did, wouldn't I get the error even when the database is ran by itself on the 2000 machine?

I am so frustrated that I am having these troubles.

If I am running the database on my machine and I hold down the shift and start the copy of the front end, it seems to start and run ok.  I have tried opening a couple of different forms and so far nothing wants to start up if I have it running.

Our IT department is trying to get me another computer for my office that has 2000 on it so that I will be able to run the database from the second computer....then I can do more testing without having to kick my co-workers off their computers.

Lena
0
 
LenaWoodAuthor Commented:
OK...I created a copy of the front end.  One frontend (EmployeeRoster.mdb) I left the 2003 references intact.  The other front end (Copy of EmployeeRoster.mdb) I opened with the shift key held down (on a 2000 machine) and fixed the missing references...so now I should have had a front end for each version.

When I started the 2000 front end up it once again hung.  When I clicked on the X to shut it down, I got a program is not responding message.  I clicked End and then got this message:

Cannot find the file "rdc1\CPRP\EmployeeRoster\Copy of EmployeeRoster.mdb' (or one of its components).  Make sure the path and file name are correct and that all required libraries are available.

The file name and path are correct because it will run if I don't have a copy of the database running on my computer.

I have nothing special in the backend except for the tables.

Any other suggestions?
Lena
0
 
Rey Obrero (Capricorn1)Commented:
instead of naming it  Copy of EmployeeRoster.mdb,
name it   EmployeeRoster2K.mdb

and copy the db to the computer where you will run the application, after you have fixed all the references problems.

0
 
Rey Obrero (Capricorn1)Commented:
for distribution of the correct version, you can use a .bat file like this


rem ------------------------vvvvvvvvvvvv-------not sure about the folder name just check
if exist "C:\program files\office2k\office\msaccess.exe" goto A2K    
if exist "C:\program files\microsoft office\office11\msaccess.exe" goto A2003
goto end

:A2K
cd C:\Documents and Settings\%username%\desktop
copy "\\ServerName\FolderName\FE_2000.mdb" "C:\Documents and Settings\%username%\desktop\FE_2000.mdb"

goto end


:A2003
cd C:\Documents and Settings\%username%\desktop
copy "\\ServerName\FolderName\FE_2003.mdb" "C:\Documents and Settings\%username%\desktop\FE_2003.mdb"


goto end


:end
cls
0
 
rockiroadsCommented:
Lena, sorry was away

so a little behind

firstly

regarding this

"I am developing it in Access 2003 (Access 2000 format) and ran into an issue about the references.  I copied the references from a 2000 machine and have referenced those instead (Outlook 0 and Word 9).  They have been placed on the same network drive as the database resides"

Your not really supposed to copy office objects, these objects should already exist but under a different path
If u take your Access2003 DB to a Access2000 machine, open up Access, go to VBA window, select Tools/References

u will most likely find some are MISSING.

If the users do not have office, then u have to place those copied references then add them as a reference - this can be done via VBA.

That link looks at broken references and reports them. There is also code that relinks references.


I looks like u are using Outlook and Word automation, is that right? Access will only want that if u use them.

do u define as
dim x as Outlook.Application?

An alternative way to use Outlook and Word auto is to create objects
dim x as Object
set x = CreateObject("Word.Application")

this is one way to use automation without having to link the references

0
 
Leigh PurvisDatabase DeveloperCommented:
Are you sharing a FE or references?
Or are each other these on each users pc?
0
 
rockiroadsCommented:
You shouldnt need to create seperate copies for different versions of office.

ok, your A2003 DB, saved in A2000 format and being used on A2000 machine

open it up and try a compact/repair (after fixing references)

Now you are using linked tables

right click on one linked table, then go to linked table manager
Ensure all links are correct

Now go in VBA window
do a debug/compile

Did everything go okay?


0
 
LenaWoodAuthor Commented:
Capricorn - I will try your suggestion of giving it a different name and see what happens; As for the batch file...I wouldn't even know where to start with that...I don't even know what one is (time to learn I guess).

Rocki - Yes, they come up as missing.  I tried fixing them from the 2000 machine and the database ran.  When I worked on the front end again it messed things up again.  As for how I reference outlook...I do it like:

    Public objOutlook As Outlook.Application 'in a different module
    Public objEmail As Outlook.MailItem 'in a different module

    Set objOutlook = CreateObject("outlook.application")
    Set objEmail = objOutlook.CreateItem(olmailitem)

and word is:

    Dim WordObj As Word.Application

    Set WordObj = CreateObject("Word.Application")

LPurvis - Each user will have their own Front End.  The references I was sharing was only to see if that would make things work (which it didn't help).  Before they were stored (the references I copied) onto my computers C Drive.  Right now they are on the Network drive.

Rocki - I tried fixing the references and doing a compact and repair with no luck in things changing.  All links are correct.

All - The database (once working correctly) will work like this:

The backend will be stored on a network drive (rdc1\CPRP\EmployeeRoster).  Each computer (all over our site - not just in one location) will have it's own Front End.  When I make changes to developers front end, I will place the changed copy on the network.  When each user opens up their database, they are actually opening up a different database that compares their version of the front end with the one on the network...if they are the same, then YEAH, if not, the new front end is copied to their machine.  I use this with other databases that I have built and it works AWESOME!

Thanks for all your help so far.  This one will cause me to go bald before it is over :-(
Lena
0
 
Rey Obrero (Capricorn1)Commented:
Lena,
to create a bat file, open notepad and copy the codes then save the file as  CopyFE.bat
---start

@echo off
echo DO NOT CLOSE THIS WINDOW.
echo IT WILL CLOSE AUTOMATICALLY WHEN FINISHED.

if exist "C:\program files\office2k\office\msaccess.exe" goto A2K    
if exist "C:\program files\microsoft office\office11\msaccess.exe" goto A2003
goto end

:A2K
cd C:\Documents and Settings\%username%\desktop
copy "\\ServerName\FolderName\FE_2000.mdb" "C:\Documents and Settings\%username%\desktop\FE_2000.mdb"

goto end


:A2003
cd C:\Documents and Settings\%username%\desktop
copy "\\ServerName\FolderName\FE_2003.mdb" "C:\Documents and Settings\%username%\desktop\FE_2003.mdb"


goto end


:end
cls

end of codes


you can then send the bat file as an attachment to an email.
give instruction to copy and paste to their desktop and once copied, double click and voila
they have the correct version copied on to their desktop
0
 
Leigh PurvisDatabase DeveloperCommented:
Is the problem that once with the correct version - sharing the data file is proving very very very slow?
(i.e. if somebody else is already in - then subsequent users are exposed to big delays?)

Is the data in the same location as backends for other applications you're using?
Or a different folder structure?
0
 
LenaWoodAuthor Commented:
Sharing the data file is so slow it doesn't seem to happen...although when it was running on 2000 (started first) it did eventually start on my machine using 2003.  So yes...I guess very very very VERY slow would be the word for it.

I am not sure I understand the second part of the question.
0
 
rockiroadsCommented:
have u tried changing

from this

Public objOutlook As Outlook.Application 'in a different module
    Public objEmail As Outlook.MailItem 'in a different module

    Set objOutlook = CreateObject("outlook.application")
    Set objEmail = objOutlook.CreateItem(olmailitem)

and word is:

    Dim WordObj As Word.Application

    Set WordObj = CreateObject("Word.Application")


to this

    Public objOutlook As object 'in a different module
    Public objEmail As object 'in a different module

    Set objOutlook = CreateObject("outlook.application")
    Set objEmail = objOutlook.CreateItem(olmailitem)

and word is:

    Dim WordObj As object

    Set WordObj = CreateObject("Word.Application")


try this in your A2003 db, then remove references to word and outlook
does it still work?




It seems there's two issues here
references
performance


With regards to performance and that slow PC, how quick is the network connection for him/her? Have u tried to copy a file,see how long that takes
0
 
LenaWoodAuthor Commented:
I get a variable not defined for olmailitem

The network connection is great for all of our computers.  Just seems to not be great when doing this particular thing.

Lena
0
 
rockiroadsCommented:
Regarding references, I created a new DB, and took my code that I use for outlook automation
I did not create a reference to outlook
And it worked fine

the references that DB had was
VBA
Microsoft Access
OLE Automation




Public Function SendRRMailEE(ByVal sReceipient, _
                           ByVal sSubject As String, _
                           ByVal sBodyText As String, _
                           optional ByVal sAttachment As String = "") As Boolean

'*** LENA - NOTE, I HAVE DEFINED AS OBJECT    
    Dim objOutlook As Object
    Dim objEmailMessage As Object
   
   
    'Specify error handler
    On Error GoTo SMError
   
    'If no receipient passed then exit with error
    If Trim$(sReceipient) = "" Then
        MsgBox "No Receipient name has been specified", vbExclamation, "Send Mail"
        Exit Function
    End If
   

'*** LENA - NOTE, I HAVE CREATED AS OBJECT    
    'Create outlook objects
    'note, you need to add the Outlook reference in Modules Menu option Tools/References
    Set objOutlook = CreateObject("Outlook.Application")
    Set objEmailMessage = objOutlook.CreateItem(0)
   
    'Set subject if specified
    If sSubject <> "" Then objEmailMessage.Subject = sSubject
   
    'If message body specified then add that
    objEmailMessage.Body = sBodyText
   
    objEmailMessage.ReadReceiptRequested = True
    objEmailMessage.OriginatorDeliveryReportRequested = True
   
    'If attachment passed then add that
    If sAttachment <> "" Then objEmailMessage.Attachments.Add sAttachment
   
    'Set the email object
    objEmailMessage.Recipients.Add sReceipient
    'Resolve email address
    objEmailMessage.Recipients.ResolveAll
   
    'Display email
    objEmailMessage.Display
   
    objEmailMessage.send
   
    'MsgBox "Message has been successfully sent", vbInformation, "Send Mail"
   
    'Return success
    SendRRMailEE = True
    GoTo SMDone
 
SMError:
    'Return failure and display error message
    SendRRMailEE = False
    MsgBox "An error occurred when trying to send the email." & vbCrLf & vbCrLf & Err.Description, vbCritical, "Send Mail"

SMDone:
    'Clear down the objects created
    On Error Resume Next
    Set objEmailMessage = Nothing
    Set objOutlook = Nothing
End Function


Public Sub SendEmailTest()
    SendRRMailEE "harry@hampster.com", "No References Test", "Whats for dinner, more lettuce?"
End Sub







0
 
LenaWoodAuthor Commented:
Need to use 0 not olmailitem - now the outlook part works.  Now to try the word part
0
 
LenaWoodAuthor Commented:
OK...The database works great with no references to Word or Outlook (I learned something very useful) HOWEVER I still have the same issues with running the database on my computer (2003) and then starting it on the computer with 2000.  The database runs great if I don't have mine running.

Now what?

Signed,
Harry The Hamster's BIGGEST fan!

PS - no lettuce for Harry if he makes this work...only the finest Hamster food will do!  (Thanks I needed the smile...I am near tears of frustration!)

Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
Yeah - using late binding sprung to my mind - but I didn't see how that would help you with your performance.
(Have you defiantely unchecked the Word and Outlook references).

The second part of my question was about how you mention you have other databases running fine.
Are they in a similar location to the BE of this one?

For example - the error you mentioned - what does
"rdc1\CPRP\EmployeeRoster\Copy of EmployeeRoster.mdb"
refer to?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You should always develop to the "lowest common denominator", or else switch all relevant objects to use Late Binding as Leigh suggest. If you're going to deploy to machines with A2000 and NOT use Late Binding, then you must do your final "build" on a machine with A2000 (and check your references first).

As rockiroads mentions, you cannot distribute the Outlook and Word references, and Access cannot "degrade" references. It can, however, upgrade them, so a reference to Outlook 2000, when moved to a machine with Outlook 2003, will work.

I too see no need in building two frontends - twice the work with no benefit, assuming you get your references straight.
0
 
LenaWoodAuthor Commented:
Yes...there are other databases that reside on \\rdc1\cprp that run fine.  Now that I have done late binding (Yes I am sure my references are unchecked) the reference thing isn't an issue.

I have been given a laptop with Access 2000 on it so no more kicking my co-workers off their computers.

My next step is to create a database (brand new) with several pieces of this database and see if I can run it on both machines.  I am wondering if it is a corruption issue.

I presenteded the idea of upgrading all of the computers that may need to use this database (about 40 of them give or take a few) and that idea was frowned upon.  Too much time and money to do it to solve a problem...they will do it as they have time (I guess).

Anyway - please don't leave me stranded.  I need to get this to work!
Lena
0
 
LenaWoodAuthor Commented:
OK...my problem just got worse...or better as the case may be.

I started my database on my machine (2003) and asked a member of our IT department to try and start the database on their machine (also running 2003) and it wouldn't start for them either.

Why can I run it twice?  Why can someone else run it if I am not in it?  Is something corrupt?

Thanks!
Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
(And do you have anything in the way of startup code?)
Do you have the same problems if you open the database bypassing everything?  So it does nothing except open the database window?
0
 
LenaWoodAuthor Commented:
No...I can open the database with the Shift held down with no problem...it starts right up.

The form that loads first has the following code in the On Load Event:

    DissallowNewLoginsToDatabase = Nz(DLookup("DissallowNewLoginsToDatabase", "[tblEmployeeRosterMaintenance]"), False)
   
    If DissallowNewLoginsToDatabase = True And Not IsLoaded("frmDatabaseAdministration") Then
        MsgBox ("The database is Temporarily Unavailable due to System Maintenance" & vbCrLf _
            & "Please try again in a few minutes" & vbCrLf & vbCrLf & "Database Administrator")
        Application.Quit
        Exit Sub
    Else
    End If
   
    fSetAccessWindow (SW_SHOWMAXIMIZED)

    Set db = CurrentDb

    If GetKeyState(VK_SHIFT) < 0 Then
        DoCmd.OpenForm "frmYesShift"
        DoCmd.Close acForm, "frmStartup"
    Else
        db.Execute ("uqry60DayPasswordChange")
        DoCmd.OpenForm "frmSplashVersion"
        DoCmd.Close acForm, "frmStartup"
    End If

    StartUpProperty "StartupShowDBWindow", dbBoolean, False
    StartUpProperty "AllowSpecialKeys", dbBoolean, False
    StartUpProperty "AllowShortcutMenus", dbBoolean, False
   
    DoCmd.ShowToolbar "Menu Bar", acToolbarNo
    DoCmd.ShowToolbar "Print Preview", acToolbarNo
    DoCmd.ShowToolbar "Database", acToolbarNo
    DoCmd.ShowToolbar "Form View", acToolbarNo
    DoCmd.ShowToolbar "Form Design", acToolbarNo
    DoCmd.ShowToolbar "Formatting (Form/Report)", acToolbarNo
    DoCmd.ShowToolbar "Web", acToolbarNo
    DoCmd.ShowToolbar "Report Design", acToolbarNo
    DoCmd.ShowToolbar "Formatting (Datasheet)", acToolbarNo
    DoCmd.ShowToolbar "Query Design", acToolbarNo
    DoCmd.ShowToolbar "Query Datasheet", acToolbarNo
   
    DoubleClickTimes = 0
0
 
rockiroadsCommented:
Hi Lena,

Glad the references issue sorted

Have u tried disabling all your security stuff and go straight into your main form?


Just a thought, what is the database open mode, its not exclusive or anything like that is it?
0
 
LenaWoodAuthor Commented:
No, it opens in Shared mode (will check backend also but sure it is too)

I will give it a go of trying to go to my main form and see how it works.

I just drug each object into a new database.  Tried importing but found that things were not moving as fast as I thought they should have.  Even had one of my forms (not one that opens at startup) cause some sort of "Report this to Microsoft" error.

We will see what happens now.
Lena
0
 
LenaWoodAuthor Commented:
OK...I can't start my startup, splashversion or my SignIn Form if I have the database running.  However I can open up the 4th form that opens in a row (frmUserInfo) and then the main menu...everything seems to work from there.  Wonder what it is about those 3 forms that are causing some problems.

Lena

PS  I did learn one thing.  Don't have Track Changes (or whatever it is called) turned off on the database you are importing from and turned on on the importing too database - it causes a MESS!
0
 
rockiroadsCommented:
ok, so now we are getting somewhere

one problem solved - the other hopefully getting closer
Alas Lena, I can no longer help you on this today as I have to pack my bags and get ready for the weekend.
Im sure we could crack it soon, but someone else has to take over now.

Suggestions, is there a timer in your splash screen?
I suggest u narrow it down, run it till the 3rd form (u may need to supply hardcoded/dummy values)
if that is fine, run till the 2nd

eventually u should fine a form thats slow

then need to look at if it has any images? perhaps thats slowing it down, or some code in there, getting stuck in a loop, etc. You know the usual thing.

Hopefully Ive given you some hints/tips to get you sorted
0
 
LenaWoodAuthor Commented:
Here is all the code from those 3 forms.  There are some days I love my job....and others....well :-(

frmStartUp:

Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer

Private Const VK_SHIFT = &H10
Private Const VK_RSHIFT = &HA1
Private Const VK_LSHIFT = &HA0

Private Sub Form_Load()

    DissallowNewLoginsToDatabase = Nz(DLookup("DissallowNewLoginsToDatabase", "[tblEmployeeRosterMaintenance]"), False)
   
    If DissallowNewLoginsToDatabase = True And Not IsLoaded("frmDatabaseAdministration") Then
        MsgBox ("The database is Temporarily Unavailable due to System Maintenance" & vbCrLf _
            & "Please try again in a few minutes" & vbCrLf & vbCrLf & "Database Administrator")
        Application.Quit
        Exit Sub
    Else
    End If
   
    fSetAccessWindow (SW_SHOWMAXIMIZED)

    Set db = CurrentDb

    If GetKeyState(VK_SHIFT) < 0 Then
        DoCmd.OpenForm "frmYesShift"
        DoCmd.Close acForm, "frmStartup"
    Else
        db.Execute ("uqry60DayPasswordChange")
        DoCmd.OpenForm "frmSplashVersion"
        DoCmd.Close acForm, "frmStartup"
    End If

    StartUpProperty "StartupShowDBWindow", dbBoolean, False
    StartUpProperty "AllowSpecialKeys", dbBoolean, False
    StartUpProperty "AllowShortcutMenus", dbBoolean, False
   
    DoCmd.ShowToolbar "Menu Bar", acToolbarNo
    DoCmd.ShowToolbar "Print Preview", acToolbarNo
    DoCmd.ShowToolbar "Database", acToolbarNo
    DoCmd.ShowToolbar "Form View", acToolbarNo
    DoCmd.ShowToolbar "Form Design", acToolbarNo
    DoCmd.ShowToolbar "Formatting (Form/Report)", acToolbarNo
    DoCmd.ShowToolbar "Web", acToolbarNo
    DoCmd.ShowToolbar "Report Design", acToolbarNo
    DoCmd.ShowToolbar "Formatting (Datasheet)", acToolbarNo
    DoCmd.ShowToolbar "Query Design", acToolbarNo
    DoCmd.ShowToolbar "Query Datasheet", acToolbarNo
   
    DoubleClickTimes = 0

End Sub

frmSplashVersion:

Private Sub Form_Load()

    If IsLoaded("frmUserInfo") = False Then
        Call ChangeColorControl(1, Me)
    Else
        Call SetSelectedTheme(Me)
        Call ChangeColorControl(intSelClrTheme, Me)
    End If

End Sub

Private Sub Form_Open(Cancel As Integer)
   
    Call gfnUpdateCount
   
End Sub

Private Sub Form_Timer()
   
    DoCmd.Close acForm, "frmSplashVersion"
    DoCmd.OpenForm "frmSignIn"

End Sub

frmSignIn:

Private Sub CancelButton_Click()
   
    Call MoveButtonDown

    AttemptedLogIn Me.txtUsername, Me.txtPassword, "Canceled"

    DoCmd.ShowToolbar "Menu Bar", acToolbarWhereApprop
    DoCmd.ShowToolbar "Print Preview", acToolbarWhereApprop
    DoCmd.ShowToolbar "Database", acToolbarWhereApprop
    DoCmd.ShowToolbar "Form View", acToolbarWhereApprop
    DoCmd.ShowToolbar "Form Design", acToolbarWhereApprop
    DoCmd.ShowToolbar "Formatting (Form/Report)", acToolbarWhereApprop
    DoCmd.ShowToolbar "Web", acToolbarWhereApprop
    DoCmd.ShowToolbar "Report Design", acToolbarWhereApprop
    DoCmd.ShowToolbar "Formatting (Datasheet)", acToolbarWhereApprop
    DoCmd.ShowToolbar "Query Design", acToolbarWhereApprop
    DoCmd.ShowToolbar "Query Datasheet", acToolbarWhereApprop

    StartUpProperty "StartupShowDBWindow", dbBoolean, True
    StartUpProperty "AllowSpecialKeys", dbBoolean, True
   
    DoCmd.Quit

End Sub

Private Sub ChangePasswordButton_Click()

    Call MoveButtonDown

    If Me.ChangePasswordButton.Caption Like "Change*" Then
        Me.ChangePasswordButton.Caption = "CANCEL CHANGE"
        Me.ChangePasswordButtonLabel.Caption = "CANCEL CHANGE"
        Me.SignInButton.Caption = "ACCEPT CHANGES"
        Me.SignInButtonLabel.Caption = "ACCEPT CHANGES"
        Me.txtUsername.Enabled = False
        Me.txtPassword.Enabled = False
        Me.txtNewPassword.Enabled = True
        HideShowSignInButton ("Hide")
        Me.txtNewPassword.Visible = True
        Me.txtNewPassword.SetFocus
    ElseIf Me.ChangePasswordButton.Caption Like "Cancel*" Then
        Me.ChangePasswordButton.Caption = "CHANGE PASSWORD"
        Me.ChangePasswordButtonLabel.Caption = "CHANGE PASSWORD"
        Me.SignInButton.Caption = "SIGN IN"
        Me.SignInButtonLabel.Caption = "SIGN IN"
        Me.txtUsername.Enabled = False
        Me.txtPassword.Enabled = False
        HideShowSignInButton ("Show")
        Me.txtNewPassword.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtPasswordReminder.Visible = False
        Me.txtNewPassword = ""
        Me.txtConfirmPassword = ""
    Else
        Me.Visible = False
        CustomMessage (19)
    End If

End Sub

Private Sub cmdHelpButton_Click()

'*******************************************************************************************'
'*                                                                                         *'
'* FUNCTION:                                                                               *'
'*  1.  DISPLAY CUSTOM HELP MESSAGE                                                        *'
'*                                                                                         *'
'* OTHER ROUTINES/FUNCTIONS CALLED:                                                        *'
'*  1.  CustomMessage - basCustomMessage                                                   *'
'*                                                                                         *'
'*******************************************************************************************'
   
    Me.Visible = False
    CustomMessage (13)

End Sub

Private Sub Form_Load()
   
    If IsLoaded("frmUserInfo") = False Then
        Call ChangeColorControl(1, Me)
    Else
        Call SetSelectedTheme(Me)
        Call ChangeColorControl(intSelClrTheme, Me)
    End If
   
    Me.txtPassword.Visible = False
    Me.txtNewPassword.Visible = False
    Me.txtConfirmPassword.Visible = False
    Me.txtPasswordReminder.Visible = False
    HideShowSignInButton ("Hide")
    HideShowChangePasswordInButton ("Hide")
    HideShowRequestPasswordInButton ("Hide")

End Sub

Private Sub Form_Open(Cancel As Integer)
   
    Me.SetFocus
    ShowMyToolBar

    Me.PWChk = 1
    Me.UIDCheck = 1

    CurrentDb.Execute ("qryMoveDeletedMessages")
    CurrentDb.Execute ("qryUpdateDeletedMessage")
    CurrentDb.Execute ("qryRemoveDeletedMessages")
    CurrentDb.Execute ("qryActivatePending")

End Sub

Private Sub RequestReminder_Click()
   
    Call MoveButtonDown
   
    If DLookup("[RecordUpdated]", "tblUsers", "[UserName] = '" & Me.txtUsername & _
      "'") = True Then
        Me.Visible = False
        CustomMessage (28)
        Exit Sub
    Else
        DoCmd.OpenForm "frmRequestPassword", acNormal, "qryRequestPassword", "", , acNormal
        Me.Visible = False
    End If

End Sub

Private Sub SignInButton_Click()
   
    Call MoveButtonDown

    If Me.SignInButton.Caption = "SIGN IN" Then
   
        If DLookup("[UserPassword]", "tblUsers", _
        "[UserName] = [Forms]![frmSignIn]![txtUsername]") = [Forms]![frmSignIn]![txtPassword] Then
            If Me.txtPassword = "Password" Then
                Me.Visible = False
                If DLookup("[PasswordReset]", "tblUsers", _
                "[UserName] = [Forms]![frmSignIn]![txtUsername]") = True Then
                    AttemptedLogIn Me.txtUsername, Me.txtPassword, "Password Reset"
                    CustomMessage (25)
                    Me.ChangePasswordButton.Caption = "CANCEL CHANGE"
                    Me.ChangePasswordButtonLabel.Caption = "CANCEL CHANGE"
                    Me.SignInButton.Caption = "ACCEPT CHANGES"
                    Me.SignInButtonLabel.Caption = "ACCEPT CHANGES"
                    Me.txtUsername.Enabled = False
                    Me.txtPassword.Enabled = False
                    Me.txtNewPassword.Enabled = True
                    Me.txtNewPassword.Visible = True
                    Me.txtNewPassword.SetFocus
                    HideShowSignInButton ("Hide")
                ElseIf DLookup("[PasswordReset]", "tblUsers", _
                "[UserName] = [Forms]![frmSignIn]![txtUsername]") = False Then
                    AttemptedLogIn Me.txtUsername, Me.txtPassword, "First Time User"
                    CustomMessage (24)
                    Me.ChangePasswordButton.Caption = "CANCEL CHANGE"
                    Me.ChangePasswordButtonLabel.Caption = "CANCEL CHANGE"
                    Me.SignInButton.Caption = "ACCEPT CHANGES"
                    Me.SignInButtonLabel.Caption = "ACCEPT CHANGES"
                    Me.txtUsername.Enabled = False
                    Me.txtPassword.Enabled = False
                    Me.txtNewPassword.Enabled = True
                    Me.txtNewPassword.Visible = True
                    Me.txtNewPassword.SetFocus
                    HideShowSignInButton ("Hide")
                Else
                    CustomMessage (19)
                End If
            Else
                If IsNull(DLookup("[UserName]", "tblUsers", "([UserName] = '" & Forms!frmSignIn!txtUsername & "' and [ChangePassword] = True)")) Then
                    AttemptedLogIn Me.txtUsername, Me.txtPassword, "Successful"
                    DoCmd.OpenForm "frmUserInfo", acNormal, "", "[UserName]=[Forms]![frmSignIn]![txtUsername]", , acHidden
                    Forms!frmUserInfo!txtLastLogIn = Nz(DMax("[LoggedIn]", "tblLogIn", "[UserID] = Forms!frmUserInfo!RecordID"), Now())
                    DoCmd.Close acForm, "frmSignIn"
                    fLogUser (1)

                    If IsNull(DLookup("[ChangeMessage]", "qryChangeAlert")) Then
                        DoCmd.OpenForm "frmMainMenu"
                        DoCmd.OpenForm "frmMessageCount"
                    Else
                        If Forms!frmUserInfo!ChangeAlertStatus = 1 Then
                            DoCmd.OpenForm "frmChangeAlert"
                            Exit Sub
                        ElseIf Forms!frmUserInfo!ChangeAlertStatus = 2 Then
                            DoCmd.OpenForm "frmMainMenu"
                            DoCmd.OpenForm "frmMessageCount"
                        Else
                            CustomMessage (19)
                        End If
                    End If

                    If Forms!frmUserInfo!UserLevel = "Administrator" Then
                        DoCmd.OpenForm "frmLoggedIn"
                    End If

                ElseIf IsNull(DLookup("[UserName]", "tblUsers", "([UserName] = '" & Forms!frmSignIn!txtUsername & "' and [ChangePassword] = False)")) Then
                    Me.Visible = False
                    CustomMessage (21)
                    Me.ChangePasswordButton.Caption = "CANCEL CHANGE"
                    Me.ChangePasswordButtonLabel.Caption = "CANCEL CHANGE"
                    Me.SignInButton.Caption = "ACCEPT CHANGES"
                    Me.SignInButtonLabel.Caption = "ACCEPT CHANGES"
                    Me.txtUsername.Enabled = False
                    Me.txtPassword.Enabled = False
                    Me.ChangePasswordButton.SetFocus
                    HideShowSignInButton ("Hide")
                    Me.txtNewPassword.Visible = True
                    Me.txtNewPassword.Enabled = True
                    Me.txtNewPassword.SetFocus
                    Exit Sub
                Else
                    CustomMessage (19)
                End If
            End If

        ElseIf IsNull(txtPassword) Then
            AttemptedLogIn Me.txtUsername, Me.txtPassword, "Blank Password"
            DoCmd.GoToControl "txtPassword"
            Me.Visible = False
            CustomMessage (2)
        Else
            AttemptedLogIn Me.txtUsername, Me.txtPassword, "Wrong Password"
            DoCmd.GoToControl "txtPassword"
            Me.Visible = False
            CustomMessage (3)
            txtPassword.Text = ""
        End If
    ElseIf Me.SignInButton.Caption = "ACCEPT CHANGES" Then
        sSql = "SELECT * FROM tblUsers where tblUsers.Username ='" & Forms!frmSignIn!txtUsername & "'"

        Set db = CurrentDb
        Set rs = db.OpenRecordset(sSql)

        With rs
            .Edit
            !UserPassword = Me.txtNewPassword
            !PasswordChangedDate = Date
            !ChangePassword = False
            !PasswordReminder = Forms!frmSignIn!txtPasswordReminder
            .Update
        End With
       
        Set rs = db.OpenRecordset("logPasswordChange")
       
        With rs
            .AddNew
            !UserName = Forms!frmSignIn!txtUsername
            !pwOld = Forms!frmSignIn!txtPassword
            !pwNew = Forms!frmSignIn!txtNewPassword
            !pwChangeDate = Date
            .Update
        End With
       
        rs.Close
        db.Close
       
        Me.Visible = False
        Me.txtPassword = Me.txtNewPassword
        Me.txtNewPassword.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtNewPassword = ""
        Me.txtConfirmPassword = ""
        Me.CancelButton.SetFocus
        Me.txtPasswordReminder = ""
        Me.txtPasswordReminder.Visible = False
        HideShowSignInButton ("Show")
        HideShowChangePasswordInButton ("Show")
        HideShowRequestPasswordInButton ("Hide")
        Me.ChangePasswordButton.Caption = "CHANGE PASSWORD"
        Me.ChangePasswordButtonLabel.Caption = "CHANGE PASSWORD"
        Me.SignInButton.Caption = "SIGN IN"
        Me.SignInButtonLabel.Caption = "SIGN IN"
        CustomMessage (20)
    Else
        CustomMessage (19)
    End If

End Sub

Private Sub txtConfirmPassword_AfterUpdate()
   
    If Nz(Me.txtConfirmPassword.Value) = "" Then
        Me.txtConfirmPassword.Value = Null
        Me.txtConfirmPassword.SetFocus
        Me.txtPasswordReminder.Visible = False
        Me.txtPasswordReminder = ""
        Me.Visible = False
        CustomMessage (11)
        Exit Sub
    End If
   
    If Me.txtConfirmPassword = Me.txtNewPassword Then
        Me.txtPasswordReminder.Visible = True
        Me.txtPasswordReminder.SetFocus
        Me.txtConfirmPassword.Enabled = False
    Else
        Me.txtConfirmPassword.Value = Null
        Me.txtConfirmPassword.SetFocus
        Me.Visible = False
        CustomMessage (12)
        Exit Sub
    End If

End Sub

Private Sub txtNewPassword_AfterUpdate()
   
    If Nz(Me.txtNewPassword.Value) = "" Then
        Me.txtNewPassword.Value = Null
        Me.txtNewPassword.SetFocus
        Me.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtConfirmPassword = ""
        Me.txtPasswordReminder.Visible = False
        Me.txtPasswordReminder = ""
        CustomMessage (4)
        Exit Sub
    End If

    If txtNewPassword = txtPassword Then
        Me.txtNewPassword = Null
        Me.txtNewPassword.SetFocus
        Me.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtConfirmPassword = ""
        Me.txtPasswordReminder.Visible = False
        Me.txtPasswordReminder = ""
        CustomMessage (6)
        Exit Sub
    End If
   
    If IsNull(txtNewPassword) Or Len(txtNewPassword.Value) < 8 Then
        Me.txtNewPassword = Null
        Me.txtNewPassword.SetFocus
        Me.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtConfirmPassword = ""
        Me.txtPasswordReminder.Visible = False
        Me.txtPasswordReminder = ""
        CustomMessage (7)
        Exit Sub
    End If
   
    For i = 1 To Len(txtNewPassword.Value)
        Select Case Asc(Mid(txtNewPassword.Value, i, 1))
       
        Case 48 To 57
            intNumCnt = intNumCnt + 1
        Case 65 To 90
            intUCaseCnt = intUCaseCnt + 1
        Case 97 To 122
            intLCaseCnt = intLCaseCnt + 1
        End Select
    Next i
   
    If intNumCnt = 0 Then
        Me.txtNewPassword = Null
        Me.txtNewPassword.SetFocus
        Me.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtConfirmPassword = ""
        Me.txtPasswordReminder.Visible = False
        Me.txtPasswordReminder = ""
        CustomMessage (8)
        Exit Sub
    ElseIf intUCaseCnt = 0 Then
        Me.txtNewPassword = Null
        Me.txtNewPassword.SetFocus
        Me.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtConfirmPassword = ""
        Me.txtPasswordReminder.Visible = False
        Me.txtPasswordReminder = ""
        CustomMessage (9)
        Exit Sub
    ElseIf intLCaseCnt = 0 Then
        Me.txtNewPassword = Null
        Me.txtNewPassword.SetFocus
        Me.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtConfirmPassword = ""
        Me.txtPasswordReminder.Visible = False
        Me.txtPasswordReminder = ""
        CustomMessage (10)
        Exit Sub
    End If
   
    If Not IsNull(DLookup("[pwOld]", "qryTop3PasswordChange", "[pwOld]='" & Me.txtNewPassword.Value & "'")) Then
        Me.txtNewPassword = Null
        Me.txtNewPassword.SetFocus
        Me.Visible = False
        Me.txtConfirmPassword.Visible = False
        Me.txtConfirmPassword = ""
        Me.txtPasswordReminder.Visible = False
        Me.txtPasswordReminder = ""
        CustomMessage (14)
        Exit Sub
    End If
   
    Me.txtConfirmPassword.Visible = True
    Me.txtConfirmPassword.Enabled = True
    Me.txtPasswordReminder.Visible = False
    Me.txtConfirmPassword.SetFocus
    Me.txtNewPassword.Enabled = False

End Sub

Private Sub txtPassword_AfterUpdate()
   
    NameAsEntered = Nz(Me.txtUsername, "None Entered")
    PasswordAsEntered = Nz(Me.txtPassword, "None Entered")


    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
        AttemptedLogIn NameAsEntered, PasswordAsEntered, "Blank Password"
        Me.txtPassword = ""
        Me.txtPassword.SetFocus
        HideShowSignInButton ("Hide")
        HideShowChangePasswordInButton ("Hide")
        HideShowRequestPasswordInButton ("Hide")
        Me.Visible = False
        Me.PWChk = Me.PWChk + 1
        CustomMessage (2)
        Exit Sub
    End If
   
    If DLookup("[UserPassword]", "tblUsers", "[UserName] = '" & Me.txtUsername.Value & "'") <> Me.txtPassword Then
        Me.PWChk = Me.PWChk + 1
       
        If Me.PWChk > 3 Then
            AttemptedLogIn NameAsEntered, PasswordAsEntered, "Force Quit"
            Me.Visible = False
            CustomMessage (16)
        Else
            AttemptedLogIn NameAsEntered, PasswordAsEntered, "Wrong Password"
            Me.txtPassword = ""
            Me.txtPassword.SetFocus
            Me.Visible = False
            CustomMessage (3)
        End If
    Else
        HideShowSignInButton ("Show")
        HideShowChangePasswordInButton ("Show")
        HideShowRequestPasswordInButton ("Hide")
    End If

End Sub

Private Sub txtPasswordReminder_AfterUpdate()
   
    If Me.txtNewPassword = Me.txtPasswordReminder Then
        Me.Visible = False
        Me.txtPasswordReminder = ""
        Me.txtPasswordReminder.SetFocus
        CustomMessage (22)
        Exit Sub
    End If
   
    If IsNull(Me.txtPasswordReminder) Then
        Me.Visible = False
        Me.txtPasswordReminder = ""
        Me.txtPasswordReminder.SetFocus
        CustomMessage (23)
        Exit Sub
    End If

    HideShowSignInButton ("Show")

End Sub

Private Sub txtUsername_AfterUpdate()
   
    NameAsEntered = Nz(Me.txtUsername, "None Entered")
    PasswordAsEntered = Nz(Me.txtPassword, "None Entered")

    If IsNull(DLookup("[UserName]", "tblUsers", "[UserName] = [Forms]![frmSignIn]![txtUsername]")) Then
        Me.txtPassword = ""
        Me.txtPassword.Visible = False
        Me.txtUsername = ""
        Me.txtUsername.SetFocus
        Me.Visible = False
        HideShowSignInButton ("Hide")
        HideShowChangePasswordInButton ("Hide")
        HideShowRequestPasswordInButton ("Hide")
        Me.UIDCheck = Me.UIDCheck + 1
           
            If Me.UIDCheck > 3 Then
                AttemptedLogIn NameAsEntered, PasswordAsEntered, "Force Quit"
                CustomMessage (15)
            Else
                AttemptedLogIn NameAsEntered, PasswordAsEntered, "Invalid UserName"
                CustomMessage (5)
            End If
    Else
        Me.txtPassword.Visible = True
        Me.txtPassword.SetFocus
        HideShowRequestPasswordInButton ("Show")
    End If

End Sub

Public Sub HideShowSignInButton(HideOrShow As String)
   
    Select Case HideOrShow
        Case "Hide"
            Me.SignInButton.Visible = False
            Me.SignInButtonBox.Visible = False
            Me.SignInButtonLabel.Visible = False
        Case "Show"
            Me.SignInButton.Visible = True
            Me.SignInButtonBox.Visible = True
            Me.SignInButtonLabel.Visible = True
    End Select

End Sub

Public Sub HideShowChangePasswordInButton(HideOrShow As String)
   
    Select Case HideOrShow
        Case "Hide"
            Me.ChangePasswordButton.Visible = False
            Me.ChangePasswordButtonBox.Visible = False
            Me.ChangePasswordButtonLabel.Visible = False
        Case "Show"
            Me.ChangePasswordButton.Visible = True
            Me.ChangePasswordButtonBox.Visible = True
            Me.ChangePasswordButtonLabel.Visible = True
    End Select

End Sub

Public Sub HideShowRequestPasswordInButton(HideOrShow As String)
   
    Select Case HideOrShow
        Case "Hide"
            Me.RequestReminder.Visible = False
            Me.RequestReminderBox.Visible = False
            Me.RequestReminderLabel.Visible = False
        Case "Show"
            Me.RequestReminder.Visible = True
            Me.RequestReminderBox.Visible = True
            Me.RequestReminderLabel.Visible = True
    End Select

End Sub
0
 
LenaWoodAuthor Commented:
Thank you Rocki - my weekend will be starting soon as well (THANK GOODNESS!)

Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
*All* of that runs at startup?
:-O

For example - what do these queries do?
    CurrentDb.Execute ("qryMoveDeletedMessages")
    CurrentDb.Execute ("qryUpdateDeletedMessage")
    CurrentDb.Execute ("qryRemoveDeletedMessages")
    CurrentDb.Execute ("qryActivatePending")

Altogether that's a lot of activity for one instance to be doing before another.
(Assume the other form activity is on bound forms?)

Do you have a persisted recordset opened at all?
Sometimes that can help performance (saves connections being established and dropped constantly) - if you open one recordset first - and keep it open during the lifetime of the application.

And bear in mind the suggestion of getting your linked table path as short as possible.  (Including the mdb filename).
0
 
LenaWoodAuthor Commented:
This has something to do with the code that is in the SignIn Form.  I am not sure what it is yet, but have a co-worker in the office next to me starting up the database and telling me yes/no.  Right now when it starts it doesn't appear that is going to be working, but give it about a minute and the splash screen shows, give it another minute and the sign in screen shows.  Never went past that point to see if it would take another minute for the Main Menu to show.

I am out of here for the weekend though.  I am going to put this behind me until Monday.  I am hoping there is some bright light that pops on so that I can make this shortly after arriving to work on Monday.

Lena
0
 
LenaWoodAuthor Commented:
Well...nothing I have tried so far is making this work...so I am starting the database over in hopes that it was/is just a corrupted form.

I will keep you informed of how it is going.  As for this question, I am not sure how to close it out, but will figure out something that is fair (at least as fair as fair can be).

Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
Starting over?  Oww!
If it's a corrupted form then there should be easier ways.
(But that's still an "if" and if it isn't surely that's a lot of work for nothing no?)

You've re-imported I imagine? (And all the rest of it - without re-reading all the previous comments)
No effect?
Have you exported to text and re-imported into a new mdb?
0
 
LenaWoodAuthor Commented:
LPurvis - It is something to do with the SignIn Form I believe.  If I bypass how the database starts up (Shift Key) and just open the frmUserInfo form and then the main menu, everything runs fine and other people can sign onto the database.

I am restarting the database to get it to work where it won't right now, then I will slowly import (and test test test) each additional part of the database.  Thank goodness this isn't something they need working right now...this is just the next generation of a database of what they are using now.  Improving on a tool that already works you might say.

Yes, I have re-imported.  I didn't try the exporting to text and re-importing, but don't think it is in my tables that my corruption exists.

Glad they pay me by the hour, no matter if what I am doing works or not haha.
Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
lol

No when I was referring to exporting to text and back I was referring to your forms.
In particular the slow form.  (Can't see why another one would cause you any problems).

However as I recall (again - the thought of scrolling upwards fills me with dread) if you removed your form's startup code all went well yes?
It sounds like the data requirements are killing your application as it's logging in.
(Did you try that suggestion somewhere up there about maintaining an open connection to the target db?)

Does everything that happens upon log on have to happen right then?
Query execution after query execution?
Hitting the system again and again?

I'd try dropping bits of that one at a time.  (On a copy of course ;-)
See if the speed increases slowly - or suddenly with the removal of any one particular item?

I've known shocking response speeds too (albeit the one that stands out in memory was in ad hoc network - with an XP desktop acting as a server.)
A proper server installed helped no end - but the little tweaks like constant connections and minimal data hits can make a real difference too.

There are subtle Jet performance Registry settings that might be worth looking in to.
As well as network configurations if what you're doing drags a lot of data back over the wire (which is always always a bad idea IMHO ;-)

See - i can just waffle on for hours.
It's a gift.
0
 
LenaWoodAuthor Commented:
How do you export to text and back for a form?

Yes, all seems to go fine if I remove all the code from the sign in form (except for the fact you can't sign in).  I have removed the other forms and have the same issue as long as the sign in form stays the same.  I have added the other forms back in (startup, splash and yesno) and just had the code behind the sign in button that opened the main menu form and all worked great...except that again, it didn't recognize who was signed in as there was nothing needed in the Username of Password field.

About the suggestion of maintaining an open connection to the target db...I am not sure what that means or how it would work.  If I am in the database changing things and my co-worker opens the database and is "surfing" around, will he notice the changes that have been made to the data?  In otherwords, does this connection always updated?

No, everything doesn't have to happen all right when I have it happen.  The query executuion after query execution doesn't have to happen at all.  It was just done so that messages that were deleted could be removed...but they don't need to be removed..I was just removing them to a different table.  Thought about just deleting them all together, but didn't think I needed to do that either.

I can waffle on for hours as well....a wonderful gift we have :-)  I am honored to be in such good company!
Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
To be fair - exporting db objects to text and back isn't documented - but is quite commonly used.

'Export
Application.SaveAsText acForm, "YourFormName", "C:\YourFormNameIsh.txt"
'Import
Application.LoadFromText acForm, "YourFormName", "C:\YourFormNameIsh.txt"
(Have a look - it's nice to know what forms are made up of huh?  Your code will be in there too ;-)

But I honestly don't think that (or any cleanout) will help.
When you stop hitting the data - it's quick.  It's surely very likely that it's the act of hitting data.

If you have cleanup operations to do - it's almost always best to perform those as you exit an application.
Users rarely care then.  They're putting on their coats and staring at the door/clock.  ;-)
I'd certainly suggest at least removing the cleanup operations from your startup.

Perhaps I didn't mention a persistent connection in this question.
It could just be as simple as opening a DAO object connected to your BE.
A recordset is usually employed for this purpose.
It just makes sure that Access isn't creating and destroying connections to the BE unnecessarily between data operations (it can't if there's a permanent recordset open).

And even with those in place - then start dropping the other things...
One by one. :-)
0
 
LenaWoodAuthor Commented:
One thing I have noticed (and not sure if this would fix my problem) is that my reference to Microsoft DAO 3.6 Object Library needs to be 3rd in line for things to work on 2000 computers, but for some reason it keeps moving to the end of the list when I do things with the programming on 2003 computer.  How can I make it stay in place?  Anyone have any Access Super Glue?

Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
"for things to work"
i.e. opening recordsets?

I'd imagine the problem is just non library qualified declarations and the presence of ADO and DAO?

dim rst as Recordset
as oppose to
dim rst as DAO.Recordset

I don't know why your library is moving though.  It shouldn't - and mine don't.
Are you creating the database from scratch in 2003 and importing everything in?
Or converting a 2000 version up?
Converting should maintain your references just as they were.
From scratch - DAO should still be above ADO - but you'll have to decide on the order of other references).

FWIW I also doubt this is anything to do with your woes.
Although... it's odd.
0
 
LenaWoodAuthor Commented:
I am building the database in 2003 (using the 2000 File Format Option).

I don't have any of my recordsets declared as DAO.Recordset...do I need too?

Thanks!
Lena
0
 
rockiroadsCommented:
oh Lena, I never got a chance to revisit this

It seems LPurvis is on the case for you regarding your final problem.

I was going to suggest putting in some logging recording steps and date/time. This is only to help you track the progress and try to identify what is actually slowing it down.

something like this

public sub DumpIt(byval sLine as string)
    debug.print Now(), sLine
end sub


Now in your code, just call


DumpIt "In this form, this line"

etc

Once up and loaded, look in immediate window and see the results (or u can change it so it writes to a file)


Regarding DAO. if u use them recordsets, u have to add the DAO Object Library recordset
0
 
Leigh PurvisDatabase DeveloperCommented:
The DAO library is present - that's not a problem I'm sure (I doubt you're opening DAO database and recordset objects without DAO installed... even though it's possible).

It's just the ordering (and hence preference) of the libraries.
If ADO is higher than DAO then
Dim rst As Recordset
will naturally be assumed to be an ADO recordset.
And you'll then get type mismatch errors - or vice versa etc.

It's become more common to delcare recordsets with the library predicate, due to a tendancy to have both ADO and DAO libraries present in a project.
0
 
LenaWoodAuthor Commented:
OK...here is what is happening.

I set the order of the references making sure that the DAO is third in line.  Compile the database and save any other changes made.

I start the database and sign into it (I don't use built in security - I am using tables to store the information).  Matt (My co-worker) signs into the database and moves around doing this and that and quits the database (using a command button with the line DoCmd.Quit).

When he tries to sign back into the database, the first form (Splash) opens and closes and then NOTHING.  He can use the X in the upper hand corner to close the database.  When he tries to restart the database it he gets the hourglass, then it disappears.  Nothing shows up.  He has to do a Control Alt Delete and use the task manager to end the MSACCESS process that is running.

When I look at the references after he has done this, the DAO references is at the bottom of the list of the selected references.

If he tries to start the database when I don't even have it running, the splash screen shows and doesn't disappear.  Again he can close the application using the X, but can't try to restart it.

I went through and made sure that all of my db as database is db as DAO.Database and rs as recordset is rs as DAO.recordset and still nothing.

This is so frustrating, I don't know what to do to solve my problem.  I have tried starting over and making the part that didn't work work and then import and things fall apart (or maybe they fell apart before that).  If I don't have the DAO reference checked then I get other errors while compiling my database (such as a control doesn't exist on a form that is there if I have DAO checked).

If I am fighting a losing battle, please let me know before I find a low bridge with a puddle under it to jump off of (afraid of heights and I can't swim).  Could this all be caused by a bad install?  Should I ask my IT department to reinstall the software and see if this fixes the issues I am having?  I am using Access 2003 with SP2 installed.

I know there have been solutions offered here but so far nothing has helped me, although I have learned a few things along the way.

I think if I could get the references to stop moving that would be a start.
Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
Unless you were using Late binding before it was suggested here - then removing the DAO library will cause your code to fail to compile.
(You'd have had to declare your DAO objects like Dim db as Object and Dim rst as Object)

Do you close and set to nothing your DAO objects whenever you're finished with them in a procedure?
(I don't really see why that would persist into your next instance of the application though - but it often explains a failure for the Access MDI window to close).

The one thing I can't let go of is that you don't get these problems without your startup code.

You mentioned that without running it means certain logon stuff doesn't happen.
Removing all the code is overkill naturally.
As I suggested - why not just remove parts of it?
Try leaving your logging in stuff.
See if it's slow with that.
See if it's fast without it?
Try the persistent connection (remember? ;-)

And if it's not revealing then as RR suggests - perhaps a logging to check for timiings.
(Hell - even a messagebox every so often is a simple way of doing that! :-)
0
 
LenaWoodAuthor Commented:
I removed the form and start up code that seemed to be causing the problem - or so I thought.

I remember the explaination about the persistent connection...how do I do that?

I can't believe I am having such problems with this database.  I am so close to telling them they will never get their "limo" and have to drive that beat up Pinto for a while.

Thanks for sticking with me.  Almost quitting time (15 minutes).  I am going to go home and drown myself in a glass of milk and stuff myself with cookies.  (It is times like this I am glad I don't drink (much)).

Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
Persistent Connection:
Declare a module level (or public) recordset object variable.
Open it.
Don't close it until your application closes :-)

So even without the form that loads first and was being slow - you get the slowness anyway?
Is there still startup code running?

I often wish I drank more.  :-)
0
 
LenaWoodAuthor Commented:
It isn't what I would consider slowness....I would say Stops.  The first form (my splash screen) starts up ok (a little slower than the first time).  The only code on it is the timer that causes it to close .5 seconds after it opens (boss says even that is too long)...then the frmSignIn is suppose to open.  It does the first time.  Then when he closes the database and tries to open it up again that is where it "Hangs".

Persistent Conntection would be in a module I would put:

Public rsPersistent as RecordSet

Public Sub (or Function?) OpenThatConnection ()

Dim MySQL as String

MySQL = "SELECT * FROM tblUsers"
set rsPersistent CurrentDb.OpenRecordset(MySQL)

End Sub (or Function)

Is that what you mean?

And then on my form that I use an unload event I would close the persistent recordset?

As for the drinking...after this I will owe you more than 500 points...I will owe you a good stiff drink...hope you like the fu-fu drinks like Baileys! :-)

Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
Yep - that's essentially the crux of the persistent recordset.
Then (assuming no huge speed boost - would be unlikely) you really need to log your code's data progression.

To know what is executing when.
What's taking the time.
If you step through the code in break mode does it not still execute slowly?

Baileys... always makes me think of Christmas. :-)
0
 
LenaWoodAuthor Commented:
Things are suppose to look better after a nights sleep - but not for me :-(

I tried the persistent idea and get it type mismatch error.  Here is the code:

Public rsPersistent As Recordset

Public Function OpenThatRecordset()

    Dim MySQL As String
   
    MySQL = "SELECT * FROM tblUsers"
    Set rsPersistent = CurrentDb.OpenRecordset(MySQL)

End Function

It highlights:      Set rsPersistent = CurrentDb.OpenRecordset(MySQL)

Please tell me what I am doing wrong.

Lena

PS Vodka is sounding better and better haha
0
 
LenaWoodAuthor Commented:
Nevermind...I didn't have my reference set to DAO 3.6
0
 
Leigh PurvisDatabase DeveloperCommented:
OK - and of course
Public rsPersistent As DAO.Recordset
would be safer with both libraries present.  :-)
0
 
LenaWoodAuthor Commented:
As soon as I referenced DAO 3.5 the database fails to work on the other computers correctly and consistently.

I have a request in to our IT department to do a fresh install of Access to see if that will help fix the problem.  We will see.

Lena
0
 
Leigh PurvisDatabase DeveloperCommented:
But have you not had DAO reference for a while now?
Or have you been using ADO?
0
 
LenaWoodAuthor Commented:
Yes...I have always had it referenced.  Old databases that I built using 2000 doing exactly what I am trying to get this one to do on startup work fine.  It is this new one that I am having troubles with making it work.  Throught my databases (old and this new one) I had used rs as recordset and rs as dao.recordset depending on the code and who helped me with it.  I didn't know there was a difference until recently.

I am hoping a fresh install will stop some of the other issues that I have had such as Access giving me an error and telling me it needed to shut down.

I am so frustrated right now.  This whole issue has me feeling like a complete failure cause I can make it work. GRRRR.  Anyway we will see how long it takes IT to come fix my computer for me.

Lena
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Don't mean to jump in, but DAO 3.5 is an Access 95 reference ... if you're using 2000 or greater you'll have to reference 3.6. If you don't have the 3.6 reference, then you may have other issues as well (and a full re-install of Office, not just Access, may be in order).
0
 
Leigh PurvisDatabase DeveloperCommented:
Hey Scott.
Fancy meeting you all the way down here... :-)
(It's a long way down now too).

Lena 3.5 (or 3.51) are indeed Access97 libraries.
Assuming DAO 3.6 is indeed installed on these other machines (which it must be no?) then that should get picked up and used.

(You're not deploying an mde yet are you - and even then I've generally known that to work fine too with DAO versions).

The fact that you have other applications running so happily certainly implies that it is be present.
0
 
LenaWoodAuthor Commented:
It is 3.6 - sorry for the typo.

I just created a simple database on another computer using Access 2003 and I could sign in and out while it was running on the other computer - no problem.

I went to a co-workers computer that has 2000 installed and it started up the first time with no problem.  No go for the second time logging on.  Went to a different co-workers computer (2000) and signed on the first time no problem...second time logging on was a no go.  It still seems to be moving my References around.

The references don't seem to move until I shut down the database and re-open it up.

I am at a loss as what to do.  I could go back to 2000 (and not have help files) and just deal with this issue at another time since sooner or later we will be using different versions of Access (split between 2000 and 2003) until IT can get everyone updated.

Can you hear the SCREAMS!
Lena
0
 
LenaWoodAuthor Commented:
A repair has seemed to do something.  So far everything is working.  I will keep experimenting with this and figure out how to give points for this question.  Although there wasn't an answer to give me, I have learned so much from this posting!

Thanks everyone for your help.  Don't hold your breath...I may be back with another issue...I don't feel like this has been totally resolved!

Lena

0
 
LenaWoodAuthor Commented:
Thank you to Capricorn for teaching me about bat files.

Thank you to Rocki for teaching me about Late Binding

Thank you to LPurvis for teaching me about Persistent Connections.

HUGE HUGE HUGE Thank you for sticking it out with me!  If it wasn't for you all, I would have lost it a long time ago.  Thanks for being a shoulder to lean on :-)

Lena
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Hi Leigh, got lost and found myself back here <g> ...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 30
  • 16
  • 8
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now