Question about field design when planning a VB front-end to Access database

[Atreju]

I apologize in advance for the x-post, but I am really not sure where
this would best be addressed.

I am designing a database in MSAccess for which I want to make the
front-end in VB.

I have planned out the basic structure of the tables and fields.
However, my experience with Access is limited to doing all the work
within Access, so I have a couple of issues I'm confused about.

1) Relationships: If I want a One-to-Many relationship between two
tables based on an ID number, in Access I used the auto-number feature
and then used the lookup feature in the other table, and viola, that's
all there was to it.

In Visual Basic, will I have to maintain the relationship on my own?
Does the Access database under the application respond with an error
if there's a problem, or what?

2) I am going to be assigning a value to a table's ID field. I am
going to want to take a set of predetermined factors to generate part
of the number, and then I want the last two digits to be an increment
from the most recent entry. How is my VB form going to check to see if
this ID is unique before writing it to the database?

In this instance I can give a specific example:
InvoiceID field - Indexed, no duplicates.
This number will be a representation of the YY,MM,DD + xx where xx
will simply be incremented for each item added on that day.

Eg: Today if I generate 3 invoices, they will be assigned the IDs:
031230-01, 031230-02, 031230-03. Would I simply lookup the last number
that matches the current date and then increment by one?

I know this is a lot.. it's just my first time coding a VB app with an
Access database with relationships and ID numbers.

Thanks for any advice you can give me.
---Atreju---

[Jerry Boone]

1 - Build queries in the database and fetch data from them using either a
data control or DAO, or ADO components. You may have to hand code some
queries and let your choice of data access fetch the data if you want data
that has variable criteria. There are great books by William Vaughn on ADO
(better get used to this now) that are actually fun (for a programming
manual) and you will learn almost everything you need to know about
retrieving and saving data. The first thing you have to remember when
leaving the comforts of Access.exe is that you are now "on your own" and
have to fend for yourself. All of the things you took advantage of in
Access have to be either bought or designed manually, but it's a great
experience - especially when you see how much less memory your application
is using.

2 - Once you get some queries written and learn to "ask" the database
questions using sql statements, you will be able to determine if you have
already used the id number. There are probably better ways for you to do
this...

Good luck - do a lot of reading and save the questions that you can find in
the book for the newsgroups.

--
Jerry Boone
Analytical Technologies, Inc.
http://www.antech.biz
Secure Hosting and Development Solutions for ASP, ASP.NET, SQL Server, and
Access
"Atreju" <so*****@who.hates.junkmail> wrote in message
news:qi********************************@4ax.com...

I apologize in advance for the x-post, but I am really not sure where
this would best be addressed.

I am designing a database in MSAccess for which I want to make the
front-end in VB.

I have planned out the basic structure of the tables and fields.
However, my experience with Access is limited to doing all the work
within Access, so I have a couple of issues I'm confused about.

1) Relationships: If I want a One-to-Many relationship between two
tables based on an ID number, in Access I used the auto-number feature
and then used the lookup feature in the other table, and viola, that's
all there was to it.

In Visual Basic, will I have to maintain the relationship on my own?
Does the Access database under the application respond with an error
if there's a problem, or what?

2) I am going to be assigning a value to a table's ID field. I am
going to want to take a set of predetermined factors to generate part
of the number, and then I want the last two digits to be an increment
from the most recent entry. How is my VB form going to check to see if
this ID is unique before writing it to the database?

In this instance I can give a specific example:
InvoiceID field - Indexed, no duplicates.
This number will be a representation of the YY,MM,DD + xx where xx
will simply be incremented for each item added on that day.

Eg: Today if I generate 3 invoices, they will be assigned the IDs:
031230-01, 031230-02, 031230-03. Would I simply lookup the last number
that matches the current date and then increment by one?

I know this is a lot.. it's just my first time coding a VB app with an
Access database with relationships and ID numbers.

Thanks for any advice you can give me.
---Atreju---

[Atreju]

I thank you for your advice.

I think I am going to get a book dedicated to learning database
programming in VB.

Thanks once again!

PS - I decided to code the ID number by using a sequential ID which
gets updated on its own in a single field table to keep track of the
last number used.

I recall a colleague of mine referring to this as a "Surrogate Primary
Key" in FoxPro.
On Tue, 30 Dec 2003 21:25:12 GMT, "Jerry Boone"
<je***@antech.biz.killspam> wrote:

1 - Build queries in the database and fetch data from them using either a
data control or DAO, or ADO components. You may have to hand code some
queries and let your choice of data access fetch the data if you want data
that has variable criteria. There are great books by William Vaughn on ADO
(better get used to this now) that are actually fun (for a programming
manual) and you will learn almost everything you need to know about
retrieving and saving data. The first thing you have to remember when
leaving the comforts of Access.exe is that you are now "on your own" and
have to fend for yourself. All of the things you took advantage of in
Access have to be either bought or designed manually, but it's a great
experience - especially when you see how much less memory your application
is using.

2 - Once you get some queries written and learn to "ask" the database
questions using sql statements, you will be able to determine if you have
already used the id number. There are probably better ways for you to do
this...

Good luck - do a lot of reading and save the questions that you can find in
the book for the newsgroups.

---Atreju---