New table with VBA

[Cass]

Hi!

I need some basics/example how generate table into VBA
problem is how define field value and when 1 record data is OK then whats next? GoToRecord, acNewRec?

[Cosmos75]

Are you trying to create a new table or add records to an existing table?

[Cass]

Both is good to know how it works ;)
Firstly I may great table manually and add then records to the table :*)

[Cosmos75]

Here is a simple example of creating a table using DAO. Be sure that your DAO reference is set.
Sub CreateNewTblDef()

Dim db As DAO.Database
Dim tdf As DAO.TableDef

Set db = CurrentDb()

' Create a new TableDef object
Set tdfNew = db.CreateTableDef("tblNew")

With tdfNew
'Create fields and append (i.e. add) them to the new TableDef object.
.Fields.Append .CreateField("strText", dbText)
.Fields.Append .CreateField("memMemo", dbMemo)
.Fields.Append .CreateField("lngLong", dbLong)
.Fields.Append .CreateField("dblDouble", dbDouble)
.Fields.Append .CreateField("blnBoolean", dbBoolean)
End With

'Append the new table only after adding the fields
db.TableDefs.Append tdfNew
db.Close

End Sub
You can also use SQL to create a table. I've got an example here (http://accessdb.info/index.php?option=com_content&task=view&id=25&Itemid=43).

If you wanted to you could use DAO to execute that SQL or even DoCmd.RunSQL but I think the example above is the best way to do it in code.

I've also got some simple code (DAO and ADO) to manipulating recordsets, which you will need to know how to do in order to add new records to a table (see here (http://accessdb.info/index.php?option=com_content&task=view&id=56&Itemid=45)). Again, you could use an SQL statement (i.e. INSERT INTO) from VBA but I think using a DAO recordset is easier to code an understand.

Be sure that you execute the .Update statement after adding a new record (using. AddNew) and creating the values for the fields. Remember if you use the field number, that .Field is 0 indexed; i.e. the first fields is .Field(0) not .Field(1).

Hope that helps!