Jim Horn
asked on
How to insert a value into an AutoNumber column
Hi all
Q: Is there a way to insert rows into a table with specific values to insert into the AutoNumber field?
aka, what's the Access equivalent of SQL Server's SET IDENTITY_INSERT OFF?
Reason I ask is because I'm working on an Access automated archive/restore functionality. So say I have the below data...
My VBA code will move any rows with the date column less than a specific amount into an 'archive' database, schema matches but the AutoNumber columns are just Longs. So, if I run my function with a date of 1/1/2006, my table now looks like this:
Thanks in advance.
Jim
Q: Is there a way to insert rows into a table with specific values to insert into the AutoNumber field?
aka, what's the Access equivalent of SQL Server's SET IDENTITY_INSERT OFF?
Reason I ask is because I'm working on an Access automated archive/restore functionality. So say I have the below data...
Customers-Main.mdb
id name date
1 Bob 1/1/2005
2 Jack 6/1/2005
3 Fred 1/1/2007
4 Jerry 12/31/2010
Not shown are bunches of other tables with a foreign key of Customers.id. My VBA code will move any rows with the date column less than a specific amount into an 'archive' database, schema matches but the AutoNumber columns are just Longs. So, if I run my function with a date of 1/1/2006, my table now looks like this:
Customers-Main.mdb Customers-Archive.mdb
id name date id name date
1 Bob 1/1/2005
2 Jack 6/1/2005
3 Fred 1/1/2007
4 Jerry 12/31/2010
All is well. BUT, I'd like to also be able to do a restore as well, meaning in the above example if I restore all rows beginning 1/1/2015, the id=1 and 2 rows move back to the Main.mdb, and because of the foreign key rows I need to specifically insert the values 1 and 2 into the AutoNumber ID field. Thanks in advance.
Jim
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER