Access 2016 append query "long text" field is being truncated 1

[08211987]

I have a spreadsheet that is linked to 2016 database and when I append it to access table it truncates the data. I've read this is new in 2016 but have not found a resolution. Does someone know of one? The linked spreadsheet table contains all the correct data.
Thanks!

 

[MajP]

I will often add a bogus row at the top of the spread sheet. Any columns I want to be memo fields I add random text longer than 255 characters. It will then treat this as a memo and not text column. Then on my append, I just do not append the bogus row.

 

[MajP]

I have had luck by just adding one bogus row for memo fields, but it may be eight that you need to add if one does not work.

Access reviews the first eight rows in each column to suggest the data type for the corresponding field. If the column in the worksheet contains different types of values, such as text and numbers, in the first eight rows of a column, the wizard suggests a data type that is compatible with all the values in the column — most often, the text data type. Although you can choose a different data type, remember that values that are incompatible with the data type that you choose will be either ignored or converted incorrectly during the import process. For more information about how to correct missing or incorrect values, see the section Troubleshoot missing or incorrect values, later in this article.

 

[Andrzejek]

I would write my own logic to 'append' (insert) data from Excel spreadsheet into a table, and not rely on any Access' Wizard and any assumptions, corrections, shortcuts, or suggestions. This was I am in charge to verify and insert the data the way I want to, not the way Access does it. It is just a few lines of code...


---- Andy

There is a great need for a sarcasm font.

 

[MajP]

Andy,
What approach do you recommend for doing this? I have never done this, but from what I have seen you can make an ADO or DAO connection directly to the spreadsheet and do an insert query. Something like:

Sub test()
   Dim dbWb as string
   Dim dsh as string
   Dim dbCon As New ADODB.Connection
   Dim dbCommand As New ADODB.Command
   dbWb = Application.ActiveWorkbook.FullName
   dsh = "[" & Application.ActiveSheet.Name & "$]" & "Data2"  'Data2 is a named range
   sdbpath = "C:\Users\myname\Desktop\Database2.mdb"
   sCommand = "INSERT INTO [main] ([dte], [test1], [values], [values2]) SELECT [haha],[test1],[values],[values2] FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
   dbCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sdbpath & "; Jet OLEDB:Database Password=;"
   dbCommand.ActiveConnection = dbCon
   dbCommand.CommandText = sCommand
   dbCommand.Execute
   dbCon.Close
End Sub

 

[Andrzejek]

Yes, you can do that way, but where is any verification of the data?

Let's say you have a column in Excel:[tt]
DOB
1/1/1999
12/12/1964
5/6/1972
Uncle
1/6/2002[/tt]

Your table should not accept 'Uncle' in the date field DOB.

So what I usually do is: 'scan' the data in Excel, validate all fields in all rows, and if anything 'funny' is found - report it so it can be cleaned. After all data is clean, I can easily copy all data from Excel (I know it is all OK) to my table(s)


---- Andy

There is a great need for a sarcasm font.

 

[08211987]

I've tried the Dummy rows like you suggested. But maybe I am not adding those records correctly. Instead of adding 8-10 complete dummy rows, I added 2 columns at the beginning of the report, both had more than 255 characters for each record > 5K recs. The file is a linked table. When I look at the contents of the linked table, those 2 columns show show as Long Text . Once I append

 

[08211987]

I've tried the Dummy rows like you suggested. But maybe I am not adding those records correctly. Instead of adding 8-10 complete dummy rows, I added 2 columns at the beginning of the report, both had more than 255 characters for each record > 5K recs. The file is a linked table. When I look at the contents of the linked table, those 2 columns show as Long Text. And the values of those fields are not truncated and the other fields that should be long text are also not truncated. But as soon as I append that linked table to another access table that also has those fields defined as Long Text, they are truncated.

Do you know why my approach cannot work? Do I need to try the VBA approach?
Thanks!

 

[MajP]

I applied originally to your first post with this:
If I understand correctly you added 2 new columns with long text. That is not going to work. When you link you do not get the option to define datatypes like when you import. So Access makes a decision column by column. So if column A has the first 8 records without anything more than 255 it assumes that is a text field and not a memo field. So you have to add a dummy record above to ensure that each column that has long text has more than 255 characters in those columns. I do not know how many columns you have but if it is just one it is easy without adding dummy rows. Instead add a column that returns the length of your text field. Then sort on that column, descending. This will put the long text at top of the worksheet.

However, in your second post it sounds like you are saying something else. Are you saying the linked table appears correct, but it truncates when you append from the linked table into an existing table?

 

[MajP]

So after some reading, I think it is possible what you say. The link table may come across correct, but the append will not. Two possible solutions
1) You should be able to import into a temptable. To do this define all the datatypes and when you are done save the import specifications. I believe this will keep you from having to define the datatypes again. Then append from that table.
2) I still think eight dummy rows will ensure that the append works as well. Again you need the first 8 rows to have more than 255 in each and every column that is memo. I am guessing this is case dependent because I have always had luck as long as the first row did this.

 

[08211987]

I am so sorry I sent 2 posts, my machine had paused and I didn't think it sent so I continued typing, hence 2 posts...
I put the 8 dummy records, defined at the top of the linked spreadsheet table with the Long Text (and Text Format as Rich Text (read somewhere) for those 2 fields and appended the linked spreadsheet sorting ascending by record id (putting those dummy records at top) into an Access Table where the fields are defined the same and it truncated the descriptions.

I then tried importing the spreadsheet with the 8 dummy records at top into an Access Table with the field defined as above and it did not truncate the descriptions. The problem is that I then created an append query of the resulting table into another table appending all records except the dummy records and in that table the truncation happened again!! I might just have to do VBA XCOPY or what it is called today? I'm thinking I'll do the import in code anyway.

I'll begin to try that tomorrow unless you have some words of wisdom? Is this issue only on a certain version of Access 2016, Windows 10?
Thanks!

 

[MajP]

Since you filter out the dummy rows Access probably goes through trying to determine the datatype. It applies the filter before the append. So I think you can
1) Append the records first and then delete out the dummy records, versus filtering out the dummy records first. I assume that would work. Just to verify, the receiving table is set up as memo for those fields?
or
2) Instead of linking, do what I said previously and import the records first into a temp table which will allow you to define the datatype as memo. Save the import specification so you can easily rerun it next time. Then append from there
3) Or you could append directly into the table, and i believe that will work since the target fields are defined as memo.

If the records have an unique indexed field they will not be appended. So if your dummy records have PK values that already exist in the appended table they are going to be skipped, because they will cause a duplicate. So you are unlikely to have to remove them after the fact if you force them to fail the append. So I would have eight dummy records that will always fail and paste those at the top of my spreadsheet before import.

 

[08211987]

MajP, I dd all that you suggested and everything works, thanks! I got to thinking that this table is used in other processes and as long those processes don't append that table again I think we are good. Then I was wondering, what if every time the table gets appended again, I'd have to add the dummy records again, so I created an update query to update those long text fields from the original source after the different appends and it works great. So for now that is what I am going to do. It adds another update query but eliminates adding dummy records, at least that is what I am hoping. Please let me know if you see issues in this approach, if you would.

Thanks for all your help, I learned a lot and it's too bad Microsoft can't fix something that worked before!
Thanks again!

 

[MajP]

If your approach is working for you I would think it is fine. As stated there are several different ways to work around this issue. You often find truncation too late, unless you are actively looking for it. You may want a to build a query that returns records where the length of certain fields are 255 (where len([fieldA]) = 255 or len([fieldB]) = 255). Then you can run this query to see if you have possible truncation in your fields after doing your insert.

 

[08211987]

Great Idea, thanks!

 

[08211987]

Does anyone know if the issue with Access 2016 still has the issue with Long text being truncated? I managed to get around an issue where I was importing excel to Access (all talked about above) and to get it to work, I did an update query of that field from the original source at the end of all my queries and it fixed it. Now I have that same field that will be updated for certain cases but from a different source. The different source is actually a shortened version of the field so that it fits appropriately on a report (smaller but still up to 400). I perform a update query that will update it from that source and it truncates to 255. Same exact query, different source. Any ideas?

I which MS would fix this issue. Also how can I contact them or find out if they have fixed it? My post seems to be running off the screen, do you see that also?
Thanks for your assistance!