unique id based on another table

[eshai]

i have 2 table that have the same fields
the have autonumber index without duplicate
how can i make tbl1 to check what is the last number in tbl2
and give the record in tbl1 a number that is not in tbl2
i have append query and i can't run it because duplicate error

 

[MarkK]

Why isn't all the data in one table?

 

[eshai]

Why isn't all the data in one table?

cant one table is for end user and in the second table i'm attaching sensitive material

 

[MarkK]

How will you keep the user out of the second table?

 

[eshai]

How will you keep the user out of the second table?

i use linked table so the dont have the link to the second table

 

[MarkK]

To me your plan adds complexity without security, for instance, I could easily find your data file from the linked table. Then I could open your data file, and open your "secured" table. So for the amount of difficulty you create by having two identical tables, you do not appreciably, IMO, improve security.

I think there are other ways in an Access Db to deny users access to specific data. Deny your users access to the Navigation Pane. Use an accdr or accde file, and then in your user interface elements, your forms and reports, only show the data users are entitled to see.

 

[Galaxiom]

Sensitive data should not be in an Access database because there is no way to properly secure it.

Sensitive data belongs in a database server like SLQ server.

 

[eshai]

Sensitive data should not be in an Access database because there is no way to properly secure it.

Sensitive data belongs in a database server like SLQ server.

not sensitive like army or benk just a student details
pls pls stop Q' i need answer

 

[Galaxiom]

Use this query SQL to get the maximum value without linking the table

SELECT Max([fieldname]) AS NewID 
 FROM tablename
 IN "path to your database file"

However, anyone with the slightest clue about Access will waltz straight in.

 

[Solo712]

i have 2 table that have the same fields
the have autonumber index without duplicate
how can i make tbl1 to check what is the last number in tbl2
and give the record in tbl1 a number that is not in tbl2
i have append query and i can't run it because duplicate error

Apart from what the others have been saying about security, you can't do what you set out to do with autonumbers. Autonumbers are generated automatically. You cannot "give" them. The only solution is to create the primary key for the two tables manually.

Best,
Jiri