VBA To Change Field Type

[jo15765]

I have a spreadsheet that I am importing that keeps changing one of the field types to Number, when the field contains text. I need this process to be completely performed through VBA, so is there a VBA 'alter table' statement that I could change the field type from number to short text?

[CJ_London]

take a look at this link

http://www.w3schools.com/sql/sql_alter.asp

but the usual way round this it to append to an existing table rather than create a new one

[Micron]

Personally, I don't think you want to go down that road since (AFAIK) the issue is not with the data type of the destination field, but is related to the transport. This is a common problem with Excel/Access data transfers, and there is a theory it's affected by the data in the first 8 rows of the spreadsheet. If the first row contains headers, then who knows for sure? One way to ensure the data is transposed as text is to preface the spreadsheet cell data with the apostrophe (') to ensure it is recognized by Access as text. This might be accomplished via a spreadsheet macro. Other than that, the only other viable way I know of would be to use Automation and import the data cell by cell, coercing the problem fields to text during the transfer. I'd favor the apostrophe method, methinks. Perhaps others here will have a better idea.

[jo15765]

I tried to run

dbs.Execute "ALTER TABLE ____Test ALTER COLUMN Blue-Green-Red12 Text(50);"

But the field does not alter, and no error is thrown?

[CJ_London]

there is a theory it's affected by the data in the first 8 rows of the spreadsheet. If the first row contains headers, then who knows for sure?

Not a theory - and I think it is 16 rows - if headers are set to true, the first row is ignored. The problem can be what you see in excel is often formatted, whereas what is imported is the actual value - formatting a number with 6 decimal places to 2 decimal places does not mean it will import 2 decimal places, formatting as text will not make it text, formatting with preceding zeros will not make it text.

You can do as micron suggests and prefix with a ', or you can prefix the 'text' number with a 0 - really depends on what you numbers are

Another alternative is to save the file as a excel .csv file. Then you would use transfertext to import and can use the import specification to specify the column should be imported as text.

[CJ_London]

chances are it's because you have non alphanumeric characters in your field name - so access is treating the hyphen as a minus sign. Just a wild guess, but it might be the reason your text gets converted to a number

better to remove the hyphens because you will get problems in the future, but in the short term, try putting square brackets around the field name

[jo15765]

@Ajax - adding the brackets solved my issue, thank you.

[CJ_London]

don't forget to change the field names going forward, otherwise you will be back here with more weird errors in the future