Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2001
    Posts
    46

    Unanswered: Autonumber which start with JH20001.. to JH99999

    1 ) I want to set my autonumber field to start with JH200001, how?

    2 ) I have 2 database say database1 and database2
    In database1 I use query to join 2 or 3 table of database1 , say query1
    In database2 , how can I setup table so that can link to database.query1. and every update of table [ or most in query1 ] in database1 will be immediately transfer to database2.

    Database1
    ------------
    tbl1
    tbl2
    query1 = join[tbl1 and tbl2]

    Database2
    ------------
    resulttbl

    Where


    resulttbl = query1
    Last edited by edmun3; 12-23-02 at 13:30.

  2. #2
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Re: Autonumber which start with JH20001.. to JH99999

    I'm sorry to say that, but NO and ..... NO

    the solution is MS SQL (you can use MSDE - it came with your MS Office 2000).... their autonumber is called IDENTITY and it's much more flexible.

    MS SQL as most of client-server databases use triggers, so you can use that if you need special autonumber (combination of character and number or date.... - if you add new record to the table, it automatically generates very special unique new record identificator PX20021223_2326). Personally I would recommend to use ONLY number as IDENTITY and if you need something special(character and number), have it as second field..... linking very large tables using just numeric field is much faster.

    Triggers is also the answer to your second question. If you update table 1 in database 1, it runs automatically trigger (stored procedure) and changes data in table 2 in database 2.


    if you HAVE to use MS Access MDB file then don't create autonumber, but reg. field and have an procedure for updating this field (again I think it's better to have reg. autonumber and this identificator as second field).

    You can create schedule and run your unique small program let's say every minute and check if the structure of a table changed (each table can have for example LAST_UPDATE field..... so you can check that field)....



    jiri

  3. #3
    Join Date
    Sep 2001
    Posts
    46
    I dont know about Triggers ,can you give me example.

    Thanks in advanced.

  4. #4
    Join Date
    Nov 2002
    Location
    San Francisco
    Posts
    251

    Re: Autonumber which start with JH20001.. to JH99999

    it would be very very long email.... some super guru may correct me, but...

    simply say TRIGGER is a STORED PROCEDURE and STORED PROCEDURE in a set of SQL commands. I don't want to say query, because you can do much much more in STORED PROCEDURE.

    There are two types of TRIGGERs in SQL2000 AFTER trigger and INSTEAD OF trigger. Check this http://msdn.microsoft.com/library/de...l/sql01a17.asp


    jiri

Posting Permissions

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