A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

A2k lookup field corrupt on some machines



 
 
Thread Tools Display Modes
  #1  
Old June 14th, 2008, 09:00 PM posted to microsoft.public.access.tablesdbdesign
Alex
external usenet poster
 
Posts: 1
Default A2k lookup field corrupt on some machines

Hello,
Hoping someone can help because haven't been able to find info online,
and it's driving me crazy, and please bear with me thru the background
info, as I'm trying to cover as much background info as possible.

An access table was setup so that everyone (5 or 6 users) was opening the
same mdb, via a shared folder on a server, and they had 2003 on their
machines but were connecting to and opening an A2k mdb file.
The problem started with 1 machine and spread to 1 other, but had not
affected all others, yet.

The problem was that users would open a table, and there was a
lookup/dropdown, with a query string to another table, pulling approx 4
or 5 fields. One day the bound field was just blank. The other fields
and all other data in the db shows up fine, but just not this one field.
The field is there, but it's blank.

I did a repair, but no effect. I loaded 2003 on the server, ran the
latest Jet update, created a blank mdb and turned off things like auto
correct, etc. and imported all of the objects. I didn't split the db, per
say, but I went to the client machines and created a blank db, turned off
the reccomended default settings, and imported all of the queries and
reports, and linked the tables.

The client machines with the corrupt or missing lookup still showed the
same problem. Instead of a query string, I created a query and changed
the order of the fields, and pointed the lookup at this query - this time
the field did not even show up, in the dropdown fields

Again, some machines are fine, but the machines with the original problem
still exhibited this problem.

Is there any info stored in cache on the client? should I delete the
page.file?

Should I have actually split the db, or was creating a new mdb and
brining everything in, and linking the tables the same? I didn't split
it because I wanted to turn off the default settings, and thought that
importing everything and linking would have the same effect.

Has anyone every seen or heard of such a persistent problem?

Any help or info greatly appreciated.
Thanks,
Ax
  #2  
Old June 15th, 2008, 10:38 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default A2k lookup field corrupt on some machines

What you are describing sounds like a known problem with Access 2003 Service
Pack 3. Details:
http://allenbrowne.com/bug-Access2003SP3.html

As the article explains, you will want to download this fix from Microsoft:
http://support.microsoft.com/kb/945674

If different users are opening the database at the same time, you may want
to consider splitting it so everyone has their own separate front end, but
tehy link to the same shared data. If that's a new concept, see:
http://allenbrowne.com/ser-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alex" wrote in message
...
Hello,
Hoping someone can help because haven't been able to find info online,
and it's driving me crazy, and please bear with me thru the background
info, as I'm trying to cover as much background info as possible.

An access table was setup so that everyone (5 or 6 users) was opening the
same mdb, via a shared folder on a server, and they had 2003 on their
machines but were connecting to and opening an A2k mdb file.
The problem started with 1 machine and spread to 1 other, but had not
affected all others, yet.

The problem was that users would open a table, and there was a
lookup/dropdown, with a query string to another table, pulling approx 4
or 5 fields. One day the bound field was just blank. The other fields
and all other data in the db shows up fine, but just not this one field.
The field is there, but it's blank.

I did a repair, but no effect. I loaded 2003 on the server, ran the
latest Jet update, created a blank mdb and turned off things like auto
correct, etc. and imported all of the objects. I didn't split the db, per
say, but I went to the client machines and created a blank db, turned off
the reccomended default settings, and imported all of the queries and
reports, and linked the tables.

The client machines with the corrupt or missing lookup still showed the
same problem. Instead of a query string, I created a query and changed
the order of the fields, and pointed the lookup at this query - this time
the field did not even show up, in the dropdown fields

Again, some machines are fine, but the machines with the original problem
still exhibited this problem.

Is there any info stored in cache on the client? should I delete the
page.file?

Should I have actually split the db, or was creating a new mdb and
brining everything in, and linking the tables the same? I didn't split
it because I wanted to turn off the default settings, and thought that
importing everything and linking would have the same effect.

Has anyone every seen or heard of such a persistent problem?

Any help or info greatly appreciated.
Thanks,
Ax


  #3  
Old June 15th, 2008, 05:15 PM posted to microsoft.public.access.tablesdbdesign
Ax[_2_]
external usenet poster
 
Posts: 2
Default A2k lookup field corrupt on some machines

Hello Allen,
Thank you for the link. As for each having own front end, I mentioned I
did not split the db, per se, but rather created a blank db and imported
queries and reports, and then linked the tables to the main mdb, on the
server, and gave each client a copy of this "manual" front end - will
this not suffice? Is there a difference between this and the access
created split db or front end?

Also, the really really perplexing thing is why some clients show this
behaviour and others don't, even after the changes. Is something held in
cache somewhere, on the client?

Thanks,
Ax

"Allen Browne" wrote in
:

What you are describing sounds like a known problem with Access 2003
Service Pack 3. Details:
http://allenbrowne.com/bug-Access2003SP3.html

As the article explains, you will want to download this fix from
Microsoft:
http://support.microsoft.com/kb/945674

If different users are opening the database at the same time, you may
want to consider splitting it so everyone has their own separate front
end, but tehy link to the same shared data. If that's a new concept,
see:
http://allenbrowne.com/ser-01.html


  #4  
Old June 16th, 2008, 07:23 AM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default A2k lookup field corrupt on some machines

When you created the blank db, linked the tables, and imported the other
objects, you gave each user a separate copy of this front end? If so, the
result is the same as splitting using the Wizard. (In fact, that's the way I
do it rather than use the wiz.)

