Creating A table with a macro

[shaybob1711]

Hello all. Without going into the long story of what I'm trying to accomplish I will just ask a simple question. Is it possible to have a Macro create a new table with pre-set field names and data types or would I actually have to use VB code to accomplish this? Also, I really haven't looked into this yet, does access have a limit on the number of tables you can have other than the 2gb size limit. Thanks.

 

[pbaldy]

The only way I can think of with a macro would be to execute a saved DDL query. You can search for "specification" in help to see the limits for your version of Access. IIRC there's no limit on the number of tables, but there is on the overall number of objects.

That all said, I hope you're not creating tables for every customer, month, year or something like that. It would likely be a mistake.

 

[shaybob1711]

I would be using this to create parts quotes. Each quote can range from 1 item to 200+ items and I figured the best way (if possible) to do this would be to create a table for each quote. I could be dead wrong on that assumption but I am creating this application through trial and error (I'm not at all experenced with Access, let alone designing what I'm trying to design). Thanks for the help.

 

[pbaldy]

I would use the standard setup used for invoicing. The relevant tables in your case would be a "master" table that stored the main info (Quote number, customer, date, etc), and a "details" table that stored the line items (quote number to relate to main table, item code, quantity, price, etc). If a quote was for 10 different products, there would be 10 records in the detail table. Having the quotes this way gives you a lot more flexibility. Read up on normalization, which is critical to good database design. There are all kinds of links; here's one:

http://www.mdbmakers.com/forums/showthread.php?t=2583

 

[shaybob1711]

I may be over thinking this (I do that quite a bit) but I want to make sure I understand what you are saying. I will need just the 2 tables and in the details table each item quoted for any quote number will have a record associated with it. If this is the case how would I avoid having the user to enter the quote number for every line item in the form. Would I do this through a subform, split-form, or is there something I'm missing. Once again thanks for the help (I'm in way over my head on this one).


Also, thanks for the normalization info. Would you have any recommendations for books on design, normalization, deployment ect? I have a book on the in's and out's of creating the Db but it doesn't say anything about design strategy. Thanks again.

 

[pbaldy]

You could use a form/subform, with the quote number field specified in the master/child links. The user would enter the basic data in the form, the details in the subform. The master/child link would automatically populate new subform records with the quote number.

You could have other tables, for customers, products, etc. These two would handle the actual quote data.

Some thoughts on books:

http://www.mdbmakers.com/forums/showthread.php?t=2678