Hi Molly,
No easy way to accomplish what you are trying to do. Here is what would be
needed (either via VBA code or manually):
- Copy table structure only as new table
- Append data from original table to new table
- Rename new table as original table
Here's a way to accomplish the above (sure there is a cleaner way of doing
it):
I have a table named "Table1" with an Autonumber field
- Copy table structure only as new table
+ Create a Make Table query that has a criteria that you no for
sure does not match
SELECT Table1.* INTO Table2 FROM Table1 WHERE
(((Table1.test)="does not exist"));
- Append data from original table to new table
+ Create an Append query that appends the data to new table
excluding the Autonumber field
INSERT INTO Table2 ( test ) SELECT Table1.test FROM Table1;
- Rename new table as original table
+ Create a function in the database that renames the new Table to
the old table
CurrentDb.TableDefs("Table1").Name = "OriginalTable1"
CurrentDb.TableDefs("Table2").Name = "Table1"
Eric
--------------------
| Content-Class: urn:content-classes:message
| From: "molly" <
[email protected]>
| Sender: "molly" <
[email protected]>
| References: <
[email protected]>
<
[email protected]>
| Subject: RE: Record num?
| Date: Mon, 1 Mar 2004 14:02:10 -0800
| Lines: 108
| Message-ID: <
[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcP/2Nh5IOndSx8jS1WElYZjAtOwJA==
| Newsgroups: microsoft.public.access.tablesdbdesign
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.access.tablesdbdesign:76535
| NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
|
| Thanks Eric for reply,
| But my question is can we have the other way to handle
| this problem in Access?
| Regards,
| Molly
|
| >-----Original Message-----
| >Hi Molly,
| >
| >Straight out of the box (without creating your own custom
| counter)
| >Microsoft Access will NOT do that. Once you use a number
| (autonumber) it
| >cannot be used again. Of course you can reset the
| autonumber. Here's an
| >example to explain how the autonumber would work:
| >
| >(note: see the Microsoft Access help topic "Autonumber"
| for detail
| >information)
| >
| >RecordID Last Name
| >--------------- --------------
| >1 a
| >2 b
| >3 c
| >4 d
| >5 e
| >6 f
| >
| >Let's say you delete the records 3 and 4 now your table
| looks like the
| >following. RecordID 3 and 4 can now longer exists in the
| table. When you
| >enter a new record it will have RecordID = 7
| >
| >RecordID Last Name
| >(autonumber)
| >--------------- --------------
| >1 a
| >2 b
| >5 e
| >6 f
| >
| >I hope this helps! If you have additional questions on
| this topic, please
| >respond back to this posting.
| >
| >
| >Regards,
| >
| >Eric Butts
| >Microsoft Access Support
| >
| >"Microsoft Security Announcement: Have you installed the
| patch for
| >Microsoft Security Bulletin MS03-026? If not Microsoft
| strongly advises
| >you to review the information at the following link
| regarding Microsoft
| >Security Bulletin MS03-026
| ><
http://www.microsoft.com/security/security_bulletins/ms03
| -026.asp> and/or
| >to visit Windows Update at
| <
http://windowsupdate.microsoft.com/> to install
| >the patch. Running the SCAN program from the Windows
| Update site will help
| >to insure you are current with all security patches, not
| just MS03-026."
| >
| >
| >
| >
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "molly" <
[email protected]>
| >| Sender: "molly" <
[email protected]>
| >| Subject: Record num?
| >| Date: Mon, 1 Mar 2004 10:58:40 -0800
| >| Lines: 8
| >| Message-ID: <
[email protected]>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| >| Thread-Index: AcP/vzXzTwhxp/HhSja9cEA486c1KA==
| >| Newsgroups: microsoft.public.access.tablesdbdesign
| >| Path: cpmsftngxa06.phx.gbl
| >| Xref: cpmsftngxa06.phx.gbl
| microsoft.public.access.tablesdbdesign:76523
| >| NNTP-Posting-Host: tk2msftngxa12.phx.gbl 10.40.1.164
| >| X-Tomcat-NG: microsoft.public.access.tablesdbdesign
| >|
| >| Hi ,
| >| I am new guy to develope Access, please help.Let say, I
| >| have 5 records, now I deleted record number 3.
| >| Does in Access it will auto convert record #4 become
| >| record #3 and record #5 become record #4? How can I do
| it?
| >| Please help.
| >| Regards,
| >| Molly.
| >|
| >
| >.
| >
|
