Using DDL and still getting goofy Results - Access 2010 1

[MacroAlan]

I am transferring an Excel spreadsheet into Access 2010. Though I am defining the Temp table and the offending field as Text, the import is throwing out the data and only keeping the numeric Customer #s. Most of the numbers are numbers but one occassionaly begins with an alpha character.

   Dim strSQL          As String
    DoCmd.DeleteObject acTable, "tmpTrax"
    strSQL = "CREATE TABLE tmpTRAX ([CustomerNo] varchar(15),Customer varchar(15),[Project #] DOUBLE,[Project Title] VARCHAR(30),[SCR #] DOUBLE,Title VARCHAR(30),State varchar(15),"
    strSQL = strSQL & " [Employee ID] DOUBLE,Employee VARCHAR(30),Role VARCHAR(30), [Work Date] DATE,Hours DOUBLE,Notes MEMO, "
    strSQL = strSQL & " Approved varchar(15), [Submit Date] DATE,[Billable Flag] VARCHAR(10),[Billable Comment] MEMO,[Project Owner] VARCHAR(30),[Decision Date] DATE,"
    strSQL = strSQL & " [Billable Status Decided] VARCHAR(30),[Project Type] VARCHAR(30));"
    DoCmd.RunSQL strSQL
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "tmpTRAX", GetOpenFile, True

The table looks correct after creation and even when the records are brought in, it still says Text. Simply throws out records.


Alan

 

[PHV]

Format the relevant columns to text ("@") in Excel before the transfer.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MacroAlan]

That works. Problem for future is that I may not have control of Excel file before import. I built a “brute force” recordset to fix after the fact.

Thanks again


Alan