Append Query Problem

I'm having the same problem. The only working-solution I've found is to add a dummy table named, "AAA" to make sure that all REAL tables show up in Append Query drop-down. I'd like to kow if anyone comes up with a more satisfactory solution. Thanks.



jas58 wrote:

Just want to add my name to the list of people who are having this problem...
12-Jun-08

Just want to add my name to the list of people who are having this problem...

-jeff



"Texernie1" wrote:

Previous Posts In This Thread:

On Friday, March 21, 2008 10:40 PM
khenders_98athotmaildotco wrote:

Append Query Problem
I have 3 tables in the database. When I go to create the append query, only 2
of the tables show up in the dropdown to select the table to append to. The
table I need to append to is the 3rd table (named linklist), which doesn't
show up.

I can select one of the other 2 tables. When I try to modify the raw SQL to
use the linklist table, I get a syntax error in INSERT INTO statment when I
try to go back to the grid view.

All 3 tables are part of relationships. I am using Access 2003 SP3.

I need some help or ideas. Thanks in advance.

Keith

On Saturday, March 22, 2008 1:44 AM
Allen Browne wrote:

Append Query Problem
Ouch. Something's haywire, here Keith.

1. Double-check these things:
1.1 You can see your linklist table in the Database window.
1.2 You can open it there and view the data.
1.3 There is no spurious character in the table name (such as a space.)
1.4 It is not an attached table, linked to a back end that is missing.
1.5 This is not a secured database, so it's not a permissions issue.

2. If that checks out, work through this standard recovery sequence:
http://allenbrowne.com/recover.html
It's designed to fix the most common problems in Access.

3. If you are still stuck, see if this query returns your linklist table:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type = 1
ORDER BY MSysObjects.Name;
If it does, type the table name into the Criteria row under the name field.
(This re-verifies the name is correct.)

4. Open the Immediate Window (Ctrl+G), and enter:
? CurrentData.AllTables("linklist").Name
In Access 2000 and later, there are 2 internal canonical lists of tables.
There is a (fairly rare) form of corruption where these 2 lists get out of
sync. If this happens, the table will show up in #3 but not in #4, or vice
versa.

5. See if this query works:
SELECT linklist.* FROM linklist;
If it does, try turning it into a Make Table query, to make a new table. You
could then create a new (blank) database, import the other 2 tables and the
one you just made, create the relationships, and you are back in business.

Note: Don't skip the steps of #1 and #2. Chances are it will be fixed by
that stage.

--
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.

"Texernie1" wrote in message
...

On Saturday, March 22, 2008 10:37 AM
khenders_98athotmaildotco wrote:

Append Query Problem
Allen,

Thanks for the quick reply. Here are the results:

1.1 - Yes
1.2 - Yes
1.3 - No
1.4 - No
1.5 - No
2. Completed - didn't help.
3. Yes to both
4. returns "linklist"
5. Completed all steps including importing into a blank database.

The problem still exists.

If I copy the table to another table not in the relationships, I can see it
in the append query drop down. Could this be a problem with my relationships?

Thanks in advance.

Keith.


"Allen Browne" wrote:

On Saturday, March 22, 2008 10:58 AM
Allen Browne wrote:

So if you copy the table into a new database, it does appear.
So if you copy the table into a new database, it does appear.
But if you then create the relationships, it disappears???

--
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.

"Texernie1" wrote in message
...

On Saturday, March 22, 2008 11:16 AM
khenders_98athotmaildotco wrote:

Allen, Thanks for your quick reply again.
Allen,

Thanks for your quick reply again.

From your reply, i tried a couple things. The drop down is missing the
first table from the table list. If I add a blank table (1 field, but no
records) named "C", I see my linkslist table. If I rename it to "Z", I no
longer see my linkslist table.

Does this make sense?

I at least have found a workaround, but the cause doesn't make sense.

Any ideas as to what is going on?

Keith

"Allen Browne" wrote:

On Saturday, March 22, 2008 11:22 AM
Allen Browne wrote:

No, I don't follow.
No, I don't follow.

If you rename the table, then Access won't be able to find it when it looks
for the old name. That's to be expected.

--
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.

"Texernie1" wrote in message
...

On Saturday, March 22, 2008 11:31 AM
khenders_98athotmaildotco wrote:

Allen, Let me see if I can explain it better.
Allen,

Let me see if I can explain it better.

Original Database Tables:
linklist
sitelist
userlist

