Question

Locked

Access 2007: Creating tables using a form...

By witakr ·
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!

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Is this for pay?

by santeewelding In reply to Access 2007: Creating tab ...

If it is, you are in over your head.

Collapse -

No

by witakr In reply to Is this for pay?

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.

Collapse -

Your last sentence

by santeewelding In reply to No

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

Collapse -

Standing by...

by witakr In reply to Your last sentence

I'll maintain this holding pattern...

Thanks lol

Collapse -

What?

by santeewelding In reply to Standing by...

No thumb?

You think my egregious comments come forth for nothing?

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

Collapse -

You are...

by witakr In reply to What?

...an interesting person.

Collapse -

Well it's technically possible, not advisable at all though

by Tony Hopkinson In reply to Access 2007: Creating tab ...

'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.

Collapse -

Well, heres what I have to do...

by witakr In reply to Well it's technically pos ...

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....

Collapse -

Erm just make brand a key...

by Tony Hopkinson In reply to Well, heres what I have t ...

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.

Collapse -

Texas

by santeewelding In reply to Well, heres what I have t ...

That explains everything, including the pistol rounds.

Back to Software Forum
10 total posts (Page 1 of 1)  

Related Forums