How to Use Base64 in Microsoft Access

[@peterhamer504]

Brilliant! I need exactly this today to encode documents for storage in an SQL database.

[@seanmackenziedataengineering]

Cool! That is a good use case for Base64 👍

[@gerfer6261]

Something completely new for me 👀 As mentioned be very useful for crossover platforms

[@seanmackenziedataengineering]

Definitely!

[@happyollyg2]

How would you use this as part of an update query please? You said it returns a byte array and inferred that's an issue. Are their functions that can handle that conversion to a long string.

[@seanmackenziedataengineering]

Actually the function returns a string that can be used just about anywhere. Since it does not use special characters, you can use it without worry inside of " or ' characters for SQL update strings or in parameters.. in fact, it will make dynamic SQL much safer from injection because it turns values into scrambled letters to be stored. For example, strValue = "YmFzZTYO" strSQL = "Update tblData Set MyField = '" & strValue & "' Where ID = 57;" CurrentDb.Execute strSQL is much safer than if you allowed the real value where the user might put something else for strValue like: abc';Delete * From tblData; or something else nefarious in there to mess up your database. Of course, parameters are preferred, but in many cases, Base64 will save you from "interpretation" in many contexts. If you insert this way, you can leave it as is and then select it to decode and display to the user. Very handy if there are very specific language considerations or funky input with specific line feeds (windows vs linux) etc.

[@kassemaubaid3519]

Very cool..but when can we use it...I mean in what field can it be used..and how?

[@seanmackenziedataengineering]

Base64 can be used in any text field, though you'll probably want to choose "Long Text" or "Memo" to store it since it stores data slightly bigger than the input text. This is a great way to store *exact* text or formatting that you don't want changed by systems that view or process it.

[@DataisKing]

I can see a lot of use cases. I'm starting to use Base64 in Power Automate

[@seanmackenziedataengineering]

Yeah it is really, really useful. After you use it a few times, the why becomes clear! Power Automate is cool, there must be a built-in function for it hey?

[@DataisKing]

@@seanmackenziedataengineering Yeah you can use Base64 when creating certain expressions in Power Automate

[@pablofelipecipriani]

​Hello @@seanmackenziedataengineering, this function its able to manage image fields from SQL Server to Image Control in Access? i have a frontend in access with a db from SQL server and i need to display images in the fields from this server

[@seanmackenziedataengineering]

@@pablofelipecipriani yes, it is one way to store and transfer images easily. Or any file actually, not just pictures.

[@Kash97h]

can you share these codes?

[@seanmackenziedataengineering]

Look for entry 2023-02-07 at mackenziemackenzie.com/downloads