table relationships and subdatasheets

[Billy11]

I'm very new with Access, so I appreciate your patience and help. I created a
database with 4 tables in it and later found that I didn't need one, so I
deleted it. Let's call that Table B. I'm left with Tables A, C, & D. (I had
established a relationship between Table A & B before I deleted it.) Now,
when I attempt to create a subdatasheet in Table A, I get a dialogue box that
says "The table or query name 'Table B' you entered in either the property
sheet or macro is misspelled or refers to a table or query that doesn't
exist." Well, I didn't create a query or a macro. How do I correct this so I
can proceed with the subdatasheets.

 

[ruralguy via AccessMonster.com]

SubData sheets impact the performance of the system. I turn them off.
http://allenbrowne.com/bug-09.html

 

[John W. Vinson]

I'm very new with Access, so I appreciate your patience and help. I created a
database with 4 tables in it and later found that I didn't need one, so I
deleted it. Let's call that Table B. I'm left with Tables A, C, & D. (I had
established a relationship between Table A & B before I deleted it.) Now,
when I attempt to create a subdatasheet in Table A, I get a dialogue box that
says "The table or query name 'Table B' you entered in either the property
sheet or macro is misspelled or refers to a table or query that doesn't
exist." Well, I didn't create a query or a macro. How do I correct this so I
can proceed with the subdatasheets.

Open TableA in design view and view its Properties. I suspect that the table's
Subdatasheet property references TableB. Set this property to [None] and you
should be back in business.

You should also view the Database's Options and turn *off* Track Name
Autocorrect - this feature has richly earned the nickname "Name Autocorrupt".

 

[David W. Fenton]

You should also view the Database's Options and turn *off* Track
Name Autocorrect - this feature has richly earned the nickname
"Name Autocorrupt".

Not meaning to dispute the recommendation that it should probably be
OFF in most cases, I'm finding it useful in a project I'm working on
right now, where all the table fields are prefixed with the prefixes
I only use for variable names. Every text field is named
txtSomething. This is highly annoying to me, as you it's impossible
to navigate a field list by typing the first letter of the field
you're looking for.

In any event, when I'm done doing the renaming and checking all the
dependencies, I'll turn Name AutoCorrect OFF. I think the problems
with it likely come from it being turned on for production use
(where it's just not needed).

It's certainly making my life easier for this preliminary work on
this new project.

 

[John W. Vinson]

Not meaning to dispute the recommendation that it should probably be
OFF in most cases, I'm finding it useful in a project I'm working on
right now, where all the table fields are prefixed with the prefixes
I only use for variable names. Every text field is named
txtSomething. This is highly annoying to me, as you it's impossible
to navigate a field list by typing the first letter of the field
you're looking for.

In any event, when I'm done doing the renaming and checking all the
dependencies, I'll turn Name AutoCorrect OFF. I think the problems
with it likely come from it being turned on for production use
(where it's just not needed).

Good point David - it's been a couple of years but I've done the same: turn it
on, make specific changes, and turn it back off. Thanks!

 

[John Spencer]

I prefer using the shareware product Find and Replace product for this. It is
inexpensive product and is pretty thorough. It can fix the name in the table,
in all queries, in references on forms and reports, in macros, and in my VBA
code. Plus it allows me to make the decision on a case by case basis or
accept change all if I wish.

Find and Replace: http://www.rickworld.com
Has versions to support up to Access 2007 (Beta version only for 2007) -
Shareware (Approx $40-$60 depending on version)

I have no association with the product other than I use it and have paid for
two copies (one personal and one at the office)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I prefer using the shareware product Find and Replace product for this. It is
inexpensive product and is pretty thorough.

I've used Speed Ferret (http://www.moshannon.com) for the same purpose. I
believe it's still version limited: it works with .mdb format files and you
must have Access 2000 or 2002 installed to use it.

 

[David W. Fenton]

I prefer using the shareware product Find and Replace product for
this. It is inexpensive product and is pretty thorough.

But it can't do the same things. For instance, all the integer
fields (long or short) are prefixed with "int". If I use Find and
Replace, I end up with problems in MakeTable queries (this app has
them -- I didn't create them) because of SELECT...INTO.

Speed Ferret could have done what I need, but I only have the A97
version (and because they haven't updated it since A2002 and it
requires A2002 to be able to use it in A2003, I won't upgrade).

It can fix the name in the table,
in all queries, in references on forms and reports, in macros, and
in my VBA code. Plus it allows me to make the decision on a case
by case basis or accept change all if I wish.

None of the search and replace tools I have are smart enough to do
this without causing damage. Sure, I have to change each field name
manually, but I can count on being able to trace the dependencies
and check that everything's been renamed properly. The only
exception is code, but this particular app has very little code in
it, so it's not a big deal.

 

[Billy11]

Thanks, John. I checked the Properties. There are no Subdatasheet properties
listed. There wouldn't be, would there, since I've never created any
subdatasheets? That's where I'm stuck right now.
--
Billy

I'm very new with Access, so I appreciate your patience and help. I created a
database with 4 tables in it and later found that I didn't need one, so I
deleted it. Let's call that Table B. I'm left with Tables A, C, & D. (I had
established a relationship between Table A & B before I deleted it.) Now,
when I attempt to create a subdatasheet in Table A, I get a dialogue box that
says "The table or query name 'Table B' you entered in either the property
sheet or macro is misspelled or refers to a table or query that doesn't
exist." Well, I didn't create a query or a macro. How do I correct this so I
can proceed with the subdatasheets.

Open TableA in design view and view its Properties. I suspect that the table's
Subdatasheet property references TableB. Set this property to [None] and you
should be back in business.

You should also view the Database's Options and turn *off* Track Name
Autocorrect - this feature has richly earned the nickname "Name Autocorrupt".

 

[John W. Vinson]

Thanks, John. I checked the Properties. There are no Subdatasheet properties
listed. There wouldn't be, would there, since I've never created any
subdatasheets? That's where I'm stuck right now.

Access will give you subdatasheets even if you DON'T want them (e.g. if you
define a relationship or a lookup field). Every table has a Subdatasheet
property (open the table in design view and select view... properties); what
you want is an explicit

[None]

in the Subdatasheet property of the table. Is that what's there?

 

[Billy11]

Found it and changed it to [NONE]. I'll see if that does the trick.
Thanks, John!
--
Billy

Thanks, John. I checked the Properties. There are no Subdatasheet properties
listed. There wouldn't be, would there, since I've never created any
subdatasheets? That's where I'm stuck right now.

Access will give you subdatasheets even if you DON'T want them (e.g. if you
define a relationship or a lookup field). Every table has a Subdatasheet
property (open the table in design view and select view... properties); what
you want is an explicit

[None]

in the Subdatasheet property of the table. Is that what's there?