# MS Access Problem **URL:** \ **Category:** Factual Questions\ **Created:** [January 5, 2005, 11:09pm UTC](https://boards.straightdope.com/t/ms-access-problem/283295 "2005-01-05T23:09:58Z")\ **Posts on this page:** 16\ **Page:** 1
**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\ **Post date:** [January 5, 2005, 11:09pm UTC](https://boards.straightdope.com/t/ms-access-problem/283295/1 "2005-01-05T23:09:58Z")
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. Except! 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. Except! 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. So… 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. Except! 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). Now… 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? Thanks. ---
**Author:** ![ouryL](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ouryl/32/6067_2.png) [@ouryL](https://boards.straightdope.com/u/ouryL)\ **Post date:** [January 5, 2005, 11:12pm UTC](https://boards.straightdope.com/t/ms-access-problem/283295/2 "2005-01-05T23:12:30Z")
> [@Bill H.](#): > > 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. > > Except! > > 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. > > Except! > > 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. > > So… > > 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. > > Except! > > 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). > > Now… > > 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? > > Thanks. Can’t you export into excel, then import back into the Access? ---
**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\ **Post date:** [January 5, 2005, 11:25pm UTC](https://boards.straightdope.com/t/ms-access-problem/283295/3 "2005-01-05T23:25:40Z")
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. ---
**Author:** ![RealityChuck](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/realitychuck/32/195_2.png) [@RealityChuck](https://boards.straightdope.com/u/RealityChuck)\ **Post date:** [January 6, 2005, 1:00am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/4 "2005-01-06T01:00:28Z")
First, make a copy of the database to play with. Is the original autonumber field consecutive? That should be an easy fix. [ol] [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. ---
**Author:** ![Boyo\_Jim](https://avatars.discourse-cdn.com/v4/letter/b/87869e/32.png) [@Boyo\_Jim](https://boards.straightdope.com/u/Boyo_Jim)\ **Post date:** [January 6, 2005, 1:02am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/5 "2005-01-06T01:02:34Z")
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: > [@](#): > > Change the starting value of an AutoNumber field > For a new table that contains no records, you can change the starting value of an AutoNumber field whose NewValues property is set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number. > > If your original table contains property settings that prevent Null values in fields, you must temporarily change those properties. These settings include: > The Required field property set to Yes > The Indexed field property set to Yes (No Duplicates) > A field and/or record ValidationRule property that prevents Null values in fields > Create a temporary table with just one field: a Number field. Set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change. > In Datasheet view, enter a value in the Number field of the temporary table that is one (1) less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field. > Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change. > How? > > Create a query that contains the table whose records you want to append to another table. > How? > > In the Database window, click Queries under Objects, and then click New on the Database window toolbar. > In the New Query dialog box, click Design View, and then click OK. > In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with. > Double-click the name of each object you want to add to the query, and then click Close. > Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order. > To view the query’s results, click View on the toolbar. > In query Design view, click the arrow next to Query Type on the toolbar, and then click Append. The Append dialog box appears. > In the Table Name box, enter the name of the table you want to append records to. > Do one of the following: > If the table is in the currently open database, click Current Database. > > If the table is not in the currently open database, click Another Database and type the path of the database where the table is stored or click Browse to locate the database. You can also specify a path to a Microsoft FoxPro, Paradox, or dBASE database, or a connection string to an SQL database. > > Click OK. > Drag from the field list to the query design grid the fields you want to append and any fields you want to use for setting criteria. > If all the fields in both tables have the same names, you can just drag the asterisk (\*) to the query design grid. However, if you’re working in a database replica, you’ll need to add all the fields instead. > > If you have a field with an AutoNumber data type, do one of the following: > Add AutoNumber values automatically > > To have Microsoft Access add AutoNumber values automatically, don’t drag the AutoNumber field to the query design grid when you create the query. > > With this method, Access appends records and automatically inserts AutoNumber values. The first record appended has a value that is one larger than the largest entry that was ever entered in the AutoNumber field (even if the record that contained the largest AutoNumber value has been deleted). > > Use this method if the AutoNumber field in the table you’re appending to is a primary key, and the original table and the table you’re appending to contain duplicate AutoNumber values. > > Keep the AutoNumber values from the original table > > To keep the AutoNumber values from the original table, drag its AutoNumber field to the query design grid when you create the query. > > If the fields you’ve selected have the same name in both tables, Microsoft Access automatically fills the matching name in the Append To row. If the fields in the two tables don’t have the same name, in the Append To row, enter the names of the fields in the table you’re appending to. > In the Criteria cell for the fields that you have dragged to the grid, type the criteria on which additions will be made. > To preview the records that the query will append, click View on the toolbar. To return to query Design view, click View on the toolbar again. Make any changes you want in Design view. > Click Run on the toolbar to add the records. > > Delete the temporary table. > Delete the record added by the append query. > If you had to disable property settings in step 1, return them to their original settings. > When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value one (1) greater than the value you entered in the temporary table. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [January 6, 2005, 1:04am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/6 "2005-01-06T01:04:47Z")
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. ---
**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\ **Post date:** [January 6, 2005, 1:17am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/7 "2005-01-06T01:17:04Z")
**Mangetout** wrote > [@](#): > > 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. That’s true, isn’t it? Kinda obvious too, now that I think about it. > [@](#): > > Actually, your table updates can be done (and will be better) with a bunch of update queries, rather than an iterative code loop. I don’t understand. Tell me more, please. ---
**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\ **Post date:** [January 6, 2005, 1:37am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/8 "2005-01-06T01:37:33Z")
**Mangetout** wrote > [@](#): > > Are there any gaps in the existing autonumber sequence (i.e. where you’ve deleted records) - actually, I’m guessing there will be… Correct. There are gaps. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [January 6, 2005, 1:48am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/9 "2005-01-06T01:48:18Z")
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. ---
**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\ **Post date:** [January 6, 2005, 1:58am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/10 "2005-01-06T01:58:24Z")
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” ---
**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\ **Post date:** [January 6, 2005, 2:09am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/11 "2005-01-06T02:09:31Z")
Ah, that’s cool; I didn’t know you could do that. This is a big help. ---
**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\ **Post date:** [January 6, 2005, 3:26am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/12 "2005-01-06T03:26:17Z")
**Mangetout** , you rock. That did it. And easy too. And thanks to everyone else for the help. ---
**Author:** ![Civil\_Guy](https://avatars.discourse-cdn.com/v4/letter/c/d78d45/32.png) [@Civil\_Guy](https://boards.straightdope.com/u/Civil_Guy)\ **Post date:** [January 6, 2005, 3:48am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/13 "2005-01-06T03:48:54Z")
…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. ---
**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\ **Post date:** [January 6, 2005, 8:51am UTC](https://boards.straightdope.com/t/ms-access-problem/283295/14 "2005-01-06T08:51:29Z")
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. ---
**Author:** ![Canadjun](https://avatars.discourse-cdn.com/v4/letter/c/76d3ee/32.png) [@Canadjun](https://boards.straightdope.com/u/Canadjun)\ **Post date:** [January 6, 2005, 1:53pm UTC](https://boards.straightdope.com/t/ms-access-problem/283295/15 "2005-01-06T13:53:11Z")
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. ---
**Author:** ![Bill\_H](https://avatars.discourse-cdn.com/v4/letter/b/a5b964/32.png) [@Bill\_H](https://boards.straightdope.com/u/Bill_H)\ **Post date:** [January 6, 2005, 6:30pm UTC](https://boards.straightdope.com/t/ms-access-problem/283295/16 "2005-01-06T18:30:54Z")
**Mangetout** wrote > [@](#): > > Is it all fixed now? Yes it is. Thanks for the excellent suggestion. **Civil Guy** wrote > [@](#): > > …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. 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 > [@](#): > > 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. Thanks. As it turns out, I’ve got it fixed now, but I appreciate the advice.