Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Posts
    13

    Unanswered: Primary Key Problems

    Hello,

    I am currently using Access 2000 from Microsoft and have an extensive customer Database set up.

    I am having problems with the primary key.

    I have a field that is the primary key and is done using numbers.
    Occasionally I will check the inner workings of the database and I have noticed that the primary key has been removed from the settings of the database. When I attempt to add it again, I usually find that a number in the primary field has been duplicated and I have to clean that up before changing it.

    What I am trying to find out is if there is a reason or case where the primary field would be removed from the database (beyond the obvious of someone turning it off or deleting it). If I can find out what might cause this I can possibly stop it from happening again.

    Thanks
    Chad Kirby

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    There is no Primary Key for a database, but you can have a Primary Key for any of your Tables within the database. If the problem is that the Primary Key is going away for a table that you have set a Primary Key on, the only thing that I can think of that could be causing this is if you are running a MakeTable query that is replacing the table and thus not setting a Primary Key in the new table. Do you have any Make Table queries?

    TD

  3. #3
    Join Date
    Nov 2004
    Posts
    13
    Sorry TD,

    what I did mean is that I have a primary key for a table that keeps going away... that was inproper wording on my part.

    I do not have any queries that are running anything to do with making a table. There really isnt anything highly advanced with this database other than the fact that it contains a large amount of specific data. There are no complex queries or anything of the sort.

    Would a crash or improper compact and repair have any influence? I have the system automatically compact and repair at close. Also note that I use this database across several computers on a PTP netowrk. This may have certain concequenses that I dont yet understand, but I also dont know the proper way to secure the database when using it this way. There may be cases when more than one computer has the database open.

    Thanks
    Chad Kirby

  4. #4
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    Hi Chad,

    I was hoping that you were running a MakeTable query. Compact on Close should not cause a problem. Does this table reside in the DB in question rather than being linked in from an external source?

    I have looked thru the Microsoft Knowledge Base and I'm not finding anything like this. I am pretty much stumped by this. I've not experienced anything like it.

    TD

  5. #5
    Join Date
    Mar 2004
    Location
    belgium
    Posts
    290
    strange to me too, but any remark can be a hint so :

    is there any code what alters the PrimaryKey property ?

  6. #6
    Join Date
    Nov 2004
    Posts
    13
    This table is right in the database, and there are really no special features that it does.

    Like I said before I do have compact and repair upon the closing of the database. I have some Queries designed, but none of them are anything more than simply data arrangement (E.G. Show me only what I want to see).

    I have a few forms to enter data into the database and I also have a few reports to view some of the information that I pull in a bit of a better format. I am fairly GREEN to the whole database thing so the amount of complex and difficult items that I do are slim to nil.

    It never fails that I will check and the primary key will be removed from the database.

    I have one other table within this particular database, but again it isnt anything special. The two tables are NOT linked in anyway.

    hmmm, im trying to think of any information that might help you. I have just removed a duplicate entry so I can now set the primary field again and save it. I am going to keep an eye on it a little closer and see if I can come up with an instance when the primary key is removed.

    Any other ideas?

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    have you inherited this database
    it is entirely possible that the table you are using is being accessed by another application either another Access database or a visual basic / visual C application that is dropping the table and re-creating. I've never heard of a column dissapearing in a table. Something soemwhere is doing the dirty deed.

    We often use make queries in transfer / interface databases - which can be a beggar to understand on first contact. So I would check any other databases in the same area. Where is the data in the database coming from?, ie is all the data captured in the forms in the database or is it being populated by another external system

    I'd check to make sure that no form in the database uses the 'drop table' syntax.
    HTH

  8. #8
    Join Date
    Nov 2004
    Posts
    13
    Well this database was "designed" (and I use that loosely!) by the previous person in my position. She was NOT a computer programmer and her knoledge of computers was limited. The database was just used as a place to store all the information from the customers and appointments that we do. I adjusted and revamped it to include more information and try and show things that were important to the company.

    There really isnt any other programs that are getting in and using the database that I am aware of. To be honest I dont really have the knoledge to do any programming.

    For the longest time all data was being entered into the table simple by opening the table and adding the information that way. After doing some reading I thought that using a form to enter helped out in a few ways. It made things faster cause you didnt need to scroll from left to right all the time. You could make sure that all the information that you were looking for would be entered. My understanding was forms were the proper way to enter the information. I didnt add anything special to the forms, just the fields that I wanted to transer directly into the tables.

    If I was trying to find a 'Drop Table' Syntax in a form, where would I look?

    Thanks

    Chad Kirby

  9. #9
    Join Date
    Jan 2005
    Posts
    1

    Hello

    I have experienced exactly the same problem and I find out by another forum that it would be a problem with Access db when it is used on a network with several Operating sistems... like win95, win98, winNT working at tha same time and the server system whas not a winXP..
    In my case our Server was working under WinNT.. we changed the server to another using winXP and it never hapens again...
    I hope it will help.. sorry about my bad English
    Dirceu

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    assuming you dont have saved action queries doing something strange

    ALT-F11 and do edit¦find (in current project) for
    ALTER
    MAKE
    DROP
    to see if you have some wierd code messing with your tables.

    other than that, assume that access has gone mad and attempt a fix as follows:

    compact & repair your existing .mdb
    quit
    make a note of the file size

    make a new, empty database called myDecompiled.MDB
    file¦get external data¦import ...ALL the objects from the old db.
    ALT-F11: debug¦compile to make sure everything is still alive.
    quit.

    make a shortcut that does:
    "C:\Program Files\Microsoft Office\Office\MSACCESS.EXE" "C:\path\to\file\myDecompiled.MDB /decompile
    QUIT myDecompiled.MDB that opens after running that command.

    reopen myDecompiled.MDB
    ALT-F11: debug¦compile to make sure everything is still alive.
    if it is OK so far...
    ...compact & repair

    now compare the file size before & after the /decompile. if this was an old db that has never been decompiled before, it is certainly now smaller and probably works more reliably.

    NEVER /decompile your "real" db ---- it kills some db stone cold irretrievably dead. always work on a new empty db into which you have imported all the old objects.

    izy
    currently using SS 2008R2

  11. #11
    Join Date
    Oct 2004
    Posts
    57
    I am having this same problem....has microsoft ever found a fix??

Posting Permissions

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