INSERT INTO linked table with AutoNumber resets counter

In Access 2003 SP2, when a record in a linked table that has an
incrementing AutoNumber primary key field has been deleted and I
retrieve the record from an earlier backup using:
   INSERT INTO tblTable
   SELECT * FROM tblTableBackup
   WHERE ID = IDBackup;
the next record to be manually inserted in the table gets assigned a
primary key value of IDBackup + 1 rather than the expected
IDLastAllocated + 1.

I can reproduce this problem in a test database as well as the working
system.

Is this a known problem?  I've not been able find anything on the web.

0
David
11/19/2007 8:44:47 AM
access 16762 articles. 3 followers. Follow

4 Replies
654 Views

Similar Articles

[PageSpeed] 58

Hi David,

I cannot reproduce the situation you are reporting. I carried out the 
following test, using the Northwind sample database:

1.) Copied the Orders table (structure + data) to two new tables:
     tblTable  and  tblTableBackup

2.) Opened the tblTable table and deleted the first (7) records. This would 
be OrderIDs 10248 though 10254.

Then I used the following INSERT query, where I substituted your "ID" with 
"OrderID", and "IDBackup" with an actual value. For example:

   INSERT INTO tblTable
   SELECT * FROM tblTableBackup
   WHERE ORDERID = 10249;

After doing this insert, the next record I tried to manually enter was 11078.

This leads me to two questions:
1.) Are you absolutely sure you have the latest service pack installed for 
the JET database engine? Use the instructions shown in this KB article as a 
guide:

    How to keep a Jet 4.0 database in top working condition
    http://support.microsoft.com/?id=303528

Follow the link that reads "Verify that the latest Microsoft Jet service 
pack is installed". Also, while you are at it, it would be a good idea to 
follow the links for verifying that the latest service packs are installed 
for your version of Windows and Office.

2.) Why delete the records in the first place? Why not just add a numeric 
field, named something like ActiveRecord, set the default value to -1 (true), 
and then when you want to inactivate a record, change this value to 0 
(false). You can even have a command button on a form with the caption that 
reads "&Delete Record", along with a confirmation message (ie. IF intResponse 
= vbYes Then....) so that the user *thinks* they are deleting a record, but 
they're actually just flipping a bit. I do this in my own applications, and I 
record the NTUserID and Date (=Now), so that I know exactly whodunit.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"David L" wrote:

> In Access 2003 SP2, when a record in a linked table that has an
> incrementing AutoNumber primary key field has been deleted and I
> retrieve the record from an earlier backup using:
>    INSERT INTO tblTable
>    SELECT * FROM tblTableBackup
>    WHERE ID = IDBackup;
> the next record to be manually inserted in the table gets assigned a
> primary key value of IDBackup + 1 rather than the expected
> IDLastAllocated + 1.
> 
> I can reproduce this problem in a test database as well as the working
> system.
> 
> Is this a known problem?  I've not been able find anything on the web.
0
Utf
11/19/2007 11:12:00 AM
Yes, it is a known problem, David.

See the 2nd cause in this article:
    Fixing AutoNumbers when Access assigns negatives or duplicates
at:
    http://allenbrowne.com/ser-40.html

Compacting the database fixes the bad autonumber seed in some versions of 
Access. Of if you want to fix it programmatically, the link above contains 
the code to do that.

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

"David L" <hamlet1@acslink.net.au> wrote in message
news:55f3077f-e9f3-484e-a13a-73aed705f5ae@e23g2000prf.googlegroups.com...
> In Access 2003 SP2, when a record in a linked table that has an
> incrementing AutoNumber primary key field has been deleted and I
> retrieve the record from an earlier backup using:
>   INSERT INTO tblTable
>   SELECT * FROM tblTableBackup
>   WHERE ID = IDBackup;
> the next record to be manually inserted in the table gets assigned a
> primary key value of IDBackup + 1 rather than the expected
> IDLastAllocated + 1.
>
> I can reproduce this problem in a test database as well as the working
> system.
>
> Is this a known problem?  I've not been able find anything on the web. 

