Member Avatar for dh111

Is this the right place to ask the following?

I have 2 tables in two different MS Access DB's. Access allows the ability of inserting data from DBA.table1 into DBB.table1, using the format [dba].[table1].[dbb].[table1]. Here's my problem.

Using a single SQL Statement I can't figure out the following. DBA.table1 has a record, with let's say ID 300. DBB.table1 has a 300 already, matter of fact the ID goes all the way to 550.

How do a write an insert statement that gets the full record from DBB.table1, but swaps puts in the 'next available' ID, which in the case, for DBB.table1, is 551?

  • 2 Contributors
  • 3 Replies
  • 108 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by huangzhi
Member Avatar for huangzhi

Please give some data for an example.
Or you can read help about :
1. SET IDENTITY_INSERT for inserting ID to another table.
2. IDENT_CURRENT('table_name') to return last ID.
3. DBCC CHECKIDENT to return last ID and corrects the identity value

Member Avatar for dh111

Please give some data for an example.
Or you can read help about :
1. SET IDENTITY_INSERT for inserting ID to another table.
2. IDENT_CURRENT('table_name') to return last ID.
3. DBCC CHECKIDENT to return last ID and corrects the identity value

All of these examples are transact SQL. I need MS Access - does transact work there?

Member Avatar for huangzhi

Sorry, I has no idea with MS Access.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.