1

I have an access database that I am making a C# interface for. I'm not using the built in one because I do not know VB.

For both tables in the database I want IDs to be automatically generated rather than having the user input them. The only way I can think of to do this is:

  1. Generate a random ID
  2. Check every record in the table to make sure non have the same ID.
  3. If it is unique use that ID. Otherwise generate a new ID.

I just think there must be a better more efficient way to do it. Can anyone help me?

Also my database will need to later be accessed from different computers across a network. Is it alright to use Access for this. Are there any advantages of using other databases like MS-SQL?

Hauzron
  • 305
  • 1
  • 3
  • 19
  • 4
    Just let Access auto generate the id. There is no need to create that manually. Just define the id field as auto increment. – germi May 29 '15 at 12:01
  • 1
    I'm pretty sure Access has an auto-id column type... [check this out](http://stackoverflow.com/questions/1072932/how-to-create-an-autonumber-field-value-in-access) – crashmstr May 29 '15 at 12:02
  • 3
    I would use MS-SQL instead, there is a free edition - SQL Server Express. Access can be used accross a network but it involves putting the DB on a network drive and its all just really 1990s. – codeulike May 29 '15 at 12:07
  • 2
    I would definitely suggest using SQL Server Express if you're not using the VBA stuff provided by Access. It will support much larger databases and handles multi-user access significantly better. – Phylogenesis May 29 '15 at 12:10
  • 1
    If you aren't using the Access front end just use MySQL or MS SQL Express. While access will work over a network with several users at once but it is easy to corrupt and difficult to secure. – Matthew Whited May 29 '15 at 12:10

2 Answers2

1

Access has an auto-id column that will do this for you.

However if you were going to do it yourself (not advisable) you would have a table like this:

TableName     NextId
---------     ------
SomeTable     123
SomethingElse 4567

i.e. track the last used ID for each table. In practice its a bit more complex because you have to use locking to make sure when a new ID is generated just one person uses it. Access uses some mechanism like this internally to generate its auto ids.

While we're at it I suppose you could just use GUIDs. They are virtually guaranteed to be unique throughout the known universe each time you generate one. See C# how to create a Guid value?

Access databases can be shared - you basically have to put the database on a network drive. Its a very 1990s sort of approach.

Ultimately though you're better off using MS-SQL - there is a free edition called Sql Server Express. Really Access is only useful if you're using it for the front-end too. If you're doing a front end in C# you might as well go for MS-SQL.

MS-SQL supports auto IDS - it calls them Identity columns.

Community
  • 1
  • 1
codeulike
  • 22,514
  • 29
  • 120
  • 167
0
  • Generate a random ID
  • Check every record in the table to make sure non have the same ID.
  • If it is unique use that ID. Otherwise generate a new ID.

I hope you see the problem with this approach. The more rows there will be, the longer it will get to insert data.

Otherwise, the standard method if you don't have an auto-incremental ID is like this:

select (count(*) + 1) as newId from mytable
thomasb
  • 5,816
  • 10
  • 57
  • 92