0
Allen
11/19/2007 1:14:50 PM
On Nov 19, 10:14 pm, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
> Yes, it is a known problem, David.
>
> See the 2nd cause in this article:
>     Fixing AutoNumbers when Access assigns negatives or duplicates
> at:
>    http://allenbrowne.com/ser-40.html
>
> Compacting the database fixes the bad autonumber seed in some versions of
> Access. Of if you want to fix it programmatically, the link above contains
> the code to do that.
>
> --
> 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.
>
> "David L" <haml...@acslink.net.au> wrote in message
>
> news:55f3077f-e9f3-484e-a13a-73aed705f5ae@e23g2000prf.googlegroups.com...
>
> > In Access 2003 SP2, when a record in a linked table that has an
> > incrementing AutoNumber primary key field has been deleted and I
> > retrieve the record from an earlier backup using:
> >   INSERT INTO tblTable
> >   SELECT * FROM tblTableBackup
> >   WHERE ID = IDBackup;
> > the next record to be manually inserted in the table gets assigned a
> > primary key value of IDBackup + 1 rather than the expected
> > IDLastAllocated + 1.
>
> > I can reproduce this problem in a test database as well as the working
> > system.
>
> > Is this a known problem?  I've not been able find anything on the web.

Thanks Allen for the putting me right on this problem.
0
David
11/20/2007 12:01:55 AM
On Nov 20, 9:01 am, David L <haml...@acslink.net.au> wrote:

Just for posterity the solution I used for this problem used the
Resolution in the MS KB Article (http://support.microsoft.com/?
id=884185), referred to in Allen's web page on this problem (http://
allenbrowne.com/ser-40.html), that recommends using the 'Another
Database' option in the Query Design screen.

As I'm creating the SQL in VBA and using DoCmd.RunSQL to execute it,
this equates to adding the IN clause into my previous example:
   INSERT INTO tblTable IN '<full pathname to back-end mdb>'
   SELECT * FROM tblTableBackup
   WHERE ID = IDBackup;
0
David
11/20/2007 12:52:58 AM
Reply:

Similar Artilces:

Pivot Table: Misordered date Grouping
I'm having a problem where the grouping is coming out wrong. I have dates ranging from October 2003 to June 2004. When I group by month, it gives me a January - November groupings vs. a October - June groupings. In other words, the October and November months should be in the first two columns given their dates come first. Thanks, Mark G. Hi, Can you post a piece of your data/spreadsheet? I can't get it to do what you describe. jeff >-----Original Message----- >I'm having a problem where the grouping is coming out >wrong. I have dates ranging from October 2...

linked cells causing pop-up
I have linked cells from one workbook to another using this formula which works great. The only problem is everytime I open the workboo that has this link in it, a message pops up asking me to link the info then when I click yes I have to choose the appropriate file in th network. Is there a way to make it automatically choose yes for me an then find the "SEPTEMBER04" file itself by using code or something? ='S:\[SEPTEMBER04.xls]INVENTORY'!A -- Message posted from http://www.ExcelForum.com ...

Sotock status value recreation through IV30300 table?
Hi, I am trying to find the difference between my goods received report and my stock status report. I have managed to recreate the goods received report through a view off the IV10200 table. These values agree to the Goods received report's values (But this does not help). However if I try and recreate the stock status report off the IV30300 table my values do not agree. I realise one issue is the transfers in the report so I am trying to use a view like below to rectify this problem: Create view ERP_IV30300Breakdown as select TRXSORCE,DOCTYPE,DOCNUMBR,DOCDATE,HSTMODUL,CUSTNMBR,I...

Custom Table Styles: Formatting Last Row
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I have been trying to develop my own custom table styles in Word. I want to create at Table Style with a 1.5 line at the header row, and a 1.5 line below the last row. Creating a style for the 'header row' works just fine but I cannot seem to create a 1.5 line below the last row. Does anyone have a clue what I am doing wrong? Also, aligning the header row in middle (vertically) is not working. <br> Thanks a lot in advance! I suspect that what "you" are doing wrong is "trying to u...

linking worksheet1 to worksheet1
Hello: I need to link two worksheets in seperate files together. The problem is the tabname on the first worksheet changes everyday. Is there a keyword I can use to identify the 1st worksheet regardless of its "name"? Thanks. Bed One way: Make the linked-to cells a named range (select the cell(s) and enter the name in the Name Box at the left side of the formula bar). Then instead of =SUM('[Workbook1.xls]Sheet 1'!A1:J10) use =SUM(Workbook1.xls!MyNamedRange) In article <95163F33-E619-47AD-BF19-897C2C2AB138@microsoft.com>, Bed <Bed@discussions.mi...

Excel Links question
Hello Everyone, We a have Excel 2000 spreadsheet that at one time was linked to another workbook. Excel asks you if you wish to update your data with the linked data. My question is how do I "unlink" the sheet. The current values in the spreadsheet are what we want. The document was passed on to me for help. I tried edit...links... but it just tries to get me to pick a different file. Anyways we just want to unlink it. I also tried tools...options...and chose to not autoupdate links and gives me a file not found error and upon cancel, the spreadsheet opens. Can any guru's...

