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.
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
:
Previous Posts In This Thread:
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
Re: 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
Reply to group, rather than allenbrowne at mvps dot org.
Re: 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.
:
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
Reply to group, rather than allenbrowne at mvps dot org.
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
:
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
Reply to group, rather than allenbrowne at mvps dot org.
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.
:
Re: Append Query Problem
On Sat, 22 Mar 2008 08:31:01 -0700, Texernie1
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]
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.
:
Re: 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]
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, 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, 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
:
Re: Append Query Problem
On Fri, 21 Mar 2008 19:40:01 -0700, Texernie1
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]
Re: Append Query Problem
John,
The database helps drive the pages following
http://www.yourcustomsitebuilder.com/customers/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, 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
:
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
(e-mail address removed)
tags: jfaughnan, jgfaughnan, bug, defect, Microsoft Access 2003, sort
order
On Mar 21, 9:40 pm, Texernie1 <
[email protected]>
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
:
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.
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/tutorial...a-6dafb17b6d74/wcf-workflow-services-usi.aspx
Submitted via EggHeadCafe - Software Developer Portal of Choice
Advanced .NET Remoting [aPress]
http://www.eggheadcafe.com/tutorial...dfc-8df46b79df2d/advanced-net-remoting-a.aspx