Access to Excel Data Import Cutting off URL

[JazzSP8]

Hey All

I've got a Workbook which imports an Access Query using VBA, it's a recorded macro being used to do this;

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
    "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=" & ActiveWorkbook.Path & "\Red Skull.accdb;Mod" _
    , _
    "e=Share Deny Write;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password=""""" _
    , _
    ";Jet OLEDB:Engine Type=6;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transaction" _
    , _
    "s=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't C" _
    , _
    "opy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=Fa" _
    , _
    "lse;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False" _
    ), Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdTable
    .CommandText = Array("Hail Hydra")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = False
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .SourceDataFile = ActiveWorkbook.Path & "\Red Skull.accdb"
    .ListObject.DisplayName = "Table_Red_Skull.accdb"
    .Refresh BackgroundQuery:=False
End With

One of the Fields contains a link to an image URL but for some reason it's getting cut off at certain points. Some examples of this include;

Access	Excel
https://jaz.banana.com/productimages/standard/en_GB/ANV00155006-40.jpg	https://jaz.banana.com/productimages/standard/en_GB/ANV00155006-40.j
https://jaz.banana.com/productimages/standard/en_GB/CNMDIN6S06-40.jpg	https://jaz.banana.com/productimages/standard/en_GB/CNMDIN6S06-40.jp
https://jaz.banana.com/productimages/standard/en_GB/GE_WELONCBTANTALUMRAD-40.jpg	https://jaz.banana.com/productimages/standard/en_GB/GE_WELONCBTANTAL
https://jaz.banana.com/productimages/standard/en_GB/G42172380401-40.jpg	https://jaz.banana.com/productimages/standard/en_GB/G42172380401-40.
https://jaz.banana.com/productimages/standard/en_GB/fluke-8845auk-40.jpg	https://jaz.banana.com/productimages/standard/en_GB/fluke-8845auk-40

One thing I've noticed is that the minimum length of the affected records in Excel is 70 charcters longs, all records under that are fine (it can get up to 93 characters long though).

One other 'odd' thing I've noticed is that in Access it's being recognised as a URL (blue underlined clickable) but two other fieds which contain a URL aren't and are fine when imported.

The URL's that are being recognised, and cut off come from an imported text file Table - The ones that aren't being recognised as a URL are coming from an imported Excel Workbook Table.

Has anyone come across anything like this before and can help shed some light?

Any help would be greatly appreciated.

 

[JazzSP8]

Little bump

Also - I forgot to mention that I'm using 64 Bit Office 2013 on Windows 10 if that makes any difference to this?

Thanks again for any thoughts anyone may have.

 

[JazzSP8]

If anyone with the same problem finds this post in the future.

I stopped Access from creating a URL by replacing the 'http' in the query design with this formula in Access;

imglink: Replace([ImageDB.image_link],"http","jazz")

Then once the Query had run in to Excel I changed it back;

Selection.Replace What:="jazz", Replacement:="http", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

Seems to have done the trick!

Would have been nice to have been able to work out "why" without a workaround, but it's working now and I can work in restoring my sanity.

 

[smozgur]

@JazzSP8 - I tested your original code in 64 bit Office 365, Windows 10, and it works without a problem.

Can you tell me the table field properties?

 

[JazzSP8]

@JazzSP8 - I tested your original code in 64 bit Office 365, Windows 10, and it works without a problem.

Can you tell me the table field properties?

Sure, and thanks for taking a look

The field is coming from a linked Tab Delimited text file - It's Data Type is "Hyperlink"

I don't seem to be able to change the Data Type though, if I change it once it's linked or as I'm linking it I get an error "Operation is not supported for this type of object".

 

[smozgur]

1. How did you get to set the field type as Hyperlink when you link a tab-delimited text file?
   Is it possible to change that field type to "Short Text" during the linking and then retry?
2. Do you have PowerQuery installed? If you do then can you try the following code? (Trying to elect data driver problem possibility).
   

   With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
   "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""Hail Hydra"";Extended Properties=""""" _
   , Destination:=Range("$A$1")).QueryTable
   .CommandType = xlCmdSql
   .CommandText = Array("SELECT * FROM [Hail Hydra]")
   .RowNumbers = False
   .FillAdjacentFormulas = False
   .PreserveFormatting = True
   .RefreshOnFileOpen = False
   .BackgroundQuery = True
   .RefreshStyle = xlInsertDeleteCells
   .SavePassword = False
   .SaveData = True
   .AdjustColumnWidth = True
   .RefreshPeriod = 0
   .PreserveColumnInfo = True
   .ListObject.DisplayName = "Table_Red_Skull.accdb"
   .Refresh BackgroundQuery:=False
    End With

Thanks. I know you solved it by using a nice workaround, but as you said, it would be amazing if we could find the actual reason.

 

[JazzSP8]

How did you get to set the field type as Hyperlink when you link a tab-delimited text file?
Is it possible to change that field type to "Short Text" during the linking and then retry?

I didn't - Access automatically sets the field type it seems.

When I was trying to solve the problem originally I tried to relink the table and manually changed the field type to both "short text" and "long text" using the Import Wizard, which it seemed to accept both times, but when I tried to run the Query in Access I got the same "Operation is not supported for this type of object" message as if i'd tried to change it afterwards.

Do you have PowerQuery installed? If you do then can you try the following code? (Trying to elect data driver problem possibility).

I don't - But I've just put in a request for it to be installed with our Helpdesk (apparantly I've had my Admin rights revoked at some point while working from home) - I'll give it a go once it's been installed and get back to you.

Thanks again for your time