Creating a new Table

[PLH]

Hello everybody, I am working on customer oriented app which uses MS access to store customer information and also orders. I have done with customer information part but it seems I have little problem with orders. The way I want to set the orders is to create a new table with customer name and six columns which will hold the order ino. This is where I have a problem. I need to create a new table with columns and add it to an existing database. If I reach this I will have to be able to get the tables names’ as well. Thanks for your answer and time.

 

[Administrator]

Are you asking about creating a new Microsoft Access table in VB.NET code?

 

[PLH]

Yes, and adding it to an existing database.

 

[Administrator]

I'm interested to see the communities advice on this. The only way I know to do this is by referencing either ADOX or DAO and AFAIK you cannot do Access DB modifications from ADO.NET. Now you've entered a realm of deployment nightmares as the end-user must have either ADOX (MDAC) or DAO (which most should have...but nothing is certain).

 

[jmcilhinney]

If it is the case that you have already deployed your app and customers' databases already contain data, I would consider the easiest option to be to simply distribute an empty database with the new schema and create an upgrade utility that transfers their existing data to the new database. I'm not sure if the use of autonumber fields might make this difficult, or perhaps impossible, but I'd certainly look into the option.

I believe that you can also roll MDAC into your setup for distribution, which, from what Neal says, would ensure that you could use ADOX. I've read a bit about ADOX but I've never had the dubious pleasure of using it for real.

 

[DavidT_macktool]

Is it your intention to create a new table for each customer?
If they add to an order later do you create a new table or add to an existing one?
I have not run accross many order systems that function like that. Generally there is a customer table and a order table with a one to many relationship setup between them. The order table contains Customer ID and Order ID(auto increment) as key fields that allow customers to access only orders that apply to themselves - easy to sort, add, update, and delete. Master/Detail forms handle this kind of relationship exceptionally well and are very easy to set up.
I am curious. I wish, for your programming sake, that a single Orders table with six columns and the proper security to ensure justified access to order records would work for your app.

Let's see what information comes out of this thread...

 

[jmcilhinney]

Well picked up David. I didn't read the original post that way but now that you mention it that seems to be the suggestion. I'd like to add my weight to the "don't do it" lobby. David's description is exactly how this situation should be handled, if that is what you are suggesting.

 

[PLH]

Yes, you got it right David. My intention is to create a new table for each customer. I came up with this because the order items are many. For example if the customer orders 5 items (food) then the app should add 5 rows or 5 columns to a single table but what if there is another customer order with 7 items. This will make the table very complicated to manage. The problem is that I don’t know the count of max items since the items could be added or deleted from the menu table through the time. At the beginning I thought to have just one table (orders) which would kip the orders but because of this I decided to make order tables for each customer so it would be easier. Of course I would have a problem with this too when the same customer has two or more orders. Up to now I am not sure which way I need to do it that is where you guys might help me.



< If they add to an order later do you create a new table or add to an existing one?>



I think if the order should be delivered at the same time then the added items could be added to the existing customer order table but if the order should be delivered different time then the app should create a new table with the new items. Again I am not sure what would be the best way to go.



Thanks for your time!

 

[jmcilhinney]

You should have a table for each entity. You need to have a Customer table, an Order table and an OrderItem table. The Customer table has all the details of the customers. The Order table has all the details of the order, like the ID of the customer, which is the link to the Customer table, and any info specific to that order like a custom delivery address. The OrderItem table would contain an entry for each item ordered. It would contain an order ID, which is the link to the Order table, and an item ID, which is the link to the Stock table (which you would also need to store information about every item you have in stock), and a quantity. You should ABSOLUTELY NEVER be doing what you suggested and adding new tables like that. You need to go back to basics and work out exactly what your entities are and create one table for each entity. If you don't know what an Entity-Relationship Diagram is, I suggest you find out and draw one for your database before you even think about implementing anything. As an example, in your case your entities would be things like StockItem, Supplier, Customer, Order, OrderItem, etc. and you need one table for each of those entities. Then, every customer is a row in the Customer table, every supplier is a row in the Supplier table, etc.

 

[PLH]

Thanks a lot, I definitly should try what to suggested. I will post any problem that I may have with this in the future

 

[jmcilhinney]

Microsoft Access comes with samples included. I'm not 100% sure about earlier versions, but 2003 comes with the Northwind database. I'd suggest you install any samples that are available with your version of Access and have a look at them. Also, use the Access help system and MSDN on-line for advice on creating database schema.