ACCESS VBA - Alter table append query

[RCBricker]

I have a form which is used to import worksheets from a file. Each worksheet gets put into its own temp table and then the data is moved into its appropriate table with an append query. However, I am having an issue with one column in particular. When access brings the worksheet into the temp column, it assigns it a data type of number. This field almost never has data in it, but when it does it is two alpha characters. I need it to be data type 'short text'. I have tried the ALTER TABLE option under the docmd.execute and it is not working.

How do I change the data type of a given field in an ACCESS table?

514                 DoCmd.TransferSpreadsheet acImport, _
                        acSpreadsheetTypeExcel12, strTBLNAME, strFULLPATH, _
                        True, "IMPORT_NAME"

On Error Resume Next
515                 db.Execute "ALTER TABLE strtblname ALTER COLUMN [SCLIN] text"
On Error GoTo ErrCapture

 

[ranman256]

In the source table, Excel can get confused about the datatype so it 'guesses', (and does so wrong)
so to fix, I have code to change EVERY cell in the 'confused' column to text.
This way it imports correctly every time.

Sub Cvt2Text()
range("B2").select
While ActiveCell.row <= 155
  if activecell.text <>"" then  ActiveCell.text= "'" & ActiveCell.text
   ActiveCell.Offset(1, 0).Select
Wend
End Sub

 

[Norie]

Try importing to existing tables which have the fields set up with the correct data type(s).