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

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

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:

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

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

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.

"Texernie1" wrote in message
...
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.

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:

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

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.

"Texernie1" wrote in message
...
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.

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

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

"Texernie1" wrote in message
...
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:

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.

"Texernie1" wrote in message
...
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.

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:

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

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

"Texernie1" wrote in message
...
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:

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.

"Texernie1" wrote in message
...
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.

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

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?

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.

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 Sat, 22 Mar 2008 08:31:01 -0700, Texernie1
wrote:

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?

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 Sat, 22 Mar 2008 13:45:01 -0700, Texernie1
wrote:

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


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

John W. Vinson [MVP]