I added autonumbering field , I added some values , then decided to delete them . the autoincrement field started this time not from 1 but from the last record was existed (7) . It tells me I can't because it's bound to another table .(I have one relation only) . How can I reset it to count from 1 or even 0 ??
i've never found an easy way around this i'm afraid. You'll have to delete the relationship, delete the column. add it in again, and add the relationship.
Sql sever has a great fix for this where you can set the value to start from whenever you want.
Originally posted by nswan i've never found an easy way around this i'm afraid. You'll have to delete the relationship, delete the column. add it in again, and add the relationship.
Sql sever has a great fix for this where you can set the value to start from whenever you want.
This problem exist in other table also which contains more than 200 records . I can't delete all of them . There must be an easier way around .
only way to sort out the table with the data in is create a temporary table and copy them all in there. Then sort out the autonumber problem, then copy them back in.
Originally posted by nemaroller YOu know, the next value of the autonumber field is in any mdb file.... somewhere in there, it sits...
I'm really surprised no one has attempted to find out where...
exactly . I think I have to replace it with my own numbering system.
Well, we know Autonumbers are Long Integers.... on a Windows system, that 4 bytes' correct?
So if you made a new database file with only one table, with only one field (myID) set to an AutoNumber....
The AutoNumber value stored in the mdb file would have to be zero.... because Access increments when a record is saved...
so add two records, because searching for the value 2 would return a lot less false positives...
so you would need to search for the 2 in byte format (and that sucks, because 23,343 in byte format would be hell lot easier to find)
using a little hand-written program that simply grabs 4 bytes, converts it into a Long, (int32) and if it matches 2, well, that could be the location in the mdb file... and then hopefully, once you overwrote those 4 bytes, and resaved the file... the next number it grabs would be +1 the number you wrote in.
And of course, that location would probably be vary by each different mdb file...
Last edited by nemaroller; Jun 7th, 2003 at 01:32 PM.
I added autonumbering field , I added some values , then decided to delete them . the autoincrement field started this time not from 1 but from the last record was existed (7) . It tells me I can't because it's bound to another table .(I have one relation only) . How can I reset it to count from 1 or even 0 ??
What do you mean 'it tells me I can't because it's bound to another table'? Who tells you? Are you getting an error? Also can't do what? Delete?
Anytime you have a relationship between two records in two tables (if referencial integrity is on) then you have to delete the children before the parent. It does that to maintain data integrity.
You can write a query to delete the parent record with all of its child records as well.
Also whats the difference if the autonumber field starts at 1 or 7?
Originally posted by Edneeis
Also whats the difference if the autonumber field starts at 1 or 7?
Hmm , do you think I don't need to start from 1 ?? Till now I don't know if I'm going to use that number or not ? All what I want is to reset it (if possible) to start counting from 1 .
Originally posted by nemaroller Well, we know Autonumbers are Long Integers.... on a Windows system, that 4 bytes' correct?
So if you made a new database file with only one table, with only one field (myID) set to an AutoNumber....
The AutoNumber value stored in the mdb file would have to be zero.... because Access increments when a record is saved...
so add two records, because searching for the value 2 would return a lot less false positives...
so you would need to search for the 2 in byte format (and that sucks, because 23,343 in byte format would be hell lot easier to find)
using a little hand-written program that simply grabs 4 bytes, converts it into a Long, (int32) and if it matches 2, well, that could be the location in the mdb file... and then hopefully, once you overwrote those 4 bytes, and resaved the file... the next number it grabs would be +1 the number you wrote in.
And of course, that location would probably be vary by each different mdb file...
In a one to many relationship (which is most common) then the One is the Parent and the Many is the children. In other words the Primary Key is the Parent and the Foreign Key is the Child, usually.
So in the SubCat_Tab table MainCat_ID is the Child or Foreign Key of MainCat_Tab and the MainCat_ID field. This relationship means that the SubCat_Tab items that have the same number in the MainCat_ID field 'belong' to the entry in the MainCat_Tab Table. So you can't delete the MainCar_Tab item without deleting all of its sub parts first. This ensures that you don't have disembodied SubCat items that belong to no MainCat items.
I don't think it matters what the number is as long as it matches through out the scheme. So if it starts at one or 10 it doesn't matter.
Tables [list=1][*]MainCat_Tab [holds main category names][*]SubCat_Tab [holds sub categories of the above table][*]SubCat2_Tab [holds subcategories of the above table(sub-sub items)][/list=1]
You can see the relationships between these tables in the earlier post (up there) .
Now , I want to save extra information like : program name , path , size , description , cd number ..etc . Where should I locate space for these fields which will be my depository (those three tables *I think* referencial tables .
I want to be able to add relevant data . For example : one of the records will be in this chain .
It really depends on how you are going to manipulate and use the data....
If what you're looking for is to pull up Internet, and all the programs that fall in that category, then you only need 2 tables.
Table1------------- Table2
Internet
CategoryID
----------------------CategoryID (foreign key to table 1's CategoryID primary key)
--------------------- Program
----------------------Size
----------------------Path
----------------------Description
Because when you bring up the program record, you'll want all that data in that record, and not have to go to another table to grab it...involving another SQL statement...
nemaroller , my query might include size , program name , cd number , or maybe compination of two or three fields . So I need relations , as it's helpful in such problems(I've read that with relational db , you can build any query you want) . I used to do it the way you explained but I wanted to expand the database structure to be more flexible in the future .
I'm thinking of adding new table to save all records in it . For my already three tables , this will only be shown to the user . I won't save anything inside them . Do you think this is a good idea ?
Well, one SQL statement can query data from more than one table and return as little or as many fields from either table as you want....
The important thing is the relationships make sense....
program size, type, its path, are all properties of the program, so they belong in the program table... and really, category, Internet, could be a field in the program table.... ProgramCategory... in which it draws upon predefined categories in a category table (a lookup table) called Categories...
Thanks both of you nemaroller and Edneeis . I'll consider these tips . I've already started coding and quering . a lot of errors here and there ....lol . I may pester you later but bear with me guys .
Reply With Quote