I'm guessing that the A2003 SP3 machines are the ones that have the problem.
The A2000 users the A2003 users who are on previous service packs probably
don't exhibit the issue.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ax" wrote in message
...
Hello Allen,
Thank you for the link. As for each having own front end, I mentioned I
did not split the db, per se, but rather created a blank db and imported
queries and reports, and then linked the tables to the main mdb, on the
server, and gave each client a copy of this "manual" front end - will
this not suffice? Is there a difference between this and the access
created split db or front end?

Also, the really really perplexing thing is why some clients show this
behaviour and others don't, even after the changes. Is something held in
cache somewhere, on the client?

Thanks,
Ax

"Allen Browne" wrote in
:

What you are describing sounds like a known problem with Access 2003
Service Pack 3. Details:
http://allenbrowne.com/bug-Access2003SP3.html

As the article explains, you will want to download this fix from
Microsoft:
http://support.microsoft.com/kb/945674

If different users are opening the database at the same time, you may
want to consider splitting it so everyone has their own separate front
end, but tehy link to the same shared data. If that's a new concept,
see:
http://allenbrowne.com/ser-01.html



  #5  
Old June 17th, 2008, 12:11 AM posted to microsoft.public.access.tablesdbdesign
Ax[_2_]
external usenet poster
 
Posts: 2
Default A2k lookup field corrupt on some machines

Hello Allen,
Yes for the manual split, and thanks for that tip, too (manual vs. wiz)!

All clients are 2003, but they were connecting to and sharing the same
Access 2000 mdb.

The 2003 clients didn't see this problem right away: 1 showed it with one
table, then a week later the second client showed it with the same table,
then the first client had a problem with another table, and then that's
when I created and gave each their own client, and upgraded the main mdb
to 2003. Now I'll go back and do the hotfix.

But the really strange part is that I changed the lookup query string to
use a query, but rearranged the columns for the the output, but the
corrupt clients now don't show that blank lookup field, at all...which
makes me think the corruption maybe in some sort of cache or temp file in
the client? Just for my own curiosity I may try a scan of the hard drive
using something like win-hex - using different strings to search
for...maybe useless but it'd be interesting to see if anything turns up.

Thanks, again!
Ax

"Allen Browne" wrote in
:

When you created the blank db, linked the tables, and imported the
other objects, you gave each user a separate copy of this front end?
If so, the result is the same as splitting using the Wizard. (In fact,
that's the way I do it rather than use the wiz.)

I'm guessing that the A2003 SP3 machines are the ones that have the
problem. The A2000 users the A2003 users who are on previous service
packs probably don't exhibit the issue.


  #6  
Old June 17th, 2008, 01:39 PM posted to microsoft.public.access.tablesdbdesign
Allen Browne
external usenet poster
 
Posts: 11,706
Default A2k lookup field corrupt on some machines

There's another valid reason why a combo might display as blank, even though
there is a value in the underlying field.

Say a combo has 2 columns: ClientID and ClientName.
ClientID is zero-width, so it shows the client name.
Now you set the RowSource to:
SELECT ClientID, ClientName
FROM tblClient
WHERE (tblClient.Inactive = False);

Now consider a row where the ClientID is an inactive record. In this case,
the ClientName is not in the RowSource query (because of the WHERE clause.)
So there is nothing for Access to display, even though there is a valid
ClientID.

Is that kind of thing possible in your database?

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ax" wrote in message
...
Hello Allen,
Yes for the manual split, and thanks for that tip, too (manual vs. wiz)!

All clients are 2003, but they were connecting to and sharing the same
Access 2000 mdb.

The 2003 clients didn't see this problem right away: 1 showed it with one
table, then a week later the second client showed it with the same table,
then the first client had a problem with another table, and then that's
when I created and gave each their own client, and upgraded the main mdb
to 2003. Now I'll go back and do the hotfix.

But the really strange part is that I changed the lookup query string to
use a query, but rearranged the columns for the the output, but the
corrupt clients now don't show that blank lookup field, at all...which
makes me think the corruption maybe in some sort of cache or temp file in
the client? Just for my own curiosity I may try a scan of the hard drive
using something like win-hex - using different strings to search
for...maybe useless but it'd be interesting to see if anything turns up.

Thanks, again!
Ax

"Allen Browne" wrote in
:

When you created the blank db, linked the tables, and imported the
other objects, you gave each user a separate copy of this front end?
If so, the result is the same as splitting using the Wizard. (In fact,
that's the way I do it rather than use the wiz.)

I'm guessing that the A2003 SP3 machines are the ones that have the
problem. The A2000 users the A2003 users who are on previous service
packs probably don't exhibit the issue.



  #7  
Old June 18th, 2008, 04:35 PM posted to microsoft.public.access.tablesdbdesign
Ax[_3_]
external usenet poster
 
Posts: 1
Default A2k lookup field corrupt on some machines

Hello Allen,
I don't think this is possible, as the query is just a straight "select
x, y, z from abc"

As well, data is returned in that column, by the clients who were not
"bitten by the bug" and still continue to show the info in that column,
even with my new client mdb - it's just that the clients who suddenly
lost showing that info, continue to not show it. I will apply the hotfix
and let you know if it reappears, but it's just so strange...

Thanks, again!
Ax


"Allen Browne" wrote in
:

There's another valid reason why a combo might display as blank, even
though there is a value in the underlying field.

Say a combo has 2 columns: ClientID and ClientName.
ClientID is zero-width, so it shows the client name.
Now you set the RowSource to:
SELECT ClientID, ClientName
FROM tblClient
WHERE (tblClient.Inactive = False);

Now consider a row where the ClientID is an inactive record. In this
case, the ClientName is not in the RowSource query (because of the
WHERE clause.) So there is nothing for Access to display, even though
there is a valid ClientID.

Is that kind of thing possible in your database?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:25 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.