Solved

Access 2003 adp performing extremely slow and strange things pop up

Posted on 2013-02-05
39
417 Views
Last Modified: 2013-02-10
Hi all, i have a strange scenario where a new pc was setup with windows 7 sp1 64 bit OS and office 2003 sp3, now when opening an access project adp file connected to sql server 2005, attached error message pops up, after clicking ok, everything opens, however certain screens are performing very slow compared to other pc's with the same setup, additionaly certain pass through queries dont open, just getting timeout expired..can someone give some clues here?
0
Comment
Question by:bfuchs
  • 19
  • 19
39 Comments
 
LVL 16

Expert Comment

by:terencino
ID: 38858126
Hi can you send the error message, it didn't come through. Also you might want to add to the Access topic rather than Excel! Requesting attention will help with that
...Terry
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38858159
file attached.
closetheprogram.doc
0
 
LVL 16

Expert Comment

by:terencino
ID: 38858163
Sometimes if a Windows or Office update has come through, you first need to open up Access just by itself from Start > All Programs > Microsoft Office > Access. Then it will run through all its configuration and be ready for action. Can you try that first?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38858169
hi, thanks for your reply, ok will do that & let u know.
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38860726
thanks
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38861480
hi, we just figured out that this issue is happening to all pc's we recently purchased from Fujitso, in case you encountered such a problem and have a solution, please let me know, thanks
0
 
LVL 16

Expert Comment

by:terencino
ID: 38861845
Hi did you try my suggestion?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38861866
oh yes, this was the first thing i tried today, however after that when opening the access file mdb format, it was crashing..
0
 
LVL 16

Expert Comment

by:terencino
ID: 38861898
A lot more questions:
1. Do you have administrative access to your PC
2. When you opened Access by itself, did it go through the configuration process? Did it give you any messages?
3. Have you checked macro security on Access and set it to low, and allowed blocked expressions?
4.  What antivirus do you have installed? Can you uninstall it and try the program again?
5. Is the Access program on the network?
6. Do you use a shortcut to run it, if so can you check the shortcut settings
Let me know
...Terry
0
 
LVL 16

Expert Comment

by:terencino
ID: 38861938
Al;so, can you check the User Account Control and make sure that is set to "Never notify"
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38861987
1-yes i have admin rights
2-no messages at all
3-macro set to low
4- we did a system restore during the day to clean out all recently installed programs, orig we had avg 2011, now there is none.
5-access its local
6-even when copied the file to the desktop its not working
btw- its a mdb file, not a adp as described on initial question.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38862075
When the MDB crashes do you get the same message as before "There was a problem sending the command to the program".

Does the MDB need any old DLLs or OCX controls eg common dialogs?

Can you start the MDB by holding the shift button down while opening it from Access Open menu? This will disable any startup code. Then can you check for add-ins (Tools > Add-ins > Add-in Manager). Then check for any missing references in the code section (Alt+F11 to bring up the VB Editor)
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38862604
1- yes uac is set to never notify
2-yes I do get the above message even when it crashes
3-program is compiling fine, checked there are no broken or missing references
4-while holding the shift key I do get to open it, its the code behind the main form that’s crashes, its at the first line of code in a dlookup field on a table (checked there the table and value looking for exists)
Additional notes, while going into debug, I realized that the debug tools are not functioning properly there, must commands like step into/out or even the F8 key does not function...maybe this can gives us a clue???
One more thing, the compiled version in mde does not crash, however symptoms like described on original question appears there.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38862622
This is strange. Can you check Use Access Special Keys is check in Tools > Startup? Then close and reopen the database with Shift again and see if you get the same issues with debug?
Access Special Keys
0
 
LVL 16

Expert Comment

by:terencino
ID: 38862627
Can you also check for:
5. Does the MDB need any old DLLs or OCX controls eg common dialogs?
6. Then can you check for add-ins (Tools > Add-ins > Add-in Manager)
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38862659
1- use special keys was checked.
2-no old dlls or ocx there
3-nothing under add-in manager.
just realized that when running the mde file, i did not get the pop message now, but its there in the open source file.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38862723
Do you have Adobe Acrobat installed
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38862746
yes, adobe reader x
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38865685
fyi-i uninstalled adobe but everything stayed the same..
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 16

Expert Comment

by:terencino
ID: 38865781
Do you have a switchboard as the main form?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38865970
yes
0
 
LVL 16

Expert Comment