Append query will not work with linklist.


Database mod1 tables:
c (only 1 field, and 0 records)
linklist
sitelist
userlist

append query does not see table c, but does see the other 3.


Database mod2 tables: (Renamed c to z)
linklist
sitelist
userlist
z

append query does not see table linklist, but does see the other 3.


Did this clarify what I see?

Thanks in advance.

Keith.


"Allen Browne" wrote:

On Saturday, March 22, 2008 3:25 PM
John W. Vinson wrote:

Append Query Problem
On Sat, 22 Mar 2008 08:31:01 -0700, Texernie1
wrote:


Yes... but it still makes no sense. You almost surely have some corrupt
systems tables. If you create a BRAND NEW database with nothing in it, and
then use File... Get External Data... Import to import all the tables (just
the tables for now), do you see them all? (Don't bother importing C or Z).

If the first table alphabetically in the list doesn't vanish this time, try
importing the Queries, Forms, Reports etc. and see if the problem comes back.
--

John W. Vinson [MVP]

On Saturday, March 22, 2008 4:45 PM
khenders_98athotmaildotco wrote:

John, Thanks for your quick reply.
John,

Thanks for your quick reply.

There are only 3 tables in the dabase. No Queries, reports, modules,
etc. It is a database that drives a website that I am developing.

I took the extra steps to export all 3 tables to a CSV. I took a brand
new database with nothing in it. I manually re-created the table structures.
I then imported the data from the three CSVs. I then recreated the
relationships.

When I tried to make an update query, the first table, alphabetically is
not in the drop down box to choose.

This is really baffling.

When I start a new query in design view, I see all three tables. I hit
close to add 0 tables to the query. I then change the query type to Append.
The first table alphabetically is missing from the "Append to Table Name"
field.

Regards,
Keith Henderson.

"John W. Vinson" wrote:

On Sunday, March 23, 2008 12:58 AM
John W. Vinson wrote:

Append Query Problem
What version of Access? I wonder if your installation of Access is corrupt! I
hate to suggest it, but perhaps it is worth uninstalling Access and
reinstalling it.
--

John W. Vinson [MVP]

On Sunday, March 23, 2008 10:00 AM
khenders_98athotmaildotco wrote:

John, I have Microsoft Office Access 2003 (11.8166.8202) SP3.
John,

I have Microsoft Office Access 2003 (11.8166.8202) SP3. It is part of
the MS Office Professional Edition 2003.

Regards,
Keith.

"John W. Vinson" wrote:

On Sunday, March 23, 2008 4:16 PM
khenders_98athotmaildotco wrote:

John, I have uninstalled Access, rebooted, and then re-installed Access.
John,

I have uninstalled Access, rebooted, and then re-installed Access. The
problem still exists. I did not uninstall the whole office suite. Nor did I
scour my registry for traces of Access after uninstall.

The mystery continues.

Regards,
Keith

"John W. Vinson" wrote:

On Sunday, March 23, 2008 11:24 PM
khenders_98athotmaildotco wrote:

John, You should have mail.
John,

You should have mail.

I deleted the actual passwords and coded passwords from the users
table, but otherwise the 3 tables are the ones I am using.

I decided to look at another, unrelated, database I have. It exhibited
the same behavior.

Could this be a bug in Access 2003?

Thanks in advance.

Regards,
Keith

"John W. Vinson" wrote:

On Monday, March 24, 2008 7:35 PM
John W. Vinson wrote:

Append Query Problem
On Fri, 21 Mar 2008 19:40:01 -0700, Texernie1
wrote:


Keith, I'm looking at the database you sent me and I have a couple of
questions.

Your table definitions have:

Userlist
UserID primary key
Username
other fields

Sitelist
SiteID primary key
UserID foreign key to Userlist.UserID
Site

Linklist
LinkID autonumber primary key
SiteID foreign key to Sitelist
Linktype
Link
Linktext

This is not a typical many to many linkage table, which would have no link
between Userlist and Sitelist, but would have the Linklist table containing
foreign keys to Users and Sites. It would not be appropriate to use an append
query to populate linklist - it defines which records are related to which
others, and that information does not (or should not) exist in either of the
parent tables.

So it seems that your use of the term "linklist" is not what we have been
interpreting! What IS the functional intention of the linklist table, and what
is the purpose of your desired append query?

To get back to the MAIN issue, when I create a Query based on Userlist, or on
sitelist, or joining those two queries, and select the Append query type from
the toolbar querytype tool, I am indeed offered all three table names as the
Append to target. It really appears that your Access installation is
compromised (even though you've removed and reinstalled). If you select
Help... About from the menu, what's on the first line? (On one of my machines
it shows Microsoft (R) Office Access 2003 11.6506.8022 SP2, you should see
something similar). If you have installed SP3, have you also installed the
hotfix? There was a bug in Service Pack 3 which causes combo boxes to
be shown blank even though they should contain data. A hotfix is available;
see http://support.microsoft.com/kb/945674. I don't know what other problems
the hotfix covers but it's perhaps worth a try!
--

John W. Vinson [MVP]

On Tuesday, March 25, 2008 12:46 AM
khenders_98athotmaildotco wrote:

Append Query Problem
John,

The database helps drive the pages following
http://www.yourcustomsitebuilder.com...rs/default.asp

Username: Test User (remove spaces)
Password: Test Password (remove space)

I know it's not wise to post usernames and passwords on boards. This is an
account setup for testing the website's functionality. Once fuctionality is
confirmed and thouroughly tested, the account will be deleted.

The UserList table controls the users to the site. Each user has only 1 logon.

The Sitelist table identifies which site(s) each user has.

The LinkList table identifies what links are associated with each site.

By the time the user has gotten to the upload page, to send me files, I
already know which foreign key in the LinkList table to put in. After a file
is uploaded to the site, I add a record to the linklist table so it shows up
on the web page.



After the re-install and hotpatch, my office is Microsoft Office Access 2003
(11.8204.8202) SP3. I had not installed the hotpatch (didn't even know there
was one available), but have now. I still see the same problem.

Hope this helps describe what the tables are for.

Regards,
Keith


"John W. Vinson" wrote:

On Wednesday, April 23, 2008 7:31 PM
khenders_98athotmaildotco wrote:

John, What you describe is exactly what I see.
John,

What you describe is exactly what I see. I am glad I am not the only one
that sees this.

Regards,
Keith


"jgfaughnan" wrote:

On Friday, April 25, 2008 9:01 PM
jgfaughnan wrote:

The alpha first table in an access 2003 database no longer appears in the append query table list
Hi Keith,

I've been experiencing a similar bug for a month or two. I suspect
some Microsoft update added it in.

Basically this is what I see.

Database contains 4 tables (for example), called:
A
B
C
D

When I try to append to a table the table name select dialog (title of
the dialog box is simply 'Append') will show me three tables:
B
C
D.

The "first" table (alpha sort) is always missing.

The work around is to create an empty table called that alpha sorts
ahead of my missing table. Then that will become the "missing" table.

The problem shows up with every one of my hundreds of Access
databases.

Very annoying.

I think we have to wait for Microsoft to fix this one.

john

tags: jfaughnan, jgfaughnan, bug, defect, Microsoft Access 2003, sort
order


On Mar 21, 9:40 pm, Texernie1
wrote:

On Thursday, June 12, 2008 12:36 PM
jas58 wrote:

Just want to add my name to the list of people who are having this problem...
Just want to add my name to the list of people who are having this problem...

-jeff



"Texernie1" wrote:

On Saturday, May 16, 2009 10:33 AM
DBL wrote:

Missing tables
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.

On Monday, September 21, 2009 10:38 AM
Eirwyn John wrote:

First table not appearing in append table dropdown
I am having exactly the same problem with the first table not appearing in append table dropdown when creating an append query. This seems to have come about in the last couple of weeks or so; I do not know what possible upgrades may have been applied. I am running

MS Office Acces 2003 (11.8166.8221) SP3

Has no-one resolved this issue to date ?



Posted as a reply to:

Missing tables

You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.

EggHeadCafe - Software Developer Portal of Choice
WCF Workflow Services Using External Data Exchange
http://www.eggheadcafe.com/tutorials...vices-usi.aspx

On Friday, January 15, 2010 1:08 PM
Noah Helenihi wrote:

Same Problem: 1st table alpha list does not appear in append qry
I'm having the same problem. The only working-solution I've found is to add a dummy table named, "AAA" to make sure that all REAL tables show up in Append Query drop-down. I'd like to kow if anyone comes up with a more stidfactory solution. Thanks.


Submitted via EggHeadCafe - Software Developer Portal of Choice
Resharper for Visual Studio .NET 2005 - Product Review
http://www.eggheadcafe.com/tutorials...sual-stud.aspx