Access to Excel Data Import Cutting off URL

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
Hey All

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

VBA Code:
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;


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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

JazzSP8

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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;

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

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

VBA Code:
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.
 
Solution

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,343
@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

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

@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

BatCoder
Joined
Feb 28, 2002
Messages
1,343
  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).
    VBA Code:
    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

Well-known Member
Joined
Sep 30, 2005
Messages
1,223
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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 :)
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top