Creating Tables in VB

[jamsta]

Hi All,

I have some code in a sub:

On Error Resume Next
DoCmd.RunSQL "DROP TABLE tblParcels" 'This deletes the table if it already exists.

Dim tblParcels As TableDef 'Create the table
Set tblParcels = db.CreateTableDef("tblParcels") 'Define the table

Dim fldURN As Field 'Create each of the fields
Dim fldSBI As Field
Dim fldTransSBI As Field
Dim fldWorkCat As Field
Dim fldException As Field
Dim fldLeaseEndDate As Field
Dim fldParcel As Field
Dim fldVFD As Field
Dim fldID As Field

Set fldURN = tblParcels.CreateField("URN", dbText, 10) 'Define each of the fields
Set fldSBI = tblParcels.CreateField("SBI", dbText, 9)
Set fldTransSBI = tblParcels.CreateField("TransSBI", dbText, 9)
Set fldLeaseEndDate = tblParcels.CreateField("LeaseEndDate", dbDate, 10)
Set fldParcel = tblParcels.CreateField("Parcel", dbText, 50)
Set fldVFD = tblParcels.CreateField("VFD", dbText, 12)
Set fldID = tblParcels.CreateField("ID", dbLong)
Set fldWorkCat = tblParcels.CreateField("WorkCat", dbText, 50)
Set fldException = tblParcels.CreateField("Exception", dbText, 50)

tblParcels.Fields.Append fldID
tblParcels.Fields.Append fldParcel 'Add each field to the table
tblParcels.Fields.Append fldVFD
tblParcels.Fields.Append fldURN
tblParcels.Fields.Append fldSBI
tblParcels.Fields.Append fldTransSBI
tblParcels.Fields.Append fldLeaseEndDate
tblParcels.Fields.Append fldWorkCat
tblParcels.Fields.Append fldException

db.TableDefs.Append tblParcels 'Add the table to the database

db is the current database, and this code works fine, in that it creates a table called tblParcels with all the fields named above.

However, I'm trying to use what seems like exactly the same logic to create a different table in another sub, and the table isn't being created:

On Error Resume Next
DoCmd.RunSQL "DROP TABLE tblNonRLE" 'This deletes the table if it already exists.

Dim tblNonRLE As TableDef 'Create the table
Set tblNonRLE = db.CreateTableDef("tblNonRLE") 'Define the table

Dim fldID As Field
Dim fldTemplate As Field 'Create each of the fields
Dim fldURN As Field
Dim fldSBI As Field
Dim fldSheetRef As Field
Dim fldParcelRef As Field
Dim fldArea As Field

Set fldID = tblNonRLE.CreateField("ID", dbLong)
Set fldTemplate = tblNonRLE.CreateField("Template", dbText, 6)
Set fldURN = tblNonRLE.CreateField("URN", dbText, 10)
Set fldSBI = tblNonRLE.CreateField("SBI", dbText, 9)
Set fldSheetRef = tblNonRLE.CreateField("SheetRef", dbText, 6)
Set fldParcelRef = tblNonRLE.CreateField("ParcelRef", dbText, 4)
Set fldArea = tblNonRLE.CreateField("Template", dbText, 8)

tblNonRLE.Fields.Append fldID
tblNonRLE.Fields.Append fldTemplate
tblNonRLE.Fields.Append fldURN
tblNonRLE.Fields.Append fldSBI
tblNonRLE.Fields.Append fldSheetRef
tblNonRLE.Fields.Append fldParcelRef
tblNonRLE.Fields.Append fldArea

db.TableDefs.Append tblNonRLE 'Add the table to the database

I don't get an error message, but the table doesn't get created. This is my third post in the week since I started playing with VB - and I know I may be missing something silly, but you've been helpful so far so any thoughts would be welcome!

thanks

 

[ChrisO]

Try commenting out the line: -

On Error Resume Next

You may be getting an error which could skip the Append.

But why not simply do: -

CurrentDb.Execute "Delete * From tblNonRLE", dbFailOnError

 

[DCrake]

Why are you deleting and recreating tables? Surely it is the data within that is different not the structure. You would not thow away a filing cabinet then buy a new one everytime you wanted to change what was in it.

David

 

[jamsta]

Thanks for the advice.
Why am I doing it this way? It's a good question. Really I'm just experimenting, getting the code to create tables from scratch. I like the filing cabinet analogy by the way!

 

[ChrisO]

I think most people around here like the idea of experimenting with code, I know I do.

But I also think it advisable to say that to people when you post because it puts a different slant on things and you may get many different answers.

 

[DJkarl]

If you are just experimenting with code how about a different and in my opinion much easier way to create a table, use SQL.

CREATE TABLE [tblNonRLE] ([ID] LONG,[Template] TEXT(6),[URN] TEXT(10),[SBI] TEXT(9),[SheetRef] TEXT(6),[ParcelRef] TEXT(4),[Template] TEXT(8))

After your DROP TABLE statement just execute the SQL above and bam, you've got your table.