Can't append to first table in database

[ramprat]

Hi All,

I'm using Access 2003 and I've noticed that if I try to create an append query I am not able to append data to the first table (alphabetically) in my list of tables. I can change the name of the table to move it further down the alphabetical list of tables and then I am able to append to it but if it's the first table in the list I cannot. Has anyone experienced this before? If so do you have a workaround? I could create a dummy table called "A" and then all others would fall below it but this seems like a cheesy solution. Is there anything else I can do?

Thanks as always.

Ramprat

[Stewart Ross]

Sorry Ramprat - we cannot guess at what is going on here without you telling us something in more detail about what you are trying to do. You will need to post the VBA code and SQL you are trying to run, and some details of the tables involved.

I know of no issues with Access which relate to alphabetical ordering of tables rendering some non-updatable - there are plenty of other issues which can interfere, however, as you can look up in this insights article linked here.

I would reckon that the problem is more likely to be something in your code that relates to how your SQL string is put together for your first table update (assuming that you are setting up the SQL in code - as you are not telling us I'm just guessing here).

-Stewart

[ramprat]

Hi Stewart,

Thanks for your response. Actually I'm not running any code at all. I simply create a new query in design view and change it from a select query to an append query and when it asks me for the table to append to I can't see the first table in my database but all the others show up as ones that I am able to append to. If I change the name of the table to move it alphabetically down the list then when I try the above steps again I am able to see that table in the list but the 2nd table (alphabetically) in my database is now not able to be appended to because it has now become the first table alphabetically because I changed the name of the first table.

To replicate this create two tables A and B. Try to create an append query that appends to table A and you won't be able to. If you rename table A to C then you will now be able to append to it but not to table B. I don't know if this is a quirk particular to Access 2003.

Ramprat.

[Stewart Ross]

Hi Ramprat. I am unable to replicate the problem you are experiencing. A2003 works perfectly in listing tables to append to in the installations I use. It does not miss out the first table in the append list, which can be selected and used like any other.

I have tried to replicate your problem using a relatively complex DB with more than 70 tables, and a new DB with just two dummy tables A and B created as you suggested.

I have used A2003 since its release and have not come across this one at all.

-Stewart

[ramprat]

Thanks anyway Stewart. Maybe I'll have to reinstall Access to fix this.

[stevening]

Same with me. I also am using Access 2003 and the first table, alphabetically, does not appear when I select an append query type.
I added a dummy table called 'aaaa' with zip in it and now I see the table i want.

I even went so far as to reboot and do a compact and repair. Still, the first one is not there. In this case, 'aaaa' (the dummy table I added so I could see the one I wanted).

And yes, it is a cheesy solution.

[ramprat]

Glad to see I'm not halucinating. Yes it is cheesy but what else can you do.

Good luck.

[Stewart Ross]

The Access expert Allen Browne mentions the append problem as an issue relating to the introduction of Office Service Pack 3 - see this link for further details: http://allenbrowne.com/bug-Access2003SP3.html. The Microsoft hotfix released in response does not appear to address this specific issue.

-Stewart

[jcdutchess]

This worked, thanks! I thought I was losing my marbles.






@stevening