Table not available for Append query

[Mo]

Hello All,

Using Access 2003.

I have a strange situation when I create an append query. One of the
tables is not available from the list of tables that is presented to you
to select from when the 'Append to' window pops up. All the other tables
(either linked or in the current db) are there except for this one.

I've tried refreshing the link to the back end tables. All the linked
tables are visible in the database window, including the one that I
can't see in the Append query. I can open it up and see the data, so
there doesn't appear to be anything unusual about this table at all.

Anyone any idea what might be happening?

Thanks very much,

Mo

 

[Evi]

If you create a Query based on this table, is that also invisible?
Evi

 

[Mo]

If you create a Query based on this table, is that also invisible?
Evi

No, it's only for Append queries. Everything works fine except for this
one thing, which seems completely arbitrary.

 

[Evi]

So what if you create a query based on the Invisible table and use this
query to do your Appending instead of the table itself?

(of course, I'd still like to know, as you do, why this is happening)
Evi

 

[John Spencer]

Is the table you can't use a linked table?

If so, what is the source of the table. Can you open the table and add
a record directly?
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Mo]

Is the table you can't use a linked table?

If so, what is the source of the table. Can you open the table and add
a record directly?
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Yes the table is linked. I can open the table itself and build queries
and do everything you'd want to do - edit, add, delete records.

The only thing that I can't do is append records to it via the 'query
type' option on the menu bar. I'm fairly certain that I could append to
it if I wrote the SQL myself.

This is the puzzle - why does this one function not work and why only
for this table?

 

[Mo]

Yes the table is linked. I can open the table itself and build queries
and do everything you'd want to do - edit, add, delete records.

The only thing that I can't do is append records to it via the 'query
type' option on the menu bar. I'm fairly certain that I could append to
it if I wrote the SQL myself.

This is the puzzle - why does this one function not work and why only
for this table?

OK, some additional information. Imagine that you are using the query
grid to do an append query. Click on the 'query type' option on the menu
bar at the top and select 'Append Query'. A window opens and you are
prompted to choose a table.

When I get this prompt, for some reason the first table in the tables
list (listed alphabetically) does not appear. So if I had a table called
'TableA' and another called 'TableB', 'TableB' would be the first
option on my list when doing the append query. 'TableA' would not
appear at all. BTW, 'TableA' behaves normally in all other situations AFAIK.

I don't know if this behaviour is repeasted across all my Access
databases or if this problem is peculiar to this one database. I'll post
when I know more.

Thanks for any input.

 

[Jeanette Cunningham]

Jumping in here to say that I have read of this problem previously on this
discussion group, earlier this year.
You could search to find the info from the earlier posting.


Jeanette Cunningham

 

[Evi]

Well remembered Jeanette. The link is here
http://groups.google.co.uk/group/microsoft.public.access.queries/browse_thre
ad/thread/410eadb6f6a9bc9d/732870e4cb681ac0?hl=en&lnk=st&q=Table+not+visible
+in+Append+Query+list#732870e4cb681ac0

The first question asked was, does the Append Table have a Primary Key.

The conclusion was that the writer had the table, to which he wanted to
append, added to the query with which he wanted to do the appending (or
added to the query on which the Append Query was itself based).
However the writer does not write back to say if this solved the problem.

It's not always easy to put together the correct search string and then sift
through the multidude of irrelevant results.
Evi

 

[Mo]

Well remembered Jeanette. The link is here
http://groups.google.co.uk/group/microsoft.public.access.queries/browse_thre
ad/thread/410eadb6f6a9bc9d/732870e4cb681ac0?hl=en&lnk=st&q=Table+not+visible
+in+Append+Query+list#732870e4cb681ac0

The first question asked was, does the Append Table have a Primary Key.

The conclusion was that the writer had the table, to which he wanted to
append, added to the query with which he wanted to do the appending (or
added to the query on which the Append Query was itself based).
However the writer does not write back to say if this solved the problem.

It's not always easy to put together the correct search string and then sift
through the multidude of irrelevant results.
Evi

Thanks very much Evi and Jeanette for your help!

Mo

 

[Evi]

Which was the solution Mo?
Evi

Thanks very much Evi and Jeanette for your help!

Mo

 

[Mo]

Which was the solution Mo?
Evi

The thread didn't really contain anything we've not discussed. So the
problem still remains although it hasn't been a major issue. Still a bit
worrying though.

If I find out anything else I'll post it here.

Mo

 

[Jeanette Cunningham]

Mo,
is the linked table in the backend database?
is the linked table an access mdb table?
is the linked table on a network and the other tables are not?
is the linked table in some other sort of database?


Jeanette Cunningham

 

[Jeanette Cunningham]

Mo,
I would like to try to reproduce this problem.
Would you supply
--The names of your main tables.
--The primary key field names and data types
--The foreign key field names and data types
--The relationship between each table.


Jeanette Cunningham

 

[Mo]

Mo,
is the linked table in the backend database?
Yes

is the linked table an access mdb table?
Yes

is the linked table on a network and the other tables are not?

Make no difference if they're sitting locally on on the network

is the linked table in some other sort of database?

All the linked tables are in the same Access 2003 db.

 

[Mo]

Mo,
I would like to try to reproduce this problem.
Would you supply
--The names of your main tables.
--The primary key field names and data types
--The foreign key field names and data types
--The relationship between each table.

Hello Jeanette,

I'll try and get you what you need in the next couple of days - a little
bit busy at the moment!

Mo

 

[Ruth]

I am having the same problem, but the table that won't display is not
linked. It is actually in the database.

I'll post if I have any success discovering what the problem is.

Ruth

 

[Ruth]

Here's what I have figured out so far:
While surfing for ideas, I found another person having this problem
and he was pointed to the MS KB that had an article about problems
after installing SP3 for Office. (http://support.microsoft.com/kb/
945674) This computer has had SP3 applied, but I have another that
hasn't. The suggestion above about trying the DB on another computer
made a lightbulb go off, so I headed over to the other PC to try it.

I was able to see the hidden table immediately. However, when I tried
to do the append query, it would fail for all records due to key
violoations. I was using the contents of a form control to populate
the "ReviewID" field. The ReviewID source was an AutoNumber field set
to Long Integer. The accepting field in the "append to" table was set
up properly, so I decided to output the query result to a new table to
see what data wasn't matching.

When I did that, the ReviewID field output as Binary and the field
size was 510 characters. I'm not able to spend the time
troubleshooting to figure out what's up with the AutoNumber function,
but I suspect that it has something to do with Office SP3. I replaced
the autonumber data type with a text field (I'm going to force the
user to create a key ID- yucky, but it will work/have to do) and the
append works just fine.

Best of luck with your issue-- I hope this helps.

Ruth

 

[barlowcj]

Hello

I had this problem as well. Bug in Access. Simple workaround is to create a table and save it with the name "aaa" or something similar that will ensure it is first table alphabetically. The table you want will then appear in the append to combo.

Regards
Chris