Access 2003 crashes on new server.

We have a fairly large access data base and are planing to move it over to a new terminal server machine.  The data base has a front end and a seperate back end.
As always we had to make sure we have all the referances, which we do.

We are coping the front end to the new C drive and the back end to a new F drive.  When the front end opens it will crash on a line code that is checking for any possible appointments for the particular user.  If I remove the code then it will run as expected.  

This is a 2003 access database and we are moving from a 2003 server to a 2008 R2 server.

What I don't understand is why this will run without any problems on the 2003 server and
fail on the 2008 server.  Is there something different with the referances etc. etc.
BillxxxxAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
The usual:

Make sure that all your Software is up to date and fully patched (Windows AND Office)
Run the compact/repair utility on the database
Compile any code (in the vba window, click: Debug-->Compile)
Run the compact/repair utility on the database again

Other questions:

Is this an issue for only this one user, or for all users?
Can you post the code, and indicate what line is crashing?

JeffCoachman
0
BillxxxxAuthor Commented:
Hi Jeff

Everything has been updated and compiled numerous times.
We have 10 machines that run the same front end without any problems.

If I make any changes in the front end I copy it to location where everyone else
can click on a button and update there front end.  So in a sense there may be some
differance. I have been doing this for over 15 years so I don't think that is the problem.

I did not mention that this also happens on a windows 7 laptop that I use.  I am thinking of taking all the referances from my machine and putting them on the new machine?

Thanks for your help.

Below is the code:

StrStatus = "open"
    StrUsername = User_name
    stDocName1 = "Appointments"
   
    SqlAppointments = ("SELECT * FROM Appointments WHERE (((Appointments.Employee)= '" & StrUsername & "') AND ((Appointments.MeetingDate)=Date()and (Status)='" & StrStatus & "')) order by meetingtime")
                                                                                                                                                                                              '  order by SnoozeFromDate
    Set Mydb = CurrentDb
    Set Myrs = Mydb.OpenRecordset(SqlAppointments)
   
        If Myrs.EOF = False Then
       
            DoCmd.OpenForm stDocName1, , , stLinkCriteria1
            Else
                If BlnShowTodaysSchedule = False Then
                MsgBox "There Are No Schedule Events Today For" & " " & StrUsername
                BlnShowTodaysSchedule = True
            End If
        End If
   
End If
0
Jeffrey CoachmanMIS LiasonCommented:
...and what line is it crashing on?

You may not be able to simply "Take" a reference from one machine an put it on another.

These references and libraries need to be installed properly and may will not work correctly (or worse cause corruption) on machines that they may not be compatible with..
0
BillxxxxAuthor Commented:
This is where it crashes   SqlAppointments = ("    in debug mode you can walk through it
and the following lines untill you get to  If Myrs.EOF =  So thats here it really crashes.
0
Jeffrey CoachmanMIS LiasonCommented:
...and what is the error?

AFAICT, as long as the variables:
StrStatus and StrUsername
...have valid values assigned to them, the code should run fine

To be sure, ...do this before the error line to see if everything looks OK:
msgbox "StrTatus= " & StrStatus & vbcrlf & "StrUsername= " & StrUsername & vbcrlf & "The SQL is=" & "SELECT * FROM Appointments WHERE Appointments.Employee=" & "'" & StrUsername & "'" & " AND " & "Appointments.MeetingDate=Date() " & " and " & "Status=" & "'" & StrStatus & "'" & "order by meetingtime"

I took the liberty of slimming the SQL for readability...


Also try using: CurrentDB(), ...instead of CurrentDB


JeffCoachman
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.