Option to use Attachment data type in Azure

[Kayleigh]

Hi
I'm building a database which will be migrated to Azure. The client would ideally like an image of each item on record. However when I checked up how compatible this would be I found the Attachment field type is not supported by Azure. Are there any other other options I could try?

 

[Minty]

You can use Filestream to manage Blob storage in Azure SQL.

Binary Large Object (Blob) Data (SQL Server) - SQL Server

With FILESTREAM, FileTables, and Remote Blob Store (RBS), SQL Server can store blobs in the database or in remote storage. Compare options for storing blobs.

docs.microsoft.com

 

[arnelgp]

Are there any other other options I could try?

i think there is none right now. whether you use Attachment or external file, you will need
need new code to "show" the image from azure to your form.
i wouldn't think about that for now but would suggest to google how you can you show
the image from sql server to your form and how to upload an image to sql server.

 

[Kayleigh]

So what data type should I use when I set it up now in MS Access (before migrating)?

 

[GPGeorge]

So what data type should I use when I set it up now in MS Access (before migrating)?

Did you follow the link in Minty's post?

I would NOT recommend attachment fields in ANY database table, not Access, not SQL Server and not SQL Azure. That said, the server-based databases are at least not subject to the problem of file size and bloating of Access files resulting from use of embedded images.

Also, check out Azure Blob Storage.

 

[arnelgp]

you can use Attachment for now.
if you need to Later moved your Attachment to sql table,
follow this code:
Trick of the Week :: Store (and then fetch) files from SQL Server tables | Useful Gyaan (wordpress.com)

 

[GPGeorge]

Look, you are at phase one: planning the design of the tables.
Why implement a design choice that we all agree is more difficult to manage and even more difficult to migrate later, just because you can do that?

I guess if you want to implement the attachment field because it's not impossible, then do so. You'll find that leads to much more work in the future, of course, and the longer it takes to migrate the data from the ACE tables in Access to SQL Azure, the more records you'll have with attachment fields, and the bigger the potential bloat problem gets. In a reverse logic kind of way, though, maybe that'll prove to be an incentive not to dawdle in the upgrade.

 

[Pat Hartman]

Don't start with something you know you have to change. That means the attachment data type is not the way to go.

You can store objects as an OLE Object in an ACE database and that is convertible to Azure so you can use bound object frames to display the image. The advantage of the Attachment data type is the control used to display it. Like other data types added in 2007 it creates a de-normalized structure to make a 1-m relationship work in a cool control It makes it very easy to include multiple objects for each record. If you have only ONE object (picture), then the Attachment data type isn't going to help, it is just going to hinder your conversion.

How long before the conversion happens? You don't want to end up languishing with an ACE BE that includes OLE Objects. You will be amazed at how quickly bloat gets in the way. If the conversion is not imminent, you might consider storing the path to the .jpg rather than the object itself. In that case, you would use an unbound object frame to display the image and load it in the current event of the form. I do this for reports in one app because the client has multiple businesses all using the same application and they want to change the logo depending on which business is sending out the letter.