Results 1 to 7 of 7

Thread: problem on keys

  1. #1
    Join Date
    Jun 2006
    Posts
    103

    Unanswered: problem on keys

    I have an access database, which use excel as an interface for the user to input data to the database, and the data mainly goes into 2 tables, usually I insert table A then followed by table B. But I need to link the 2 tables in order to let users to update the tables, thus i use an auto generated primary key in table A, before insert into table A, then i try to get the maximum index in table A + 1, then insert into the foriegn key for table B, but i have problem on keeping the index accurate, the error wont happen offen, around once per 50-100 records insertion. Is there any better solution to get the index correctly? Can anyone tell me why the index happen this way?

    Thanks.

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Why use Excel? - Access has forms, which give you substantially more control over the process and less overhead (meaning "faster").

    Setup the tables as "parent-child", use forms setup as such and Access will manage the foreign keys for you with errors.

    good luck,
    tc

  3. #3
    Join Date
    Jun 2006
    Posts
    103
    i have reason not to use access form to insert data, because I am in an environment which has about 400 users, and their pc don't have Microsoft access installed, the only applications they all have in command is excel and words, and the users are good in excel. I have thought of using VB, but again, they dont have a licence in VB development tools, thats why i need to use excel. Here is a well-know bank, and pirate software is definately a 'NO'.

    Any more suggest will be much appreciated.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Probably the best way to handle this would be to use a vba function to read the results from the Excel spreadsheet(s) and then post the values into the tables.

    you can do all your error checking and validation at the same time.


    as you are controlling the insert procress you can always get the parent autronumber value to act as the foreign key.

    an alternative approach would be to consider using the another method of making the record unique. Afterall the autonumber has no intrinsic value, all you need is something that makes it different form any other value. one option may be to consider using the computer that is doing the datacapture and say the timestamp the data was captured in Excel as your primary key. that way round you (probably) also have a mechanism of knowing who did the data capture and when.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    And, once you get it working and have time to breathe, look into Access Run-Time. Run-Time can be freely distributed as long as you have the one liscense on the development PC.

    This will let you develop and distribute an Access app without having to purchase 400 copies of Access.

    tc

  6. #6
    Join Date
    Jun 2006
    Posts
    103
    Probably the best way to handle this would be to use a vba function to read the results from the Excel spreadsheet(s) and then post the values into the tables.

    you can do all your error checking and validation at the same time.


    as you are controlling the insert procress you can always get the parent autronumber value to act as the foreign key.
    Yes, currently I am doing this way.

    Currently the way i m doing it is before i insert into table A, i will make a query which get the max index (which is auto number primary key for table A), then i put the result onto a sheet, then, use vba to read the value, then i add 1 to it (named the value B_Index, then i proceed the insertion data to table A, then use the value of B_Index as a foriegn key, then insert data into table B.

    however, using this method, the problem encounter is just like i say, about 50-100 record inserted, 1 might have a problem to get a wrong index of having 2 same value for foreign key in table B, or it happens once, the auto number index in table A jumps a number (eg. i have 100, but no 101, then next record is 102) i m the only ppl can direct access the database, so i know no one is deleting my records. The user excel application is not make to delete record from the database.

    an alternative approach would be to consider using the another method of making the record unique. Afterall the autonumber has no intrinsic value, all you need is something that makes it different form any other value. one option may be to consider using the computer that is doing the datacapture and say the timestamp the data was captured in Excel as your primary key. that way round you (probably) also have a mechanism of knowing who did the data capture and when.
    __________________
    Mark James (the reprobate formally known as Mary, primarily 'cos she is so contrary)
    -----------
    immoderation in all things
    2nd option not possible, because the users might directly insert record for table B, which then i set the foreign key to table A to default value of 0 (if they directly insert record for table B, which it has no reference on table A), thats why i cant make this key unique (is possible to have many zero value for this field)


    for Tcace's suggestion, it will be great that the user can use access without license, may i know where i can set the access Run-Time option that you were talking about?

    however, I already developed the excel application, currently the users are using it fine, just it happens every 2 days, that the index messes up. is easy to make changes, i just change the index (foreign key) in table B to the correct value. It just i dont want to keep doing this in the future. Thats why i prefer to solve this issue once for all.

    I always like to try and learn out new things, I will appreciate any creative suggestion. By the way, thanks for the reply.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    runtime is an additoinal access product. if your bank is big enough it may well already have a copy of RT, if not it costs for a one off purchase.

    as regards yyour key [problems, not knowing your user requirement it sounds as if the physical design of the tables is flaky - and a very good reason to use an Access form to handle data entry. you can do a great deal with macros in Excel to habdle this, but users have a nasty habit of shortciruiting such steps - its a lot harder in an Access MDE.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •