Access 2007 Paste Append problem

Are you speaking of an existing table with established fields and their data types? If so, what else would you expect? Surely you did not expect an alpha string to make it into a numeric column?

If you are speaking of creating the table on-the-fly with data pasted into it, then remember that Access only inspects the first couple of rows to decide on the data type. It may guess wrong. I would much rather either attach the Excel file as a table, or design the table first, then import the data.

If you use the Excel feature to save your data area as a table, then format your table columns to the desired type, it will help.  The table will have a name and you can import the named table into access with much better results, and quicker also. 

Here is a link to a series of youtube videos from an excel expert detailing how to properly setup excel data tables.http://www.youtube.com/user/ExcelIsFun#p/c/D8EEA7196D943ED5/13/V_SKpQVlIdE

I have had the same issue with a long established process after converting to Office 2010.  Access field is formatted text in an established table.  Was paste appending a column of mixed alpha and numerice results and Access 2003 used to convert all to text.  Now 2010 converts the numeric to text and deletes the actual text values.  This is an error and should not happen.  I worked around it by formatting all to text before pasting but Access should not be deleting fields with no warning or error message.  I have found many questions about this online as well.  Can't believe this has been going on since 2007 version. 

Why is it so hard to see a bug when it is screeming at you?

From Access 2007 you have the same problem as you have had in Excel for a long time.

If you format a column as text in Excel and then copy numbers into it, Excel change the format to general (if you have 0123 it will change to 123) . Even that you have told Excel that it should be text, you have to use the smart label to change it back to text.

If you create a table in Access and create a column with format as text, I guess you think it should stay as text after you have appended data into it?

If you have a column with both numbers and text and the first cirka 15 records are numbers, Access append all nubers in the column BUT leaves the records with text empty. And that without notice!

This is what Eweedon and KAccess have tried to explain.

If you don't beleive me, you can try it yourself. It takes two minutes.

I use both Excel and Access a lot to work with lots of data, and I am very sad to see how Access is inheriting the bad things from Excel.

PS. If I have at least four rows with text in the beginning of the column, Access append alla data as it should. DS.

Never mind the 'formating' as that is just display - not storage.  Open table in design view and see what the DataType is -- I expect you will find it is a number.

It looks like some think this is ok, and some seems not understand the problem.

I try to make it more detailed.

I create a table in Access (2010) like this:

Field Name: ArtNo

Data Type: Text

I have data in Excel in a column with the format General:

123

23

541

1246

2153

235

78

147

98

25

45

12

8

254

32

abc1

26

sd2

vb8

58

I copy the cells and open the table in Access.

I choose Paste append.

This is the result when I view the content of the table:

123

23

541

1246

2153

235

78

147

98

25

45

12

8

254

32

26

58

The three rows with text characters are there, but the data is missing.

Access just through it away without any notice!

The reason seems to be that the first 15 article numbers only contain figures. 

But why throw the article numbers with text characters in them away? I am appending them into a text format column!

If anyone have a good explanation for this, please try to convince me.

Because I Think this is a very wrong behavior!

Actually, with a little looking up, this is a bug.  One of the work arounds is to close excel AFTER doing the copy.  Then go to the Access table and PASTE ..... it does work.

My work around this issue was to write a little VBA in Excel which prompts the user if they want to a) save and b) create and export file.  if yes, then it do and save as a csv file (where I control the location and my access application looks to the same location).  My access form has a button to 'import' the csv  data.  It works pretty slick.

here's what I did in Excel -- on the OnClose:

Sub onClose()
On Error GoTo Handle_ERR

'Does the user want to save this file?
    Dim response2 As Integer
        response2 = MsgBox("Do you want to SAVE this file?  (no Save and No file)", vbYesNo, "Want a Save?")

        If response2 = vbNo Then
            GoTo Exit_Close
        End If
   
'Copy the current data and save it
    Sheets("ExceptionRules").Select
    Range("F3:F51").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("O3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
    Sheets("Working Capital").Select
    Range("G2").Select
   
    ActiveWorkbook.Save

'Prompt the user if a file is wanted or not?
    Dim response As Integer
        response = MsgBox("Do you want a FILE for the daily Sympro Access TRTC database?", vbYesNo, "Want a data file?")

        If response = vbNo Then
            GoTo Exit_Close
        End If

'Temporarily copy the range to a new sheet to export
    Dim strFileName As String
   
    Sheets("Copy 2 Access TRTC").Select
    Range("import2Access").Select
    strFileName = "J:\DailyInvestment2Access.csv"
   
'Sub SaveRangeToTextFile(xlRange As Range, strFileName As String)
    Selection.Copy
    Workbooks.Add
    'Sheets.Add
    'ActiveSheet.Paste
    Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipblanks _
        :=False, Transpose:=False
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=strFileName, FileFormat:=xlCSV
    'ActiveSheet.Delete
    ActiveWorkbook.Close
    Application.DisplayAlerts = True

   
Exit_Close:
    Exit Sub

Handle_ERR:
    MsgBox Err.Number & " - " & Err.Description, vbInformation, "Error on Closing"
    GoTo Exit_Close

End Sub