Change data type using query expression

[cenderawasih]

Hi...

Can i change the data type of a field using a query expression? If can then how to do it? I have a field with a text datatype (in access table) to be change to numeric datatype so that it can link to another field in another table (not an access table)where the datatype is numeric.

 

[Aubs010]

Cenderawasih,

Don't know if this helps at all, but it's from the VB help file:

Type Property (Column)
      

Indicates the data type of a column.

Settings and Return Values

Sets or returns a Long value that can be one of the DataTypeEnum constants. The default value is adVarWChar.

Remarks

This property is read/write until the Column object is appended to a collection or to another object, after which it is read-only.

Hope it helps

Aubs

 

[cenderawasih]

Thanks for the info....but how do i set the column type?

 

[Aubs010]

Sorry, never done it myself before, just looked it up when I saw your post!!

Does this make any more sense? (Not read it properly myself!):

ParentCatalog Property Example (VB)


The following code demonstrates how to use the ParentCatalog property to access a provider-specific property prior to appending a table to a catalog. The property is AutoIncrement, which creates an AutoIncrement field in a Microsoft Jet database.

Sub CreateAutoIncrColumn()

    Dim cnn As New ADODB.Connection
    Dim cat As New ADOX.Catalog
    Dim tbl As New ADOX.Table

    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source= c:\Program Files\" & _
        "Microsoft Office\Office\Samples\Northwind.mdb;"
    Set cat.ActiveConnection = cnn

    With tbl
        .Name = "MyContacts"
        Set .ParentCatalog = cat
        ' Create fields and append them to the new Table object.
        .Columns.Append "ContactId", adInteger
        ' Make the ContactId column and auto incrementing column
        .Columns("ContactId").Properties("AutoIncrement") = True
        .Columns.Append "CustomerID", adVarWChar
        .Columns.Append "FirstName", adVarWChar
        .Columns.Append "LastName", adVarWChar
        .Columns.Append "Phone", adVarWChar, 20
        .Columns.Append "Notes", adLongVarWChar
    End With

    cat.Tables.Append tbl

    Set cat = Nothing

End Sub

Aubs

 

[cenderawasih]

The system doesn't seem to recognized ADOX...i tried to add the VBA referance but still doesn't work.....

 

[Aubs010]

Sorry, looking back, I would doubt if that would do anything!

As a work around, I think you could probably do a MakeTable query to create a new table with the new data types and import the date you currently hold...

I found this on another forum which I think would answer your question successfully:


You can change the datatype of a table field from a query or running SQL from vb.

create a query with the SQL of

ALTER TABLE [Your Table Here] ALTER COLUMN [Your Field Here] Int;

Changes the [Your Field Here] field to integer.

Replace the 'int' with 'Text', 'Date', 'Memo','Money' etc depending on what you want.

Hope it helps



Aubs

 

[Aubs010]

Oops, should have given credit to the author of the detail I supplied. Found it on Google, and it's a link to experts-exchange. Couldn't get it directly from experts-exchange as the web page kept hanging.

The link (via google cache) is:

http://www.google.co.uk/search?q=ca...+Access+Change+Table+Data+Type&hl=en&ie=UTF-8

and the original link is:

http://beta.experts-exchange.com/Databases/MS_Access/Q_20663657.html

hth



Aubs