[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

intermittent #error in a notes field in MS Access

Posted on 2013-02-06
6
Medium Priority
?
322 Views
Last Modified: 2013-08-22
I have a ms access front end (2003) and a sql server 2005 back end. I have about 100 users. There is notes field on the main form. 3 or 4 users have reported that about once a week they see #error in the notes. The field is varchar(1000) in sql server and memo data type in the access linked table. The control source is just the database field , there is no calculating going on. Users access the front end with a mde file. Most of them get access through a remote desktop. The remote desktop server has windows 2008 server(32bit) on it. Only one record at a time seems to have this problem and only the one field. The default view is "single form" so they only see 1 record at a time.

 Apparently this has been happening for a while but went unreported. Closing the application and reopening fixes it. And I think just navigating away and back also fixes but havn't confirmed that yet. When I say fixes I mean the regular notes show up. I have been unable to recreate the problem ever. 1 person said they were running the mde from their local computer, the rest from the remote desktop. I make updates to the mdb and create new mde's about once a month. I always compact and repair before making the mde.

All my googling for #error only turned up problems with calculations and corrupt db's. And as I said there is no caluclating happening. Corruption seems less likely since I have a sql server backend. Any suggestions?
0
Comment
Question by:Grazzhoppa
[X]
Welcome to Experts Exchange

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

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38861781
"Corruption" is a vague term that can apply to almost any gremlin...
;-)

A lot of issue across Networks can do with Network delays or bottlenecks (timing issues).

Many times you can tweak the ODBC settings to minimize this.
0
 

Author Comment

by:Grazzhoppa
ID: 38861810
I am using the sql server  driver version 6.01.7601.17514

I would have used sql server native client 10.0 but none of the remote desktops actually had it installed. I can get it installed and update my dsn. Do you think this could help?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38862474
<Do you think this could help? >
Again, try the ODBC setting first.
This is a lot less disruptive than changing SQL drivers...

The published "limit" of concurrent users for a properly split Access database is 255.
In reality it may be closer to 50 or so.
So 100 seems to be pushing it a bit...

There are always folks who claim they are getting over 100 users, but t is never clear if all of these users can all log on and work at the same time (concurrent)

To be sure, does each user have their own copy of the Frontend on their local machine...?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Grazzhoppa
ID: 38862538
Yes each user has their own copy of the front end. No there is not 100 concurrent users. Probably not more than about 40 connections to the sql server database at one time but could be more. When you say "odbc setting" , are you referring to the timeout setting in Access? Im not sure what you mean. I don't see why changing drivers would be that difficult. I could just install it on a couple remote desktop servers. Then change the dsn that is used during table linking to point to the new driver.
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 1000 total points
ID: 38864070
<"odbc setting" >
Not sure I remember where they were in Access 2003 (I think under "Advanced Options"

But there are a couple of setting there that might help...

You can change drivers if you like...

You can also:
Make sure the back end is compacted on a regular basis
Make sure that you compact the Front end on each revision (and compile the code)
You can even try creating a blank new db and importing all the objects
You can try Decompiling the FE and BE...
Make sure you have all service packs in stalled on the Server and all the desktops...

Again, without much to go on here, I can only throw out guesses.
0
 

Author Closing Comment

by:Grazzhoppa
ID: 39431079
this problem was never resolved.
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

650 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