Hello all, this is my 1st post
Who can help me to automatically create tables by using a table. I have a table where i store records, I want these records to be the fields of another table B..

How can i automate this process without manually create the tables.

Jinane

Hi

Put the fields from the first table in a query, make the query a "Make Table" query, specify the new table name as "Table B".

If you run a make table query twice to the same table name, the original table will be overwritten.

I usually run a make table query once, and then change the query to an append query if I want to keep adding data to it.

HTH

Why would you have 2 tables with the same fields in?

This goes against normalisation (i.e setting your tables up right)

What are you trying to achieve by doin this?

First, let's make sure we understand what you are asking.

You have a table with each record representing one field of the table you want to create. So you have fields in the original table that specify the name and data type of the each field you want to have in the new table. Is that on the right track?

Glenn

thx every1,
Gelnn u r right,
i have 6 or 7 records in the table A..
i want 2 create a table B- (the name is not fix it's dependant of texts in a formB) where the nb of fields is = to 6 or 7 (depepnds on nb of recs of table A) + 3 fields that i determine .. those 6 or 7 recs which will mean to the table B the fields have a fix lengh type integer.
hope the idea is clear .. i'm sorry i've changed my mind about the names..
well but i want the rec1 of table A to correspond to the field nb 2 in table B
& the rec2 of Table A 2 correspond to the field nb 3 in table B ..extra ...

confusing, no .. but it's soooooo important.
an example will be perfect.

To automate the process, you will need to develop a VBA procedure that reads the data from the table where you have the field definitions. There are then a couple of approaches that you can use to create the new table. The DAO library has a CreateTableDef and related methods to do the job or you can execute an SQL data definition query. If you haven't yet done any work in vba, this example would not be my first choice as an introduction to vba. So before we proceed with how to do it, it will help to know how proficient in VBA you presently consider yourself.

Glenn

thx Glenn,
i'm beginner in VBA yet i've made some of my own modules like converting numbers into arabic string, & others..
so pls can u help with a sample ??

Jinan

The solution is a bit too comprehensive to cover off in a simple example. Unfortunately the time pressure of my own current projects prevents me from developing an in depth sample for you. Beside that wouldn't help you learn and understand what you are doing. So what I suggest you do is start by learning how to work with a recordset (open, close, and navigate). Because you are using Access 2000 you can choose either DAO recordsets or ADO recordsets. The general approach in either model is the same but there are some specific implementation differences. For now choose one of the models and focus on learning its basics.

While the two models are roughly equivalent, if you are planning on staying with standard mdbs for the near future, I suggest that you go with DAO for now. Ultimately you will want to learn ADO as well.

So read up a little on DAO, in the Access VBA help documentation, here on UA, and in whatever other resources you have at hand. Then try to implment a few samples in your own simple database. When you run into a snag, UA members are here to talk you over the hump if your post your problem of the moment.

Glenn

Hello, here's an example of using ADO to create a table from a data source:

http://www.utteraccess.com/forums/showthre...p?Number=418108

Note, the data source is an Excel Spreadsheet, not an Access table, but that's a minor adjustment.

Thanx Glenn, Nate
but pls can some1 adapt it from excel to access text boxes in forms ?
& do i need anything to add it to my access in order 2 let the ADO works (or DAO)?

Jinane

I will reexplain it:
how can i convert from lines 2 columns ????
Let's say in Table1 :
IDField ... unimportant 2 me
Field1 ... from this field i want to count the NUMBER of records & create a new table B which the nb of fields must be = 2 the nb of records in A (Type INTEGER , 2 decimals) + 2 records 1st text (&KEY) & second text (could be null)..
Well, & the name of the table B, would be the combination of 2 textboxes in a form F.

pls a sample DB!!!!!