Creating tables dynamically

[mikeymay]

I need to create several tables that contain 30+ fields, each of which will have varying field settings (decimal places, text, dates, etc) and rather than manually creating each field, choosing its settings, labelling and so on, can I use some VBA in either Excel or Access to create them dynamically and quickly?

TIA

 

[skillilea]

We've done it. You need to set all the properties correct or you end up deleting a bunch of sheets.

Sub CreateStandardTable()
Dim ws As Worksheet
Dim tbl As ListObject
Dim tblRange As Range

Set ws = ActiveSheet ' usually a loop here

Set tblRange = ws.Range("A1:E1")

Set tbl = ws.ListObjects.Add(xlSrcRange, tblRange, , xlYes)

tbl.Name = "tblBrandNew"

tbl.ListColumns(1).Name = "Id"
tbl.ListColumns(2).Name = "Field1"
tbl.ListColumns(3).Name = "Field3"
tbl.ListColumns(4).Name = "Field4"
tbl.ListColumns(5).Name = "Field12"

tbl.ListRows.Add ' add so you have a databody

tbl.ListColumns(1).DataBodyRange.NumberFormat = "@"
tbl.ListColumns(2).DataBodyRange.NumberFormat = "0"
tbl.ListColumns(3).DataBodyRange.NumberFormat = "0.00"
tbl.ListColumns(4).DataBodyRange.NumberFormat = "mm/dd/yyyy"
tbl.ListColumns(5).DataBodyRange.NumberFormat = "General"


End Sub

 

[Micron]

This is an Access question, not Excel, no?

If you can find some ready made code that you can adapt, it's doable. To start from scratch might take longer than manually creating the tables. I'd be interested to know what these tables will look like because I suspect you'd end up with a case of a bunch of similar tables that are not normalized.

 

[Joe4]

We've done it. You need to set all the properties correct or you end up deleting a bunch of sheets.

Sub CreateStandardTable()
Dim ws As Worksheet
Dim tbl As ListObject
Dim tblRange As Range

Set ws = ActiveSheet ' usually a loop here

Set tblRange = ws.Range("A1:E1")

Set tbl = ws.ListObjects.Add(xlSrcRange, tblRange, , xlYes)

tbl.Name = "tblBrandNew"

tbl.ListColumns(1).Name = "Id"
tbl.ListColumns(2).Name = "Field1"
tbl.ListColumns(3).Name = "Field3"
tbl.ListColumns(4).Name = "Field4"
tbl.ListColumns(5).Name = "Field12"

tbl.ListRows.Add ' add so you have a databody

tbl.ListColumns(1).DataBodyRange.NumberFormat = "@"
tbl.ListColumns(2).DataBodyRange.NumberFormat = "0"
tbl.ListColumns(3).DataBodyRange.NumberFormat = "0.00"
tbl.ListColumns(4).DataBodyRange.NumberFormat = "mm/dd/yyyy"
tbl.ListColumns(5).DataBodyRange.NumberFormat = "General"


End Sub

Perhaps you did not realize that you were in the "Microsoft Access" forum and not the "Excel Questions" forum.
If you found this thread via the "Unanswered threads" listing, note that it tells the locations of each thread:

 

[Joe4]

This is an Access question, not Excel, no?

If you can find some ready made code that you can adapt, it's doable. To start from scratch might take longer than manually creating the tables. I'd be interested to know what these tables will look like because I suspect you'd end up with a case of a bunch of similar tables that are not normalized.

Yeah, I too was thinking it might be doable, but it would probably involve some high-level VBA code (not quick-and-easy beginner stuff).
As Micron said, it may take you longer to create and test the code than it would to set them up manually.

A few things to consider:
- Do any of these 30 tables have the same structure? If so, then you may be able to set one up, and copy that structure for the other tables that are structured the same way.
- If you are more comfortable doing it Excel, you can set-up the tables in Excel, and then link them into Access.

Also, are you familiar with the "Rules of Normalization"? These are vital when creating tables in databases.
If your tables are not normalized, it could make it very difficult to work with the data.
I would be interested if you really need 30 tables and all the fields in each table after they have been normalized.

 

[Micron]

Do any of these 30 tables have the same structure?

If they do, then there is most certainly a normalization problem - or there will be. Whenever I see comments like that posted, I always suspect it.

 

[Joe4]

If they do, then there is most certainly a normalization problem - or there will be. Whenever I see comments like that posted, I always suspect it.

Absolutely. I think is a distinct possibility.

 

[mikeymay]

Yes this was an Access question and not Excel.

I am building a Db that needs lots of tables with lots of unique fields per table so a bit of a grind to do manually.....

 

[mikeymay]

If they do, then there is most certainly a normalization problem - or there will be. Whenever I see comments like that posted, I always suspect it.

All the tables will contain their own unique fields.

Basically the Db is to hold information about property developments, the materials being used and the properties of the materials, e.g. amounts to be used, dimensions, wastage, etc.

Until I build each of the Db tables (which I am basing off an existing but very cluncky and not very well built Excel file) I won't know about any redundancies or duplications in the Excel file tables but the rule or normailsation make sense and lend themselves to building purely Excel based processes as well which I'm well versed with.

 

[Joe4]

Note that the redundancies we (and the Rules of Normalization) talk about are NOT data redundancies, but rather table/field redundancies.

For example, let's say that you had separate tables, one for each client, and they all had the exact same table fields.
That would be a "de-Normalized" structure. It instead should be one table, with a table field to indicate the client.

Or, let's say that you have one table, by similarly repeating type fields. Let's say that you had 12 revenue tables, one for each month, all with the same data fields.
That also would be a "de-Normalized" structure. It instead should be one table, with a table field to indicate each month.

One dead giveaway that you have a de-normalized structure is if you have to constantly add more data tables just to accommodate new data, when nothing basic has changed about the database, i.e. you need a new data table because you have a new client. If your database it designed well, you would be able to add new data (i.e. clients or months) without having to create new data tables which closely resemble the structure of existing tables that you already have now. In a well-designed database, you seldom have to create/add new data tables, unless something fundamentally changes about what your database is supposed to be doing. You should be able to create all the data tables you need right at the beginning in development, and not have need to constantly add new tables.