import table
Hi, I got a message " Record is deleted" when I was trying to import a table from different database to my database. I have imported tables many times in the past, but I have a problem at this time. Please help. Thank you Chi It's possible that the table in the other database is corrupted. Maybe do a compact and repair on it. Or you could link to that table then use queries to find the problem record. After that you could run an append query and exclude the "deleted" record. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager...

get SQL Server text data type into Excel pivot table
Is it possible to get the full text of SQL Server TEXT data types into Excel pivot tables? SQL Server 7 and Excel 2002 SP3 I have a table that has "text" data type column. I have an ODBC connection to the database. I go to Excel's PivotTable Wizard and select the ODBC data source, and then the table and columns, but I only get a max of 253 characters in my pivot table for the text type column (I know it's being truncated). I've found some VB code to get CLOB data from various DB types but I still don't know if it's possible to get that into my pivot ta...

HELP with link to specific page in a pdf
how on earth in 2003 or 2007 do I create a hyperlink from my Word document to a specific page in a PDF? I have it linked to the PDF but can't get it to a specific page. I looked on line and from what I can see it says to add #page= and then the page number. I'm not sure where in the link to add it but where ever I have put it, it does nothing. Thank you. Using #page= only works for sure if your pdf is on a webserver. That is, if your link starts with http:// or https:// . In case of file:// links (local ones) it depends on a number of aspects which you most like...

link errors-Phantom ones
I inherited a work book shows a Link of another worksheet in the EDIT LINK, but I can not find any reference to a formula in any of the sheets or "Names". I try to delete it, it comes back as still a valid LINK. Where it could be hidden/stored that I am not able to find it? How can I chase it down? Try this free ad-in courtesy of Bill Manville MVP, it's really excellent, I'm sure it will help, don't forget to thank him if it does the trick for you! http://www.bmsltd.ie/MVP/Default.htm Regards, Alan. "JayRama" <JayRama@discussions.microsoft.com> wrot...

Use CDHtmlDialog in statically linked MFC DLL
Hi, I use a statically linked MFC exe load a statically linked MFC DLL, an careate a CDHtmlDialog in that DLL, I already add below code to solved original when create CDHtmlDialo will crash problem successfully, AFX_MANAGE_STATE(AfxGetStaticModuleState()); ::OleInitialize(NULL); AfxEnableControlContainer(); but now when I use it's member function, ex:GetDHtmlDocument it'll crash, I already add AFX_MANAGE_STATE(AfxGetStaticModuleState()); to the DLL' each function still have same problem what else should I add? Thanks! - rocktilldi -------------------------------------------...

Pivot Table Data #3
I've just discovered pivot tables and WOW! A **totally** beginner question: It appears that changes made to the source data will reflect in a pivot table, after refreshing. But, vice-versa, it appears that changes made in a pivot table cannot be reflected in the source data. Am I correct? Many thanks, Karen That's correct -- if you change a heading in the pivot table, it doesn't affect the source data. There's a sample file on my web site that uses programming to change source data, after a change in the pivot table. Make a backup copy of the data before experimentin...

Linked Table Dilema
Hi, Access 2000 & Excel 2000 I have 6 linked dbf tables to MS Access on a server which are automatically updated each day Queries are written in Access and Excel is used to extract data depending on the users selection, this works fine when using the raw dbf files, however there are occasions when the user has to do a what- if scenario and the dbf files are copied to the users local C:\My Documents folder where they can make changes to the dbf file using my Excel program as the files on the server have to remain unchanged. When running from the local drive I have to manually copy the m...

URL Links do not work
It seems that no URL links work. I get no error - in fact nothing happens at all. I can copy the link into the browser and it works fine. I am using WLM Desktop Beta on Vista with all updates. I have reset defaults for IE (8). How you fix it depends on what version of Windows OS you are using. Give us more details about your system. "gjs92" wrote in message=20 news:56782613-3581-4F71-99B7-50CD1B6930EF@microsoft.com... It seems that no URL links work. I get no error - in fact nothing = happens=20 at all. I can copy the link into the browser and it works fine. ...

Pivot table toolbar #2
User wants to open Excel-file containing Pivot table in IE. The problem is that the Pivot table toolbar opens also and the user doesn't want others to be confused by it. The versions are Excel 2003 and IE 5.5 or IE 6.0. For me it works fine if I have saved the file toolbar hidden but for the user that doesn't work. Why is it working for me, but not for her? ...

