I'm trying to import data into my DB from an Excel SS or a CSV file. Here's the code I'm using:
FilePath = "C:\Users\residents.csv"
'DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel7, "TblNm_SS", FilePath, True
DoCmd.TransferText acLinkDelim, , "TblNm_SS", FilePath, True
Set InRS = DB.OpenRecordset("TblNm_SS")
Set OutRS = DB.OpenRecordset("PermTbl")
If InRS.EOF = False Then
Do Until InRS.EOF = True
I = 0
With OutRS 'the input is an empty string write it
For I = 0 To InRS.Fields.Count - 1
.Fields(I).Value = CStr(Nz(InRS.Fields(I).Value, ""))
The problem is that I have a zip column that a few of the records have a few zip+4 like "44081-1234"
which when viewing the linked table comes up as #Num and the properties say it is a long integer in the linked table.
The data set is small enough that I can just copy and paste to the staging table. But does anyone have any suggestions to get around this if I want to automate the import process?