change datatype programmatically

[DavidWRouse]

I have imported an access table. I need to change a text field to a number field before my existing queries will work.

I need to do this programmatically as it will be done quite often from a menu.

I has scanned the Forums, with no luck.

Has anyone come across this before, and can help.

 

[Shadez]

What exactly are you changing, Give examples


1) from what?


2) to what?


3) do you need to do this in real time or is the a one off process?





ps, you might want to look at the VBA type conversion functions ie

Clng
Cstr
Cint
Cbool
CDbl


ShadeZ

 

[DavidWRouse]

I need to do it once, when the table is Imported.

the text field holds 6 figure numbers.

If I can convert the DataType to Number then it will link to an existing table in my DataBase that has the same field but as a number

I will have a look at the Functions you Mentioned.

But I am still intersted in Changing it by programming.

 

[Shadez]

Changing the datatype of an existing field is a bit tricky, you need to use the sql function

"Alter Table"

look it up in the help, i dont know if it will work as i have neither tried it.

using Clng and / or "Alter Table" you should be able to do every thing you need.

 

[Mile-O]

Another idea would be to create a new field programmatically, transfer all the data from the old field into it (by applying the correct conversion function) and then deleting the old field.

Just off my head, won't be perfect...

    Dim db As DAO.Database
    Dim td As DAO.TableDef
    Dim rs As DAO.Recordset
    
    Set db = CurrentDb
    
    Set td = db.TableDefs("MyTable")
    
    td.Fields.Append .CreateField("NewField", dbInteger)
    db.TableDefs.Append td

    Set rs = db.OpenRecordset("MyTable")
    
    With rs
        Do While Not .EOF
            .Edit
            .Fields("NewField") = CInt(.Fields("OldField"))
            .Update
            .MoveNext
        Loop
        .Close
    End With
    
    td.Fields.Delete "OldField"

 

[The_Doc_Man]

For that matter, pre-define the final table that will link up to your data with the fields intact.

Import your data as-is.

Then create an append query that adds the data from the imported table to the final table. For the oddball column, use one of the conversion functions such as CDate, CCur, CStr, CLng, etc.

You could do this in a Macro.

If you need to pre-erase the contents of the final table before you do the append query, you can write a delete query that your macro could also run (in the correct sequence, of course...)

Then, you could do an erase query on the imported table, which would allow you to later reclaim that space. (NOTE: When churning tables this way, you will need to compress your DB more often to do the actual reclamation step.)

And you could turn off warnings in the macro while you are doing all of these steps, then restore them to normal when done.

 

[DavidWRouse]

Thanks, That has been a great help.
I will get down and see which best suits me