Creating Database Question.

[berry2]

I am creating an windows application in vb.net 2003 and have some questions about how to create the Access database.

1) Do I need to create relationships in Access. I can do this in VB.net if I need to but do I also need them in the database?

2) I think I need a primary key set in the database. I get errors telling me that no key exists in the database so it can't update or insert. Is this the caes?

3) If I need a primary key, I need the keys to accept duplicate values. I can't seam to get this done. Access won't allow me to create a key with duplicates. What am I doing wrong? Do I just need to add a autoincrement a column that is not used in my program?

Thanks for the help,
Steve

 

[Summerwind]

1) You don't need to set relationships in Access

2) You don't have to have a primary key either, but you ought to

3) You can create an index with duplicates but I don't know that this is very efficient

 

[boblarson]

3) If I need a primary key, I need the keys to accept duplicate values. I can't seam to get this done. Access won't allow me to create a key with duplicates. What am I doing wrong? Do I just need to add a autoincrement a column that is not used in my program?

Okay, a little instruction here - A PRIMARY KEY, by definition CANNOT have a duplicate value. It must be non-duplicated because how would you identify a record (row of data) uniquely otherwise?

You should ALWAYS, again I say, you should ALWAYS have a primary key in your table. Access doesn't force you to do so, but you should always have a primary key as that will let you identify a unique record and then manipulate it. You can store additional information in that record that is duplicated (and indexed, as mentioned by Summerwind).

 

[berry2]

Thanks for the reply.

I thought that was the case. I am having trouble with desining my database that includes keys. If I posted what I have would someone tell me how I should change it?

I was going to print the relationship but I get an error when I click on the "print" menu option in access, "Object variable or With block variable not set" and I don't know where to look to fix this error. Any help?

I am assuming that when I get it printed I can attach the relationship file or would it be best to attached the database I have?

I would be most appreciative of any help.

 

[boblarson]

If you can run Compact and Repair, then zip the database, you should be able to post it here if it is less than 393Kb in size. If it is still too big then we would have to have you email it, but that's the last resort.

 

[berry2]

I ran Compact and repair. Attached is the database.

 

[boblarson]

a quick cursory glance tells me that, yes, your relationships (and possibly some of the tables) could use some help. I will work at this but it may take several hours before I get back to you since it will require more work than I can do while at work.

 

[berry2]

No big hurry, I appriaciate the help.

Steve

 

[boblarson]

Okay, here's a bit of it for you:
Each table should have a Primary Key created using an autonumber and you should not have your primary key as text.

Then, to tie it to another table you have that same field name in your other table as Long Integer and is the foreign key, but it still has a primary key created using an autonumber.

Your Event table appears to have duplicate data (Wedding master has contact info and the event table also has contact info – not good if it is the same, if it is totally different then that’s possibly ok). But, I would just create a contacts table and then you can have all the contacts you want for an event.

I am going to give you some screen shots and a bit of explanation and let you go at it. I didn't do all of the tables as it looked like you didn't have all of the ones you'd need anyway.

 

[berry2]

Bob,

You have been a big help. Your post has answered my questions and I have implemented the changes.

The "Contact" in the WedMst table is a second name and phone number for the bride, that is why I left it in the WedMst table. I don't have the dates and events tables tied together because a lot of the dates aren't really events but when things are going to happen. As I understand it, I could or should (and probably will in time) create other tables to optimize starage. Like the WedMst table, it has duplicate information in each row, bride and groom address table, phone number table, etc. For right now as a newbie I am overloaded with what I am doing now.

Again, many thanks

Steve