DAO - CreateTableDef help!!

[jtseltmann]

Can anyone see a problem with the following code? I have a function set up to create a table and defs from the header row of an ascii file. I have referenced MS DAO 3.6 Objects library and am running MS Access 2000. Any help would be greatly appreciated. I got the code tips from posts on this site...
'----------------------------------------------
Public Sub CreateTableAndDict(fields As Variant)
Const cRoutine = "CreateTableAndDict"
On Error GoTo ErrorHandler
'---
'Sub creates the temp_999 table/dictionary
'---
Dim db As database
Dim x As Integer
Dim thisfield As Field
Dim newtable As TableDef

Set db = CurrentDb()
Set newtable = db.CreateTableDef()
newtable.Name = "temp_999"

For x = LBound(fields) To UBound(fields)

Set thisfield = newtable.CreateField(fields(x), dbText, 15)
newtable.fields.Append thisfield

Next x

db.TableDefs.Append newtable

Exit Sub
ErrorHandler:
MsgBox Err.Number & " - " & Err.description, vbExclamation, "Routine - " & cRoutine
Err.Clear
End Sub

 

[PHV]

Replace this:
Dim thisfield As Field
By this:
Dim thisfield As DAO.Field

Anyway, what is your problem ? Any error ? Unexpected behaviour ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jtseltmann]

PHV,
Thank you! I was just about to post a comment that I forgot to note the error. When setting the 'thisfield' var it was giving me an invalid data type.

Your fix worked. May I ask why the post just show:
Dim field as Field and others seem to have worked?

Again, thanks to all on this site for always helping

Jeff

 

[PHV]

It's always better to declare full qualified objects.
Your problem was that the Field object is exposed by ADODB and DAO which is not the case for TableDef nor Database.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[jtseltmann]

It does. Thanks very much!

Problem solved!

 

[grmman]

can you replace dbtext with Variable.
Set thisfield = newtable.CreateField(fields(x), dbText, 15)

I have a table that has all the data I need to create a new table from my table has 2 col(fieldname and fieldtype)
for exmaple of the data:
account_number dbdouble

is there a way I can use the fieldtype to replace the dbtext in the createfiled line.

thanks

 

[PHV]

You may use something like this:
Set thisfield = newtable.CreateField(fields(x), someIntVar, 15)
provided the value of someIntVar is a valid DAO.DataTypeEnum

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grmman]

Is this how I should do it.

Dim ftype As DataTypeEnum

fieldname = rst.Fields!Question
ftype = rst.Fields!Type


Set thisfield = newtable.CreateField(fieldname, ftype, 15)

thanks for the help

 

[PHV]

Should work, but I wonder about the highighted 15:
Set thisfield = newtable.CreateField(fieldname, ftype, [highlight]15[/highlight])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grmman]

the field type is a string format. I think thats my problem.
the ftype is defined as a num.
How do I get around this one.

 

[PHV]

Either populate the Type field with correct values or use sort of Select Case instruction to convert it before calling the CreateField method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[grmman]

thanks for all the help I had to do this to get it to work

Select Case rst.Fields!Type
    Case "dbtext"
        Set thisfield = newtable.CreateField(fieldname, dbText)
    Case "dbDouble"
        Set thisfield = newtable.CreateField(fieldname, dbDouble)
    Case "dbdate"
        Set thisfield = newtable.CreateField(fieldname, dbDate)
    Case "dbdecimal"
        Set thisfield = newtable.CreateField(fieldname, dbDecimal)
    Case "DBMemo"
        Set thisfield = newtable.CreateField(fieldname, dbMemo)
    Case "dbCurrency"
        Set thisfield = newtable.CreateField(fieldname, dbCurrency)
    End Select