Question Locked

Access 2007: Creating tables using a form...

0 helpful
11 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hey folks!

I need help big time. I am in the eleventh hour of creating a really complex database for a buddy of mine and I am in need of a special task.

I need a form the user can use to create a whole NEW table with predefined fields.

For example:

The user needs to track a particular brand of item. So with this db they will have a whole table just for that brand of item. Lets assume the user has a new brand they need to add so they go to this form, they select a name for the table and then the user clicks a create button. Then a new table is created with fields that have been predefined by me. Then using another form the user can make changes to they new table.

What I need help with is how I can use a form to create the table.

I hope what I am asking was a little more clear than mud, lol.

Thanks in advance for any help!

11 replies

by witakr

No, I am doing this for free. I am familiar with vb coding and SQL server. I am just fairly unfamiliar with access. I can handle the problem I just can't figure out how I would go about it.

by santeewelding

Doesn't quite make sense. But, stand by. There are those here who may swarm you.

by witakr

I'll maintain this holding pattern...

Thanks lol

by santeewelding

No thumb?

You think my egregious comments come forth for nothing?

You...you, impetuous, ingratitudinous, dissolute youth.

by tony hopkinson

'Easiest' way is to build a create table query from from the input and then execute it.

A table of allowable fields, Another table to hold the new table structure. Types and nullability, keys, indexes, auto increment. Basically you are redesign the access IDE.

Personally if I gad to do something like this, and I'd avoid it like the plague.

I'd create a table will all the predefined fields, have another column the hold a userTable ID. Then I'd have another table for holding a configuration, ie which fields of the predefined ones are used.

Another way to go is a property bag approach

There you craete a table to hold the predefined fields (Id, Name, Type) etc

And your data table then becomes

TableID,KeyID,FieldID,FieldValue

It's a complex tip whichever way you look at it, even in a full DBMS, it's not an option for the faint hearted.

by witakr

I have to track ammunition by brand and caliber/type.

For example:

Brand: Federal
Cal/Type: .40S&W (Hollow Point)

In my plan, the brand name, (i.e. Federal) would be the table name. So if I have 5 brands, say:

"Federal", "Blazer", "Gold Dot", "Remington" and "Winchester"

then I would have 5 tables named:

"Federal", "Blazer", "Gold Dot", "Remington" and "Winchester"

In each table there would be 2 fields

"Caliber/Type" and "Quantity"

When the user wants to add a 6th Brand then they would use a form which I create for that purpose. The form, when used and submitted, will create a new table with the brand name as the table name with the fields "Caliber/Type" and "Quantity" and then the user would use a separate form to enter the quantities.

My problem is I have several brand names to track and for each brand there is several caliber/types and for each caliber/type there is a quantity. If I could track all this in one table effectively then I would love to but I just can't figure out how to do it with out making the table ridiculously difficult to manage.

Do you know how I could track this data in a single table? I can't figure out how to do it.

I hope I didn't confuse you....

by tony hopkinson

Table called brands,
BrandID (key), Name, ...

Have a Calibres table

CalibreID (key), Description etc

then another Quantities

(BrandID,CalibreID) key, Quantity

For exmples hit Help or google on

There;'s probably some mileage in a making calibres dependant on brand as well.

That way you can filetr out calibres that belong to different brands.

Hit F1 in access for
Look up tables
and
Master/detail relationships

Or google, loads of examples on solving this need.

Example is more likely to be say Customer and Order or Name and Address, but it's the same idea.

This discussion is closed to new replies.