UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Exporting All Awa Images, Access 2013 Web App    
 
   
JoeAccessWebAPP
post Mar 21 2018, 01:50 AM
Post#1



Posts: 6
Joined: 24-May 17



Hi All

I have been searching the internet and forums for a while now but I cannot find a way to get all of my images out of AWA

I have converted the bacpac file to SQL server (express) and that is all working well. The last step we have is to export the images from the Azure back end to the new SQL server.

I know that the images are stored in the images table (with a reference 0xFFD8FFE000104A46494600010101006000600000FFDB004300080.........) but:

Is this only a reference or the actual picture in code? If so, where are they actually stored & how to I get all of them out (without having to click on each image/save as. Each student has an image and there are 600 students in the DB

Thanks for you help (I realise I am cutting if fine!)
Go to the top of the page
 
RobKoelmans
post Mar 21 2018, 03:40 AM
Post#2



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


No experience with exporting pictures, I'm afraid. But hopefully George will be able to help you. Otherwise I can try to take a closer look.
Kind regards,
Rob
Go to the top of the page
 
JoeAccessWebAPP
post Mar 21 2018, 03:43 AM
Post#3



Posts: 6
Joined: 24-May 17



No worries Rob

I do have a back up plan, have exported to sharepoint lists so I have all of the photos but figured there must be an easier way than manually adding them all back in!

Thanks
Joe
Go to the top of the page
 
GroverParkGeorge
post Mar 21 2018, 05:05 AM
Post#4


UA Admin
Posts: 33,245
Joined: 20-June 02
From: Newcastle, WA


I don’t really have any experience either. However, they are stored as binary blobs. That means you should be able to get them directly in your new SS database. How many are ther?

--------------------
Go to the top of the page
 
GroverParkGeorge
post Mar 21 2018, 05:32 AM
Post#5


UA Admin
Posts: 33,245
Joined: 20-June 02
From: Newcastle, WA


I take that back. I did work with them once for a client. I am sure they are available in the new SQL Server tables, as binary BLOBs, which you can render as images if you have the right tool. I don't recall that we did it in Access. I am sure you can render them properly in a web page if you have the right code to do that, though.

What do you plan to do with them?

Attached File  imageinsstable.jpg ( 23.1K )Number of downloads: 1

--------------------
Go to the top of the page
 
RobKoelmans
post Mar 21 2018, 05:41 AM
Post#6



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


I discussed it with our AWA-Expert. The blob is base64 format. You can do anything with that. It's also standardly used in Powerapps and PowerBI.
Rob
Go to the top of the page
 
RobKoelmans
post Mar 21 2018, 05:53 AM
Post#7



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


Hadn't refreshed so I didn't see your second message, George. Now that I see it, there is something strange going on. It's defined in the AWA as a VarChar(Max) but in the database there's a separate table for each table with an image-column, its name extended with _Images (TableName_Images). So probably images exist twice or perhaps even three or four times, if you include the Access.Tablename and AccessRuntime.TableName structure in AWA-databases. But -as said - it almost for sure is base64. We work on a daily basis with all kinds of base64 blobs like images, sounds, videos, pdf's etc in SQL-server on behalf of powerapps, both collecting them and displaying.
There's a number of websites you can just copy or upload the blob-string in and it will figure out for you what kind of base64 object it is and display it for you. You can create a simple function on Azure that will display or play the blob in a browser or iframe within an app.
Kind regards,
Rob
This post has been edited by RobKoelmans: Mar 21 2018, 05:55 AM
Go to the top of the page
 
GroverParkGeorge
post Mar 21 2018, 06:08 AM
Post#8


UA Admin
Posts: 33,245
Joined: 20-June 02
From: Newcastle, WA


Hi, Rob.

Yes, at some point, MS added the images tables to improve performance. They duplicate each of the main tables and are supposed to be there only to store images that would otherwise have been in their parent table. It is possible that earlier instances may have the images in both places, first in the parent table when they were created and then in the related image table when it was added later. In fact, the table that I took the screenshot from was exactly that, I think. It was long ago.

But the key point is that they are a standard image format and can be used directly, as Rob explains.

--------------------
Go to the top of the page
 
ryan996
post Mar 22 2018, 03:18 AM
Post#9



Posts: 90
Joined: 16-June 13



I've successfully done it in the past. I used SQL Server Integration Services. You have to create a query to the relevant table so the picture files are named properly.

Follow instructions here:

https://www.mssqltips.com/sqlservertip/2693...lder-with-ssis/
This post has been edited by ryan996: Mar 22 2018, 03:18 AM
Go to the top of the page
 
JoeAccessWebAPP
post Mar 22 2018, 06:24 AM
Post#10



Posts: 6
Joined: 24-May 17



Hi Rob, Ryan & George

Thanks for the replies. I understand now what they actually are (base64). now that I know this I know they can be loaded into the dashboard (although limited to 32,766 bytes

With my AWA i am converting to a PowerApp even though we have multiple outside users it seemed like the cheapest option (create logins that they can share). AWA was built for an NGO with a small budget. All other options seemed too expensive

I just wanted to make sure I didn't have to manually get all of the photos out and then put them back in.

I was always holding out hope that MS would change their mind about scrapping but that doesn't look like it will happen now
This post has been edited by JoeAccessWebAPP: Mar 22 2018, 06:24 AM
Go to the top of the page
 
RobKoelmans
post Mar 22 2018, 08:15 AM
Post#11



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


If you use base64 with PowerApps, you have to be aware of a number of things. Up to a month ago, you just had to connect the string to the right type of control, put Text() around it!!!!! (undocumented) and everything worked fine. We placed the solution because the community was filled with questions on that while MS-employees said, it wasn't supported yet. It was working without the support people being aware of it. A couple of weeks ago, our method didn't work anymore, so we created a support case. They repaired it, but it's working differently now. You have to modify the beginning of the string with what the blob represents (video, image, sound, etc). This is all with a custom connector. It's possible that you encounter nothing of this when you use common data model, SharePoint, new SQL-connector etc.
Rob
Go to the top of the page
 
GroverParkGeorge
post Mar 22 2018, 08:24 AM
Post#12


UA Admin
Posts: 33,245
Joined: 20-June 02
From: Newcastle, WA


I'm going to back away now. Rob's team is leading the way forward here, and many of us are very grateful that he is.

With regard to the demise of the Office 365 support for Access Web Services, it's a done deal, for a lot of reasons.

Rob's proving that the AWA technology can be pushed forward, but it's well beyond the toolkits and skillsets of the original intended audience for AWAs, I'm afraid.

Microsoft is making a heavy investment in the alternative, PowerApps. Despite the fact that they are a huge organization with major resources, there are limitations to what they can do.

On the other hand, desktop Access itself is getting the attention it has been missing for the last few years.

--------------------
Go to the top of the page
 
JoeAccessWebAPP
post Mar 23 2018, 12:32 AM
Post#13



Posts: 6
Joined: 24-May 17



Hi Rob

Is this the same text that is needed for Power Bi (data.image/jpeg;base64,)
Go to the top of the page
 
RobKoelmans
post Mar 23 2018, 08:50 AM
Post#14



Posts: 456
Joined: 25-November 14
From: Groningen, Netherlands


Hi Joe,
We looked a little deeper. In the ?images tables is probably a hex string, not a base64. hex strings seem to be commonly used for jpeg. As said, we have no experience on AWA images (only on PowerApp blobs) but I expect this format is commonly used within SQL-Server for Images. If you do need to convert, I found an Access VBA sample that does: https://bytes.com/topic/access/answers/8747...vert-hex-string

I hope you have experience with connecting Access Desktop to your AWA-database. If not, there's a number of examples on this site.

Here's some interesting background information on the format:

• List of headers and file type they represent. https://books.google.nl/books?id=7O-cBAAAQB...FFE&f=false

• Explains the format of the stored image. https://social.msdn.microsoft.com/Forums/sq...m=sqldataaccess

Steps to display an image stored in SQL database.

• Convert hex (base16) to base64. I used this site http://tomeko.net/online_tools/hex_to_base64.php?lang=en.
• Convert base64 to respective file type. I used this site http://base64converter.com .

Perhaps you can convert in bulk using the Access VBA code sample.
Keep us posted on your findings.
Kind regards,
Rob
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    17th August 2018 - 06:10 PM