Overcoming 255 Character Limit With CopyFromRecordset When Importing Into Excel From Access ACCDB

[anselhelm]

Hi all,

I've been trying to work my head around this issue for ages and I've come to the conclusion that I can't solve it myself, so I'd appreciate the help.

I use an Excel spreadsheet (2016, XLSM) to import from an Access database (2016, ACCDB) to search the data in a table in the database and then show the results in the spreadsheet. The problem is that one of the fields ("Aliases") has data that is greater than 255 characters in length and uses the Long Text data type.

I'm trying to find a workaround for the 255 character limit for this specific scenario.

Here is the specific part of the code (with my sincere apologies if it's inelegant, as I'm really not a very good programmer nor is it my actual profession):

imerchantname = Replace(Trim(Range("G4")), " ", "?")
imerchantID = Trim(Range("G6"))

db.Connect

If imerchantname <> "" And imerchantID = "" Then
strSQL = "SELECT tblMerchantID.[Merchant Name], tblMerchantID.[ICA (Clearing)], tblMerchantID.[Clearing ID], tblMerchantID.[ACQ BIN (Auth)], tblMerchantID.[Authorisation ID], tblMerchantID.[MCC], tblMerchantID.[Expected Frequency], tblMerchantID.[Aliases], tblMerchantID.[Country], tblMerchantID.[Date/Time Added] " & vbCrLf & _
"FROM tblMerchantID " & vbCrLf & _
"GROUP BY tblMerchantID.[Merchant Name], tblMerchantID.[ICA (Clearing)], tblMerchantID.[Clearing ID], tblMerchantID.[ACQ BIN (Auth)], tblMerchantID.[Authorisation ID], tblMerchantID.[MCC], tblMerchantID.[Expected Frequency], tblMerchantID.[Aliases], tblMerchantID.[Country], tblMerchantID.[Date/Time Added] " & vbCrLf & _
"HAVING (((tblMerchantID.[Merchant Name]) Like ""*" & imerchantname & "*"") OR ((tblMerchantID.[Aliases]) Like ""*" & imerchantname & "*""));"
End If

If imerchantname = "" And imerchantID <> "" Then
strSQL = "SELECT tblMerchantID.[Merchant Name], tblMerchantID.[ICA (Clearing)], tblMerchantID.[Clearing ID], tblMerchantID.[ACQ BIN (Auth)], tblMerchantID.[Authorisation ID], tblMerchantID.[MCC], tblMerchantID.[Expected Frequency], tblMerchantID.[Aliases], tblMerchantID.[Country], tblMerchantID.[Date/Time Added] " & vbCrLf & _
"FROM tblMerchantID " & vbCrLf & _
"GROUP BY tblMerchantID.[Merchant Name], tblMerchantID.[ICA (Clearing)], tblMerchantID.[Clearing ID], tblMerchantID.[ACQ BIN (Auth)], tblMerchantID.[Authorisation ID], tblMerchantID.[MCC], tblMerchantID.[Expected Frequency], tblMerchantID.[Aliases], tblMerchantID.[Country], tblMerchantID.[Date/Time Added] " & vbCrLf & _
"HAVING (((tblMerchantID.[Clearing ID]) Like ""*" & imerchantID & "*"") OR ((tblMerchantID.[Authorisation ID]) Like ""*" & imerchantID & "*""));"
End If

db.RunGetResults (strSQL)

Range("F12").CopyFromRecordset db.Recordset

db.Disconnect

If anyone can help me with this specific situation, I'd be very grateful

Thank you to anyone who reads this and leaves a reply!

 

[mole999]

Are you being truncated, otherwise only 255 shows

 

[anselhelm]

Are you being truncated, otherwise only 255 shows

Hi there,

Yes. The data from the Aliases field is truncated at 255 characters as far as I can tell, as only the first 255 characters are being pasted into the cell via CopyFromRecordset.

 

[anselhelm]

Hi all,

Just checked with the forum rules before posting this bump post, as alas I've had no replies for a couple of weeks

Anyone who might be able to help me with this 255 character limit?

Thanks to anyone who tries to help!