VERY bad problem, database runs differently on different machines!

[Maury Markowitz]

We have a large MDB that we host on a file server and can be run from a
variety of machines on our net. It stores NO LOCAL DATA, everything is linked
to two back-end databases, one runnings SQL server, the other another
commercial engine in an accounting system. The basic purpose of the MDB is to
compare the two, then copy over new information found in the accounting
database.

Starting about a week ago, the system has started acting oddly. When run on
my machine columns that are non-null in the database suddenly start returning
null values. The same MDB run on another machine does not have this problem.
It gets worse; last night a query that should return zero rows (it's looking
for changes to data that has not changed -- we looked) is suddenly returning
thousands of rows. This morning the problem went away, then it came back.

We tried running a VERY old copy of the MDB and it did not show these sorts
of problems, but we didn't test very far.

Has anyone seen anything like this before? Remember, NO LOCAL DATA, and the
data itself is fine -- we can look at it with other tools and everything's as
expected.

Maury

 

[mnature]

You might try reading this link, and see if it fits any of your problems.

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[Albert D.Kallal]

First, in place of mdb, you should likey be using a mde.

Further, do you ever have more then one user in that database?

if yes, then you need to read the following:

http://www.members.shaw.ca/AlbertKallal/Articles/split/index.htm

The issue, and quite common is that of broken references.

check out:

Allen Browne
http://allenbrowne.com/ser-38.html

Doug Steele:
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html

Peter Walker:
http://www.papwalker.com/dllhell/index-page2.html


MsKb Articles: 310803, 208218, 209849, 286300

ACC2000: How Access 2000 Resolves Visual Basic for Applications References
http://support.microsoft.com/default.aspx?scid=kb;en-us;248941

ACC2000: How to Resolve Reference Issues in an Access Database
http://support.microsoft.com/default.aspx?scid=kb;en-us;310803

 

[Tony Toews]

We have a large MDB that we host on a file server and can be run from a
variety of machines on our net. It stores NO LOCAL DATA, everything is linked
to two back-end databases, one runnings SQL server, the other another
commercial engine in an accounting system. The basic purpose of the MDB is to
compare the two, then copy over new information found in the accounting
database.

Starting about a week ago, the system has started acting oddly. When run on
my machine columns that are non-null in the database suddenly start returning
null values. The same MDB run on another machine does not have this problem.
It gets worse; last night a query that should return zero rows (it's looking
for changes to data that has not changed -- we looked) is suddenly returning
thousands of rows. This morning the problem went away, then it came back.

I'd wonder if this MDB is suddenly linked to tables in the wrong MDB?
Or linked to the wrong SQL Server database? MDBs or SQL Server
databases which have the same tables or are old?

Run the Linked Table Mgr to double check.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Maury Markowitz]

I'd wonder if this MDB is suddenly linked to tables in the wrong MDB?
Or linked to the wrong SQL Server database? MDBs or SQL Server
databases which have the same tables or are old?

Run the Linked Table Mgr to double check.

We did, no change.

Same thing happened last night again. I attempted to run the process on my
machine, and got nulls in non-null fields. We ran it on another machine, and
the "one query" once again returned hundreds of rows once, and then none the
next time.

This is crazy!

Maury

 

[TC]

When run on my machine columns that are non-null in the
database suddenly start returning null values

How /exactly/ are you determining that (a) the column is defined as
not-null, and (b) it is returning a null?

HTH,
TC [MVP Access]

 

[George Nicholson]

Frankly, it sounds a lot like an buggy/intermittent network connection:
sometimes your FE's are "seeing" both BEs, sometimes they are only seeing
one of them, etc.

Any chance you are relying on a wireless connection (and shouldn't be)?

 

[Maury Markowitz]

How /exactly/ are you determining that (a) the column is defined as
not-null, and (b) it is returning a null?

rstUpdates("SettleCash"), hover over than and you see "null". It's real too,
the only reason I noticed the problem in the first place is when it attempted
to copy this into our SQL Server and failed a constraint.

Maury

 

[Maury Markowitz]

Frankly, it sounds a lot like an buggy/intermittent network connection:
sometimes your FE's are "seeing" both BEs, sometimes they are only seeing
one of them, etc.

Any chance you are relying on a wireless connection (and shouldn't be)?

No wireless, but that is my general feeling too. But I can't imagine any way
to debug that! An alternate theory is that the non-SQL Server DB is
internally corrupt, or the ODBC link to it is. But again, I can't think of a
way to debug this.

Maury

 

[TC]

Can you show me the exact code that opens the recordset (and all
assocated variable declarations etc.)?

Also - a serious question - how exactly are you determining that the
column is defined as not-null?

TC [MVP Access]
(of for today, it's 5:30am here!)

 

[Maury Markowitz]

Can you show me the exact code that opens the recordset (and all
assocated variable declarations etc.)?

No. But this is not the problem. This code has been successfully used in
production for several years now. All of a sudden it's broken. This is not a
code problem.

Also - a serious question - how exactly are you determining that the
column is defined as not-null?

It's in the table definition.

Please don't think I'm blowing you off here, I _do_ appreciate that you're
trying to help. But you're going down the wrong path. This is not some sort
of "you're doing it wrong" problem, something is wrong with the database or
our connection to it. It used to work all the time, now it works some of the
time, and it's continuing to get LESS reliable as time goes on.

Maury