Visual Basic - Create Table

[Nelson The Missing Lead]

Hi,

I trying to create a table using visual basic, when i try to run it. It
prompt me "No fields defined, can't append Table or Index. Anyone, could
highlight to me what is the problem. My code is as follows:

Public Sub PCreateTable()

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fldTradeDate As DAO.Field
Dim fldStockName As DAO.Field
Dim fldRemarks As DAO.Field
Dim fldCurrency As DAO.Field
Dim fldClose As DAO.Field
Dim fldChange As DAO.Field
Dim fldVolume As DAO.Field
Dim fldHigh As DAO.Field
Dim fldLow As DAO.Field
Dim fldValue As DAO.Field
Dim fldDaysAvg14 As DAO.Field

Set dbs = CurrentDb
On Error Resume Next

'If the table already exists, delete it
dbs.TableDefs.Delete "SGX Individual Historical"

On Error GoTo 0

'Create the table definition in memory
Set tdf = dbs.CreateTableDef("SGX Individual Historical")

Set idx = tdf.CreateIndex("ID")
idx.Primary = True
idx.Required = True
idx.Unique = True

Set fldTradeDate = tdf.CreateField("Trade Date", dbDate)
Set fldStockName = tdf.CreateField("Stock Name", dbText, 50)
Set fldRemarks = tdf.CreateField("Remarks", dbText, 5)
Set fldCurrency = tdf.CreateField("Currency", dbText, 5)
Set fldClose = tdf.CreateField("Close", dbDouble, 3)
Set fldChange = tdf.CreateField("Change", dbDouble, 3)
Set fldVolume = tdf.CreateField("Volume", dbLong)
Set fldHigh = tdf.CreateField("High", dbDouble, 3)
Set fldLow = tdf.CreateField("Low", dbDouble, 3)
Set fldValue = tdf.CreateField("Value", dbLong)
Set fldDaysAvg14 = tdf.CreateField("DaysAvg14", dbDouble, 3)

'Append the index to the Indexes Collection
tdf.Indexes.Append idx

'Append the fields to the TableDef's Fields Collection
tdf.Fields.Append fldTradeDate
tdf.Fields.Append fldStockName
tdf.Fields.Append fldRemarks
tdf.Fields.Append fldCurrency
tdf.Fields.Append fldClose
tdf.Fields.Append fldChange
tdf.Fields.Append fldVolume
tdf.Fields.Append fldHigh
tdf.Fields.Append fldLow
tdf.Fields.Append fldValue
tdf.Fields.Append fldDaysAvg14

'Append the TableDef to the Database's TableDefs collection
dbs.TableDefs.Append tdf

'Refresh the Indexes collection
tdf.Indexes.Refresh

'Refresh the TableDefs collection
dbs.TableDefs.Refresh

Set idx = Nothing
Set fldTradeDate = Nothing
Set fldStockName = Nothing
Set fldRemarks = Nothing
Set fldCurrency = Nothing
Set fldClose = Nothing
Set fldChange = Nothing
Set fldVolume = Nothing
Set fldHigh = Nothing
Set fldLow = Nothing
Set fldValue = Nothing
Set fldDaysAvg14 = Nothing
Set tdf = Nothing
Set dbs = Nothing

End Sub

Thank you.

Nelson Chou

 

[Dale Fye]

Nelson,

I have not done this in a while but I have a couple of suggestions.

1. You never define the ID field, create it and append it before you create
the index on it. Honestly, I don't remember the field type to create the ID
field as an autonumber field, it might be "Counter" or something like that.
Then move the code that appends the index to the tabledef to the next line
after "idx unique = True".

2. I would put the tdf.Fields.append line right behind where you create it.
This is not a big deal, but would make the code easier to read.

Dale

 

[Allen Browne]

Here's an example of how to create a table using DAO:
http://allenbrowne.com/func-DAO.html#CreateTableDAO

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

"Nelson The Missing Lead" <[email protected]>
wrote in message

 

[Marshall Barton]

You need to create and append the fields to the table before
creating the index.

You never added any fields to the Index's Fields collection.

 

[John W. Vinson]

I trying to create a table using visual basic, when i try to run it. It
prompt me "No fields defined, can't append Table or Index. Anyone, could
highlight to me what is the problem. My code is as follows:

If you'll be doing this routinely, you may want to consider having an empty
"template" table with all the desired field types, sizes, indexes, etc. and
just use TransferDatabase to make a copy of it.

I'd actually have to question WHY you need to routinely delete and create
tables. What is the table being used for, and can that goal not be attained
using a Select Query?

John W. Vinson [MVP]

 

[Nelson The Missing Lead]

Hi John,

The reason why i need routinely delete the table is: I have another
procedure to calculate the 14days average of a particular stock which i
stored in the above table. If I never delete the table, the index number will
continue from the last store data even i have deleted the fields data. This
will create a error as i have previously posted on the discussion group.

Thank.

Nelson Chou

 

[John W. Vinson]

Hi John,

The reason why i need routinely delete the table is: I have another
procedure to calculate the 14days average of a particular stock which i
stored in the above table. If I never delete the table, the index number will
continue from the last store data even i have deleted the fields data. This
will create a error as i have previously posted on the discussion group.

I still must disagree here. If you can select the items which need to be
averaged in a MakeTable query, you can select those same items in a Select
query - with less hassle and less overhead! It sounds like your code is
dependent on the value of an Autonumber, which is never a good idea. I didn't
follow the previous discussion so I may be off base here... but I will assert
that creating a new table is NOT required in order to get a 14-day running
average of any data.

John W. Vinson [MVP]

 

[DAVID]

Well, that's the wrong approach, but if you
are wedded to it, use a create table query
to copy a blank template table to create
your new table. If you can't make the create
table query do exactly what you want, use
code just to fix up the differences.

(david)