?
Solved

Access 2003 -- Insert AutoNumbers -- "Set Identity_Insert On"?

Posted on 2007-10-03
8
Medium Priority
?
478 Views
Last Modified: 2008-01-09
I need to copy (programatically) some tables from one Access 2003 DB to another.  The DB's are actually in MDE files so that any rogue customers have a bit more trouble stealing my code, and I'm finding that limits me in some ways.

My problem w/ the data copy is that many of the tables use AutoNumbers. Those, of course are referenced by other tables.

How do I copy the AutoNumber from the populated table into the empty one?

In MS SQL Server, I'd use "Set Identity_Insert On".  Is there an Access 2003 equivalent?

Thanks!
0
Comment
Question by:Daniel Wilson
8 Comments
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20005073
You may establish a identity increment counter...
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 20005141
I'm not finding anything on "identity increment counter" in Google that appears to answer the question.  Can you explain  a little more?
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20005176
<< Can you explain  a little more?>> You may take a look t the following link...

http://msdn2.microsoft.com/en-us/library/aa933196(SQL.80).aspx

Hope this helps...
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 32

Author Comment

by:Daniel Wilson
ID: 20005243
OK ... that's all about the Identity field property in SQL Server.  I'm quite familiar with that in SQL Server 7, 2000, and 2005.  One of the things that can be done there is telling SQL Server to allow the Identity field to be set by an Insert statement.

This is useful when copying a large block of data from one set of tables to another.

But on the current project, I'm in Access 2003.  Does Access 2003 have something similar to "Identity Insert ON"?  In other words, how can I run an Insert statement that actually sets the values in the AutoNumber field?

Thanks.
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 20005266
My access is a little rusty so I am not sure it is possible to do that in Access...
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 20005309
You can write data to an Autonumber field just as you would any field. Assuming that tblCustomers.CustomerID is an AutoNumber field, you can do this:

INSERT INTO tblCustomers(CustomerID, LName, FName) VALUES(123,'Smith','Bob')

Of course, if this is a PK or has a Unique index set on it, you cannot duplicate those values.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 20005326
Create a syandard append query to copy the data, using the query grid.
You can specify the other database as the target and it will copy the autionumber values.
0
 
LVL 32

Author Comment

by:Daniel Wilson
ID: 20013183
>>You can write data to an Autonumber field just as you would any field.

Why didn't I think to give it a try!  Works fine.  thanks!
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question