Code to Create a Table

[TomJinCA]

Just need code to create a new access table in VBA. Searched Technet & couldn't find.

Thank You ;-)

Tom J

 

[simarik]

Simply use DAO's ability to programmatically create tables dynamically.

Dim dB as Database
Dim tableName as TableDef
Dim field1 as Field

Set dB = CurrentDb()
Set tableName = dB.CreateTableDef()
tableName.Name = "name"

' create a text type field with a length of 15
Set field1 = name.CreateField("field_name", dbtxt, 15)

' Append the field to the table first
name.Fields.Append field1

' then append the table to the tables collection
' otherwise, your table will not be stored permanently
With dB.TableDefs
.Append name
' refresh in case of multi-user environment
.Refresh
' update entire db window for immediately reflecting changes
.RefreshDatabaseWindow
End With

Narayanan Srinivasan
coolie91@hotmail.com

Brothers and Sisters from India, if you have any recent news regarding F-1 to H-1B visa transfer, please e-mail me. Thanks.

 

[braindead2]

something like this!!
dim db as database, tdfnew as tabledef
set dbs = opendatabase("your.mdb&quot
set tdfnew = db.createtabledef ("yourtable&quot
with tdfnew
.fields.append.createfield ("Lastname",dbtext)
.fields.append.createfield ("firstname",dbtext)

db.tabledefs.append tdfnew
end with
db.close

 

[simarik]

i have used db.close before and it does not generate an error, but since you are actually not opening a new database (it's already opened for you), it's a technical mistake. Access probably ignores it.

Narayanan Srinivasan
coolie91@hotmail.com

Brothers and Sisters from India, if you have any recent news regarding F-1 to H-1B visa transfer, please e-mail me. Thanks.

 

[simarik]

pardon me, in your code, it makes sense.

Narayanan Srinivasan
coolie91@hotmail.com

Brothers and Sisters from India, if you have any recent news regarding F-1 to H-1B visa transfer, please e-mail me. Thanks.

 

[braindead2]

simarik,
we posted at about the same time. Your example is much better then my post. It gives a detailed description, since I wrote mine on the fly it is untested. I see several errors that tells me it probably won't work with out debugging.

 

[TomJinCA]

Thank you both. I made a few changes to the Narayanan's code and it worked. Here's the one that worked for me:

Sub CreateTable()
Dim dB As Database
Dim tableName As TableDef
Dim field1 As Field

Set dB = CurrentDb()
Set tableName = dB.CreateTableDef()
tableName.Name = "MyTable"

' create a text type field with a length of 15
Set field1 = tableName.CreateField("First_Name", dbText, 15) ' had to change to tablename
Set field2 = tableName.CreateField("Last_Name", dbText, 15)

' Append the field to the table first
tableName.Fields.Append field1
tableName.Fields.Append field2

' then append the table to the tables collection
' otherwise, your table will not be stored permanently
With dB.TableDefs
.Append tableName
' refresh in case of multi-user environment
.Refresh
' update entire db window for immediately reflecting changes
' .RefreshDatabaseWindow Didn't like this!!
End With


End Sub


Thanks again

 

[MichaelRed]

Tom,

This is "O.K." as an example, but you really should add some error checknig if you are going to use it in a production process. Also, it lacks the setting of indicies for the table. You can also include the description(s) for the table and the fields, but they require additional 'work'.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[TomJinCA]

Thanks Michael, I agree with you completely. Just was looking for a sample to at least get started.

Thanks again for your comments

 

[NRK]

Is there a way to ask about the table name before you create it?