MS Access Problem

I wrote a program maybe 10 or 15 years ago in Access for my brothers electronic store. Shortly thereafter, MS made changes to Access so that it was not backwards compatible, and as a result, my brother’s used that same program this whole time, tied to Access 2.0, which meant tied to Windows 95, which meant tied to some archaic computer. Over Christmas, I visited him and updated the program to run on the latest Access.


There’s a lot of pre-existing data, and I need that to be usable.

There are a number of tables. Most have a field that is an “autonumber”, which is used as a record identifier, and is referenced by other tables. Standard concept.

When I tried to import the existing data, I couldn’t “force” autonumber entries to have a specific value; Access wanted to give them it’s own value. So, I changed each of the autonumbers to a long, imported the data, and everything works great.


Now when I go to add a record, there is no longer an autonumber for the id, so initially it’s null. Ok, I’ll just change the field type back from long to autonumber. Nope; once a table has data, a field can’t be changed to autonumber. You can create a new field which is autonumber, and all the existing records will be autonumbered. But then the new values won’t mesh with their “pointers” in other tables.


The only solution I see at this point is to create new autonumber records in each table, and programatically change all of the other pointers appropriately. That’s a bunch of work, but I can do it.


There doesn’t seem to be any sort of “interpreter” mode in Access, i.e. where I can type in code and execute it. As near as I can tell, code only runs from forms. There is an intermediate mode, but it only can print out or set values. (I don’t know Access that well; I wrote this thing long ago).


If the data were in MySQL or some other database where I could access the data from tools other than Access, I could do this. So, I’m considering trying to move the data over to MySQL and using Access strictly as the frontend for this. But that seems pretty ugly.

Any ideas?


Can’t you export into excel, then import back into the Access?

I can export individual tables, and reimport them, yes. But the problem is when I import into a autonumber field, access won’t accept it. And if I change the autonumber to long, import, and attempt to change it back to autonumber, it won’t let me.

First, make a copy of the database to play with.

Is the original autonumber field consecutive? That should be an easy fix.

[li]Sort by old autonumber field[/li][li]Create a new autonumber field. It should fill consecutively. Give it a temporary name.[/li][li]Delete the old autonumber field.[/li][li]Rename the new autonumber field to match the old name.[/li][/ol]
References to the table in queries should now point to the new autonumber.

If the autonumber is not consecutive, then it’s a big problem. You may be reduced to numbering the entries manually each time.

I found this in Access’s help text. I’m not an expert, but there might be enough clues in here to do what you’re looking for:

Are there any gaps in the existing autonumber sequence (i.e. where you’ve deleted records) - actually, I’m guessing there will be, as an autonumber is even used and discarded if you start populating a new record, then exit without saving. If it were not so, it might have been possible to sort the table on the existing (not)autonumber field, then add the new autonumber field and have the values coincidentally the same.

I think your programmatic approach is best - add the new field (keeping the old one for the moment), then updating the key fields in all the other tables to match; once you’ve done that, it should be possible to delete the duff pseudo-autonumber(long) field and rename the new autonumber field so that you won’t have to recreate all your relationships.

There’s no interactive code intepreter as such, but if you want to run a bit of VBA, all you have to do is create a blank form, drop a button on it and shove your code in the click event handler. Actually, your table updates can be done (and will be better) with a bunch of update queries, rather than an iterative code loop.

Whatever you do, make copious backups first.

Mangetout wrote

That’s true, isn’t it? Kinda obvious too, now that I think about it.

I don’t understand. Tell me more, please.

Mangetout wrote

Correct. There are gaps.

I need to go to bed now, but I’ll try to post back tomorrow with more details on update queries - It will help to know what version of Access you’re using now and whether/to what extent you’ve used the query designer.

I’m using Access in Office 2003. If you can even give me some key words to google, that’ll be great. I’ll uh start off with “update queries”

Ah, that’s cool; I didn’t know you could do that. This is a big help.

Mangetout, you rock. That did it. And easy too.

And thanks to everyone else for the help.

…and I don’t know if you ran across this, yet - took my a long while to figure this one out - but one way to create a query is to code it up in SQL. That’s one of the, uh, windows that’s available through the query designer in Access. Switch back to the default query design window and Access will, if it can, translate the SQL into the design layout it uses by default.

(There’s actually a few SQL commands that Access will accept that can’t be entered outside of the SQL window.)

That is, this might be just as easy for you if you feel comfortable using MySQL. Myself, I find SQL just a little arcane, though not too bad.

Is it all fixed now?

SQL is indeed a really powerful tool and it took me quite a while to grasp some of the root concepts (silly really, because it is in fact designed to be a very simple language) - the one that took forever to dawn is that, when you run a SELECT query, you’re not looking at an extract of the data, you’re looking at the data - for some reason, I had it in my mind that select queries were scooping up a bunch of data and putting it into a separate, temporary bucket, then showing it to you, when in fact, the bucket has a glass bottom and you’re actually looking at part of the real data through it.

I’m not at a computer with Access at the moment (I’m at home and Access is at work), but as I recall, you can use an append query to get the old values into the new autonumber field. Instead of trying to turn your long int field into an autonumber field (which as you’ve seen doesn’t work), create an empty table with that includes an appropriate autonumber field and then use an append query to copy the data from the old table into the new.

Mangetout wrote

Yes it is. Thanks for the excellent suggestion.

Civil Guy wrote

Cool; that’s very valuable to know. Actually, the sort of access to the data that I had in mind with MySQL wasn’t using SQL commands directly, but rather accessing it through Perl or PHP or c or some other language that I’m more familiar with. Visual Basic isn’t that tough, but it’s not really a native language for me, and I find the help in Access extremely limited.
dmartin wrote

Thanks. As it turns out, I’ve got it fixed now, but I appreciate the advice.