Programmatically change a fields Datatype

[JohnLee]

Good day folks,

I'm hoping someone can help me on this problem I've had for a number of years now and I believe the work around I've being using really needs to be automated.

I import daily text file data into my database, which has a number of fields one of those fields is a path to where the image relating to that data is located.

The field in my database is named hprImagePath and it's datatype is set to hyperlink.

Because text files don't allow data to be underlined the information pertaining to the image path is just raw text data.

This presents a problem when importing that data into my table because even though I have set the field for this particular bit of data to be of hyperlink type, when the data is imported the hyper link that gets created doesn't work, even though the data is underlined and has turned blue.

The only way I can get this to work is to manually changed this fields datatype from Hyperlink to OLE Object save the table and then immediately change it back to Hyperlink and save the table again, then the hyperlink works and the image gets displayed when the link is clicked on.

What I would like to do and I've been searching for a long long time is to be able to programmatically changed the Datatype field from Hyperlink to OLE Object, save the table and then change it back to Hyperlink and save the table.

This would save having to do this manually everyday, whilst it doesn't take long to do manually, a problem I've had to deal with is that when I'm not around the person(s) that are suppposed to do this action in my absence always forget to do that part and so I end up having to deal with this upon my return which is a pain in the neck.

If anyone has any idea of how I can acheive this programmatically I would be most grateful for your assistance.

Regards

John

 

[vbaInet]

I've just tested it and I see what you mean. You need to enclose your URL string in octothorpes (#) before importing it. So google.com should be #www.google.com#

I would imagine that the data in the text file is comignfrom a db so you can enclose those characters before exporting to the text file. If you don't want to do that then you should be able to run an UPDATE query to wrap the links in "#".

That way Access knows to resolve it as a link.

 

[JohnLee]

Hi vbaInet,

Thank you very much for your response, Wow that was so simply to do and to think I've spent so long trying to work out how to programmatically change a fields datatype when there was this solution was out there just waiting for me.

The text file is created from a scanning system so I can make changes to the way it outputs that slice of information no problem on that score.

I've finally can do away with the manual process that I do everyday.

Thanks once again, just goes to show how great this site is, with knowledgeable people like you to help us.

John

 

[vbaInet]

Glad to help JohnLee.