Creating Access tables from metadata worksheet 1

[tommyj55]

I have an Excel spreadsheet that contains database definitions. Each row includes 5 colums: Table Name, Field Name, Field Type, Length, and Nulls Alowed (1/0).

I'm looking for input to build a VBA script that will use this metadata from my spreadsheet to generate the actual MS Access tables.

Yes, I could manually generate the tables, but there are over 300 tables with over 3200 fields.

Any input would be greatly appreciated.

I'm more of an engineer than a programmer.

Thanks in advance!

 

[ettienne]

You can use DAO to create an Access database and tables.
Include a reference to Microsoft DAO 3.x Object Library. (Tools, References in VBE)

To create a database use:

Dim strSQL as String
Dim db As DAO.Database
Set db = DAO.CreateDatabase("C:\MyDatabase.mdb", dbLangGeneral)

'To create a table:
strSQL = "CREATE TABLE MyTable (Field1 CHAR(10), Field2 DOUBLE, Field3 INTEGER);"
db.Execute strSQL

db.Close

Set db=Nothing


You just need to figure out how to read your spreadsheet info and translate that to SQL queries to create the tables.