by:terencino
ID: 38866011
Adobe Reader should be OK. Acrobat though is much more invasive. So you could reinstall Reader. I need to do some research on the switchboard issue. It is encouraging though that the MDE works OK, did you ever have a problem with that one?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38866105
the problem with debug tools its not happening now, possible because last night i was connected remotely thru logmein..dont know.
just want you should take into account the factor that this is only happening to pc's manufactured by Fujitso, the others are Dell computers and are working perfectly fine!
additional, just tried now with a different Fujitso pc and its not crashing, (not even that message pops up), therefore i would only focus now on the question below
when trying to open the pass-through query "SELECT View_HC_PersonalFileInfoQry.* from View_HC_PersonalFileInfoQry Where  len(MissingDoc)>0" it hangs, and after a while i get the message "ODBC-connection to Home Care Failed", all other pc's are fine.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38866467
Well that's all good news! Is it possible the new PCs are missing the Home Care DSN? Probably installed locally. Go to Start > Control Panel > Administrative Tools > Data Sources (ODBC). Find the Home Care DSN and click Configure > Advanced. If there is a login name and/or password listed there, make sure it is correct. Then test the connection, and make sure the path specified to the database is correct. (Configure > Select).
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38866668
1- the dsn are all there, as described this is happening on this manufactures pc's only.
besides, all tables are opening fine, even the view "View_HC_PersonalFileInfoQry" by itself its opening, its just the pass=through query based on that view which is making trouble..
0
 
LVL 16

Expert Comment

by:terencino
ID: 38866734
Can try these slight modifications to the query:

"SELECT View_HC_PersonalFileInfoQry.* from View_HC_PersonalFileInfoQry Where  len(View_HC_PersonalFileInfoQry.MissingDoc)>0"

"SELECT View_HC_PersonalFileInfoQry.* from View_HC_PersonalFileInfoQry Where  NZ(len(View_HC_PersonalFileInfoQry.MissingDoc))>0"
0
 
LVL 16

Expert Comment

by:terencino
ID: 38866740
And can you also
1. Check Tools > Options > Advanced Tab and uncheck the box next to "Ignore DDE requests". Then exit Access and retry
2. Exit Access, check in the registry for this key, and rename to NOddeexec, then try again.
HKEY_LOCAL_MACHINE\SOFTWARE\Classes\Access.Application.11\shell\Open\ddeexec
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38866814
tried all, nothing helped,
so renamed back to ddeexec.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38866872
Sorry it is quite frustrating, this type of error occur for a lot of different issues, so troubleshooting is tedious. You said before that one other Fujitsu machine runs it without a problem, do you know if that is Windows 64-bit? Also, can I also confirm that not all the Fujitsu PCs are affected only just one? And that you still get the message "There was a problem sending the command to the program"?

Can you also confirm if the SQL above is run by the DoCmd.RunSQL command? If so can you change that to Application.CurrentProject.Connection.Execute command instead?
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38866937
1-no, all Fujitsu pc's are having this pass-through query problem
2-all have windows7 64 bit.
3-since the above message "there was..." its only happening in one pc. i would let it go for now.
4-its a mdb application, trying to open a saved pass-through query, dont think that code you're mentioning is meant to use there, only in an adp.
0
 
LVL 16

Expert Comment

by:terencino
ID: 38866945
Are the PCs that are working 64-bit or 32-bit Windows? Can you send a screen shot of the whole screen where it breaks on the SQL query error
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38866957
yes, they are all win7 64 bit.
attached screenshot.
odbc-2-7-13.doc
0
 
LVL 16

Expert Comment

by:terencino
ID: 38866976
Can you open the HomeCare DSN from ODBC Data Source Administrator on that machine and test that it is connecting successfully?ODBC Test
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38867001
see attached
odbc-2-7-13.png
0
 
LVL 16

Accepted Solution

by:
terencino earned 500 total points
ID: 38867011
Well that's good, the connection between the machine and the SQL server works. The connection between Access and the DSN does not. Can you run the SQL query progressively, initially

"SELECT View_HC_PersonalFileInfoQry.* from View_HC_PersonalFileInfoQry"

then adding each WHERE condition bit by bit to see what breaks it
0
 
LVL 3

Author Comment

by:bfuchs
ID: 38867041
hi terry, i think we got it, its the odbc connection string setting that was causing it, see code below     qd.Connect = "ODBC;DSN=HomeCare;UID=sa;PWD=mypwd;DBQ=HomeCare"
had to change for the following ODBC;DSN=HomeCare;UID=sa;PWD=mypwd;DATABASE=HomeCare;Network=DBNMPNTW;Address=\\WGCTS\pipe\MSSQL$SQLEXPRESS\sql\query
however, before accepting this solution of yours, i will have to wait for next week, when i get a chance to test on other pc's that are currently working fine, how will this affect on them.
I really appreciate your efforts, thanks allot!
0
 
LVL 16

Expert Comment

by:terencino
ID: 38867158
Awesome, fingers crossed that is the problem!
0
 
LVL 3

Author Closing Comment

by:bfuchs
ID: 38874540
actually, looking inside on the query that helped me find what was wrong, the connection string.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Formula to convert ranked data 2 11
Stored procedure 23 23
Conditional formatting based on date 2 12
Update in Sql 7 0
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

760 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

22 Experts available now in Live!

Get 1:1 Help Now