Numbers, text and null all in one field, linked from Excel

[Big Pat]

Hi,

I know you can't store text in a numeric field but I always thought you could store numbers in a text field - provided you didn't need to do any calculations on them. My problem is as follows:

I receive an Excel 2003 spreadsheet once a month, which I save to a specific filename/location overwriting the previous file. My Access 2003 database uses this as a linked table and (among other things) runs an append query to add the new data onto an existing table.

We have now added a new column called Reference in the spreadsheet. Often, this will be empty, but it could contain numbers or text. This is the first month I have received it and most entries are blank (including the first row) but further down there are some numeric values.

So I added a new Reference field to my main table and set it to text. Then I amended the append query to include the new field. But when I run it I get the error "Numeric field overflow". If I take that column back out of the query, it runs fine, so that's definitely the offending data. And when I open the linked table in Excel and scroll down to where I should see the reference numbers, I see #Num! So it looks to me like it doesn't recognise numbers as text.


Things I've already tried
In Excel, I formatted all the Reference cells as text.
That didn't work, so next I added a dummy record at the top of the Excel file (just under the headings), with zeroes in the numeric columns and 'X's in the text columns including Reference.

But that doesn't work either. Given the above circumstances, what's the best way to proceed with this?

Thanks

 

[StarGrabber]

Hi Big Pat,

what's the best way to proceed with this? I don't know.

But I found a solution using VBA that works at least:

Private Sub AppendExcelData()
On Error GoTo Err_Append

    Dim dbXLS As DAO.Database
    Set dbXLS = OpenDatabase("PathAndNameOfYourExcelFile", False, True, "Excel 12.0 Xml;HDR=Yes")
    
    Dim rs As DAO.Recordset
    Set rs = dbXLS.OpenRecordset("SELECT * FROM [table1$]", dbOpenSnapshot)

    rs.MoveFirst

    Do
        If Not IsNull(rs(0).Value) Then
            DoCmd.RunSQL "INSERT INTO tblTest ( FieldName1, FieldName2, FieldName3, FieldName4 ) " & _
                         "VALUES (" & rs(0).Value & ", " & rs(1).Value & ", " & rs(2).Value & ", " & rs(3).Value & ");"
        End If
        
        rs.MoveNext
    Loop While Not rs.EOF

    rs.Close
    dbXLS.Close
    
Exit_Append:
    Set rs = Nothing
    Set dbXLS = Nothing
    Exit Sub
    
Err_Append:
    MsgBox Err.Description
    Resume Exit_Append

End Sub

Please note that in this example all recordset values (i.e. your Excel data) are numeric (or of date type). In case of text values you have to use inverted commas in addition:

..."VALUES ('" & rs(0).Value & "', '" & rs(1).Value & "', '" & rs(2).Value & "', '" & rs(3).Value & "');"

 

[Big Pat]

Hi,

I wasn't able to get that working, though I'm sure it's my fault and not yours. Several of my columns are text and I suspect I'm getting the placement of commas wrong.

However, I tried this at home using Access 2007 with a brand new database and linked excel file and it worked. It just went ahead and inserted the "numbers" in the text-field as I expected it to.

One of our other offices in the same building already has Access 2007 (I don't because of the need to interface with a legacy system which requires Office 2003) and I went down and tried it on a PC there and it works fine.

Possibly just a glitch on this machine then, but i'm happy enough with that.

Thanks again!

 

[StarGrabber]

Hi,

the OpenDatabase method has the following syntax:

Expression.OpenDatabase(Name, Options, ReadOnly, Connect)

On your machine with Office 2003 you have to use 'Excel 8.0' instead of 'Excel 12.0 Xml' for the Connect parameter.

Good luck!