If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Showing the actual database, not access's idea of it...
I've been working with MS Access for the past few weeks. A friend has a side
job working for a summer camp and he needed a database to store all the kids information. Using VBA is not going to happen, I thought I could do what he needed much much easier in visual studios.net 2008 and I probably could have if that department of Microsoft didn't do what appears as drastic as disown the MS Access department. After much work I am able to read/replace and edit the data, but through the whole process I started noticing this. First, in Access the ID(auto number) is listed correct, 1 - whatever. But when I look at the data through a datagrid in a vb.net program it does something odd. The numbers don't go in correct order. it might be 1-11, then 25-201 then 12 through 19 and then 202 through the end. This hasn't been a problem and it hasn't effected my program in anyway, just something I thought was weird. Another puzzle started just the other day, which is what brings me here now. The person doing the data entry part of this project is on and off using my program to enter data, and using ms access. Mainly because of bugs that pop up in the code that need to be hammered out. While filling in info with in access, he moved 2 fields to have an easier flow. Well, these 2 fields were the 2 fields used to pull info between that and another database and I knew it was going to cause problems with my code. But it didn't. When I look at the DB with in vb.net it moves the fields to the location they need to be, data and all, but with in access its moved to the new location. So here is my question. What is the true representation of my database? I know access is probably doing some sort functions which keep the ID's in order, but even clicking the "remove all sorts" button does nothing. And why are fields in different locations depending on what I'm using to look at them? Whats their true locations? How do I make Access display the database as it is, not how it believes it is, if that is even the problem I'm having at all. One other question, how do you re-assign ID numbers? add five records, remove the first and "1" is gone forever. I'm sorry for the length of this question. I have this funny feeling that this is a DOH moment. That I'm over looking something. I'm sure that this exact problem has been asked and solved many times over, but I don't even know what to look for. What is this problem I am having? So thanks for reading it all, and thanks for any help offered! |
#2
|
|||
|
|||
Showing the actual database, not access's idea of it...
Nick
Access has an Autonumber datatype that uniquely identifies a table row. If you delete the record with ID=1 (and it's an Autonumber), you WANT #1 gone forever! Otherwise, if you had ANY other tables that used that value as a foreign key (oh, say, table1 is the kid into and table2 has purchases at the store), then deleting KidID=1 means you orphan all those purchase records in table2!). If the user moved the fields around, but you still can see them, what does it matter? Access tables are, loosely thinking, buckets o' data. There's no inherent (or humanly perceptible) order, but you can put data in and get data out. So again, what does it matter? Are you interested for scientific reasons, prurient interest, or out of concern? More info, please... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Nick" wrote in message ... I've been working with MS Access for the past few weeks. A friend has a side job working for a summer camp and he needed a database to store all the kids information. Using VBA is not going to happen, I thought I could do what he needed much much easier in visual studios.net 2008 and I probably could have if that department of Microsoft didn't do what appears as drastic as disown the MS Access department. After much work I am able to read/replace and edit the data, but through the whole process I started noticing this. First, in Access the ID(auto number) is listed correct, 1 - whatever. But when I look at the data through a datagrid in a vb.net program it does something odd. The numbers don't go in correct order. it might be 1-11, then 25-201 then 12 through 19 and then 202 through the end. This hasn't been a problem and it hasn't effected my program in anyway, just something I thought was weird. Another puzzle started just the other day, which is what brings me here now. The person doing the data entry part of this project is on and off using my program to enter data, and using ms access. Mainly because of bugs that pop up in the code that need to be hammered out. While filling in info with in access, he moved 2 fields to have an easier flow. Well, these 2 fields were the 2 fields used to pull info between that and another database and I knew it was going to cause problems with my code. But it didn't. When I look at the DB with in vb.net it moves the fields to the location they need to be, data and all, but with in access its moved to the new location. So here is my question. What is the true representation of my database? I know access is probably doing some sort functions which keep the ID's in order, but even clicking the "remove all sorts" button does nothing. And why are fields in different locations depending on what I'm using to look at them? Whats their true locations? How do I make Access display the database as it is, not how it believes it is, if that is even the problem I'm having at all. One other question, how do you re-assign ID numbers? add five records, remove the first and "1" is gone forever. I'm sorry for the length of this question. I have this funny feeling that this is a DOH moment. That I'm over looking something. I'm sure that this exact problem has been asked and solved many times over, but I don't even know what to look for. What is this problem I am having? So thanks for reading it all, and thanks for any help offered! |
#3
|
|||
|
|||
Showing the actual database, not access's idea of it...
Nick,
I believe what you are eluding to is how is the data stored in tables. Well, the tables are like buckets of water... pour in new water and it just mixes with the old water becoming impossible to separate and all mixed up. The tables just hold data, the order of the fields and the records is irrelevant. If you want some order to them use queries bound to forms. The same holds true for the forms, it doesn't matter where you place the field on the form... the table could care less. The tables are just buckets of water, you want order... make ice cubes (use queries). As for the Autonumber ID field... that's just there to uniquely identify the record (Primary Key or, as some would call it, Unique Key) to be used for any number of reasons *except* numbering the records, as they will skip. If you care about the order of the records or the value you can either use a Natural Key or DMax(), depending on what you are looking for. You cannot renumber the Autonumber ID field, since you care about that number you might want to consider using DMax(). As for sorting, don't waste your time sorting a table... as mentioned prior this needs to be done in a query. You can sort the table then remove the sort but that will be overwritten by sorts you do in queries and on forms. But if there is nothing sorted or anything filtered, clicking the Remove Sort or Filter button will do nothing. You would actually have to apply a Sort or Filter to see that button do anything. All of the above is nothing new for Access, it has always treated tables and Autonumber fields the same way. So I don't understand your question, "What is the true representation of my database?". Since you're not an Access person, I'm going to suggest some reading... Might be overkill but you might find some additional answers... Jeff Conrad's resources page... http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page... http://www.mvps.org/access/resources/index.html A free tutorial written by Crystal (MS Access MVP)... http://allenbrowne.com/casu-22.html MVP Allen Browne's tutorials... http://allenbrowne.com/links.html#Tutorials Sample data models... http://www.databasedev.co.uk/table-of-contents.html -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Nick" wrote in message ... I've been working with MS Access for the past few weeks. A friend has a side job working for a summer camp and he needed a database to store all the kids information. Using VBA is not going to happen, I thought I could do what he needed much much easier in visual studios.net 2008 and I probably could have if that department of Microsoft didn't do what appears as drastic as disown the MS Access department. After much work I am able to read/replace and edit the data, but through the whole process I started noticing this. First, in Access the ID(auto number) is listed correct, 1 - whatever. But when I look at the data through a datagrid in a vb.net program it does something odd. The numbers don't go in correct order. it might be 1-11, then 25-201 then 12 through 19 and then 202 through the end. This hasn't been a problem and it hasn't effected my program in anyway, just something I thought was weird. Another puzzle started just the other day, which is what brings me here now. The person doing the data entry part of this project is on and off using my program to enter data, and using ms access. Mainly because of bugs that pop up in the code that need to be hammered out. While filling in info with in access, he moved 2 fields to have an easier flow. Well, these 2 fields were the 2 fields used to pull info between that and another database and I knew it was going to cause problems with my code. But it didn't. When I look at the DB with in vb.net it moves the fields to the location they need to be, data and all, but with in access its moved to the new location. So here is my question. What is the true representation of my database? I know access is probably doing some sort functions which keep the ID's in order, but even clicking the "remove all sorts" button does nothing. And why are fields in different locations depending on what I'm using to look at them? Whats their true locations? How do I make Access display the database as it is, not how it believes it is, if that is even the problem I'm having at all. One other question, how do you re-assign ID numbers? add five records, remove the first and "1" is gone forever. I'm sorry for the length of this question. I have this funny feeling that this is a DOH moment. That I'm over looking something. I'm sure that this exact problem has been asked and solved many times over, but I don't even know what to look for. What is this problem I am having? So thanks for reading it all, and thanks for any help offered! |
#4
|
|||
|
|||
Showing the actual database, not access's idea of it...
You type faster then I do...
-- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "Jeff Boyce" -DISCARD-HYPHEN-TO-END wrote in message ... Nick Access has an Autonumber datatype that uniquely identifies a table row. If you delete the record with ID=1 (and it's an Autonumber), you WANT #1 gone forever! Otherwise, if you had ANY other tables that used that value as a foreign key (oh, say, table1 is the kid into and table2 has purchases at the store), then deleting KidID=1 means you orphan all those purchase records in table2!). If the user moved the fields around, but you still can see them, what does it matter? Access tables are, loosely thinking, buckets o' data. There's no inherent (or humanly perceptible) order, but you can put data in and get data out. So again, what does it matter? Are you interested for scientific reasons, prurient interest, or out of concern? More info, please... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Nick" wrote in message ... I've been working with MS Access for the past few weeks. A friend has a side job working for a summer camp and he needed a database to store all the kids information. Using VBA is not going to happen, I thought I could do what he needed much much easier in visual studios.net 2008 and I probably could have if that department of Microsoft didn't do what appears as drastic as disown the MS Access department. After much work I am able to read/replace and edit the data, but through the whole process I started noticing this. First, in Access the ID(auto number) is listed correct, 1 - whatever. But when I look at the data through a datagrid in a vb.net program it does something odd. The numbers don't go in correct order. it might be 1-11, then 25-201 then 12 through 19 and then 202 through the end. This hasn't been a problem and it hasn't effected my program in anyway, just something I thought was weird. Another puzzle started just the other day, which is what brings me here now. The person doing the data entry part of this project is on and off using my program to enter data, and using ms access. Mainly because of bugs that pop up in the code that need to be hammered out. While filling in info with in access, he moved 2 fields to have an easier flow. Well, these 2 fields were the 2 fields used to pull info between that and another database and I knew it was going to cause problems with my code. But it didn't. When I look at the DB with in vb.net it moves the fields to the location they need to be, data and all, but with in access its moved to the new location. So here is my question. What is the true representation of my database? I know access is probably doing some sort functions which keep the ID's in order, but even clicking the "remove all sorts" button does nothing. And why are fields in different locations depending on what I'm using to look at them? Whats their true locations? How do I make Access display the database as it is, not how it believes it is, if that is even the problem I'm having at all. One other question, how do you re-assign ID numbers? add five records, remove the first and "1" is gone forever. I'm sorry for the length of this question. I have this funny feeling that this is a DOH moment. That I'm over looking something. I'm sure that this exact problem has been asked and solved many times over, but I don't even know what to look for. What is this problem I am having? So thanks for reading it all, and thanks for any help offered! |
#5
|
|||
|
|||
Showing the actual database, not access's idea of it...
On Tue, 16 Mar 2010 14:12:02 -0700, Nick
wrote: So here is my question. What is the true representation of my database? I know access is probably doing some sort functions which keep the ID's in order, but even clicking the "remove all sorts" button does nothing. And why are fields in different locations depending on what I'm using to look at them? Whats their true locations? How do I make Access display the database as it is, not how it believes it is, if that is even the problem I'm having at all. One other question, how do you re-assign ID numbers? add five records, remove the first and "1" is gone forever. Two very common misconceptions he 1. A Table HAS NO ORDER. Access will store the records on disk in any order it finds convenient. This order might even change, say if you compact the database. If you want to see records in a particular order, you must - no option! - use a Query sorting the records by some field or fields in the table. 2. An Autonumber HAS NO MEANING. It's not guaranteed to be sequential; it's not guaranteed to be free of gaps; it's not a "record number". ALL it is is a meaningless unique identifier for the record. Once an autonumber is assigned in a table, that value gets "used up" and won't be used again - and it gets used up even if you just start to enter a new record and cancel the addition after the first keystroke. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|