Showing the actual database, not access's idea of it...

  • Thread starter Thread starter Nick
  • Start date Start date
N

Nick

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!
 
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,

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/accessjunkie/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

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!
 
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

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.
 
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.
Click to expand...

Two very common misconceptions here:

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.
 
Back
Top