pivot table from different worksheets
hi to all, can i do a pivot table out of different worksheets ? currently i have a daily database (same structure, different values). i place them on file each on a different worksheet. how do i go about including all the worksheets on the same pivot table? sev You could create a Pivot Table from multiple consolidation ranges: 1. Choose Data>PivotTable and PivotChart Report 2. Select Multiple consolidation ranges, click Next 3. Select one of the page options, click Next 4. Select each range, and click Add, click Next 5. Select a location for the PivotTable, click Finish However, y...

Linked activities for users??
Is there anyway to see all the linked activities (sent/received email, etc) for a User in CRM? For contacts, they have the activities tab. For Users, I don't see anything. Do you have to go search the entire Activities list? In Workplace you can display Activities. The default views do not include a 'user' (Owner), but using the method below you can modify the 'All Activities' view to include the Owner ans then sort by Owner to get all their activities displayed. Open a web browser on the crm server and copy the required link into the browser - replace crmserver w...

Outlook locks up when inserting file
I have user w/WINXP Office 2002. Whenever he tries to insert a file in an Outlook message, the application locks up and you must end task. It is somewhat intermittent (happening about 90% of the time). Sometimes it locks up when clicking on File\Insert (you don't even get the dialog box). Sometimes you get to "Insert File" dialog box before locking up. Most of the time it happens at this point. Sometimes when the "Insert File" dialog box is open and then you click on the "look in" drop down menu, the text in that field starts flashing and then lo...

How to access table values of one third party from other third par
Hi I have a senario in which i have to access a table value of one third party product in other third party product script.I have written a trigger (TRIGGER_FOCUS_FILL) on a scroll window (TRIGGER_BEFORE_ORIGINAL).The table is linked to scroll window. When i have written third party code to access the value of a field from table its returning zero always. How to solve this.How to get exact vale which is used to fill scroll window. Any help will be appreciated. Thanks Pradeep Please have a look at the Pushing the Limits with Dexterity conference materials created by myself and Mark Ro...

insert objects in .xlsx fails with message "cannot insert object"
When inserting objects into an excel .xlsx spreadsheet I sometime receive an error message reading "cannot insert object" with no other alternaive than to click OK. I have tried inserting .docx and .xlsx files and in both cases I get the error, there appears no consistency. Why am I getting this error and how can it be avoided? ...

Insert logo into header & footer
Hi all member in MPEN :) I'm a newbie with Excel, I want to creat a letter form for my company with logo and address in header & footer but I can't do it. Plz give me some advice and step by step guide it. Thanks Might depend on your version of Excel. I Excel 2003: File | Page Setup... | Header/Footer tab | Custom Header... | Header | position the cursor in one of the sections and use the Insert Picture icon and perhaps the Resize Picture icon. Regards Trevor "day-du" <day-du@hcm.vnn.vn> wrote in message news:3005942a.0411260912.12df3a71@posting.google.com...

INserting Records into Table
I am in need of some help. I am very new at SQL so please disregard my ignorance. I have a table where I have a field named “persNo” is set up as the primary key. This is working well as I don’t want duplicate records. The problem is what I am trying to do is update the table with new records. The file I have contains both New records( not in the table) and “Old” records that are already in the table. What I would like to do is have the new Records load and where there is a primary key violation (old records) those records are just ignored and not loaded. Here is w...

Pivot table manual AUTOSORT
My worksheet contains a pivot table field ASOF MONTH dropdown list to get data of monthly data 200101 ..... 200805 200806 then i press REFRESH of pivot table to retrieve newest data (i.e 200807) but the pivot table field still does not show the newest data in dropdown list (ie. asof month 200807) UNTIL i press the dropdown list 'fields setting' -> 'advanced' ->'Autosort' -> 'ascending' or 'descending'.... instead its original setting 'manual' i want to know why options 'manual' of AUTOSORT cannot refresh my ASOF MONTH dropdo...

Finding/deleting old links?
Hi, I've got a workbook with about 5 links somewhere on one of th sheets... im not sure where. When I go to Edit/Links I can see th links and what they are linking too (all old stuff that we do not us anymore). I just want to delete these links. Is there a way to fin out where the links are in the workbook and go there to delete them -- Message posted from http://www.ExcelForum.com You can select all sheets and search for part of the path, "C:\Excel\". If that doesn't find them, look in your Named Ranges. Those can refe to other files as well. Macro buttons and oth...

Table-auto fit to contents?
HI, I see no auto fit to contents command in the table menu in Publisher '02. Is there a way to do this? Thanks Catt On Sat, 22 Apr 2006 18:34:01 +0100, Catt wrote (in article <D0C21016-ABAC-49A3-AAFC-BB3054161DAE@microsoft.com>): > HI, I see no auto fit to contents command in the table menu in Publisher '02. > Is there a way to do this? > Thanks > Catt Nope. ...