-1

I'm using MS Access to visualize the data from a MySQL database. The data from MySQL is containing some images, which should be displayed in a continuous-form in MS Access. The images in the MySQL database are stored in a BLOB field.

I linked the MySQL table in MS Access through the ODBC-driver and I'm able to see the data in the MS Access table, but displaying the images is not an easy task.

I saw this and it's working great for single-forms, but as far as I know it can't be used for continuous-forms:

There are also some vba code snippets out there, which dump the BLOB to a temp file and then the form is referring to the file-path:

I am not really happy with this solution because the total size of the images is about 500 MB, which will probably be downloaded and stored every time!?

Does anybody know a good way to display these images!?

EDIT: I just realized that the images (.bmp) can be displayed in a form, if they are inserted by the import-function of MS Access into the linked table. So, somehow the generated binary file created from MS Access must be different from my images, which I'm uploading with python to the MySQL database, but I don't know how.

Thank you very much in advance for your support!

Community
  • 1
  • 1
Michael Haar
  • 671
  • 6
  • 14
  • You realize that 500MB is sent across the network every time that query runs anyway, right? Any reason you can't just store the *network path* to the images instead? – Mathieu Guindon Oct 24 '16 at 18:19
  • @Mat'sMug can you please tell me how to achieve this!? I do not host the MySQL server. So, I have to store the images somewhere on the server with ftp or ssh and only store the path in the MySQL database instead of the BLOBs!? – Michael Haar Oct 25 '16 at 14:42

2 Answers2

0

I've ended up with uploading the images to GoogleDrive instead of storing them as BLOBs in the database. The images can then easily be referenced with the relative folder path

Michael Haar
  • 671
  • 6
  • 14
-1

MS Access is a Client-Side database. What this means is that every time you run a query, it pulls all of the data across the pipe to your local PC, performs the operation(s) necessary, and then (if required) pushes the data back to the server.

Pulling large amounts of data across the pipe is going to kill your application. And the only way Access can handle files like the one you're describing is if it contains a field with a data type of "Attachment". You can't do this with a linked table.

The most efficient way to do this will be to use a VarChar/Text field with a network path to the image. You can then bring the image into your form based on the path to said image. If you really, really want to use an embedded image, you'll need a table with a field of data type "Attachment", and some type of Key to tie it in to the linked MySQL table.

Take a look here for a little more info: https://support.office.com/en-us/article/Attach-files-and-graphics-to-the-records-in-your-database-bced3638-bfbc-43f0-822d-374bca2e6b40

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Thank you for your reply. I'll give the network-path option a try! – Michael Haar Oct 24 '16 at 19:34
  • I shall also add that in ANY system, if you pull 10 rows, then all 10 rows come to the client. That client can be c#, vb.net, c++ or any development system. And in fact even when dealing with a web server, the web server will ALWAYS pull the requested rows for the given SQL. However, even in the case of an access client, you request 10 rows out of a 1 million row table, then you only get the 10 rows pulled. The number of rows you pull is NOT any different than using software written in c++, vb.net, c# or in this case Access. – Albert D. Kallal Oct 24 '16 at 21:12
  • 1
    I'm sorry if you don't understand how Access works, Albert. If you have a linked table and you run a query in Access, it most certainly does pull the entire dataset across the pipe. – Johnny Bones Oct 24 '16 at 23:36
  • I got two completely different answers now. – Michael Haar Oct 25 '16 at 12:13
  • I order to find out who is right, I did a small experiment. I installed _glasswire_ to monitor my network traffic and afterwards I started MS Access. About 0,1 MB are downloaded instantly when opening the linked table from the database. I saw 225 entries in the table and the rest was loaded if I scrolled down to the end of the list, which gave me another 225 entries and ~0,1 MB download usage. This process can be repeated until all rows are loaded. The same thing is happening again when I opened my _continuous-form_. – Michael Haar Oct 25 '16 at 12:41
  • The average size of the images is about 30 kB. Therefore, I expected the downloaded data to be at least 30kB*225=6,75MB instead of 0,1MB. Due to my experiment, I’d say that MS Access is loading small sets of data and is not even downloading my images. – Michael Haar Oct 25 '16 at 12:41
  • @MichaelH that would be the client (Access GUI) virtualizing rows. Try running `SELECT TOP 10 * FROM ThatLinkedTable` while monitoring network traffic, and if possible run a profiler on the MySql side to see if the "top 10" part is actually passed through the driver. This answer presumes that the linked table is queried by Access, not MySql - and if that's the case then indeed, the entire table needs to be passed to Access. I suspect the driver *does* translate the query though, which means it's MySql doing the real work. – Mathieu Guindon Oct 25 '16 at 16:17
  • @Mat'sM Queries like `SELECT TOP 10 * FROM ThatLinkedTable`, `SELECT TOP 100 * FROM ThatLinkedTable` and `SELECT * FROM ThatLinkedTable` used all about the same download volume (~0,1 MB). However when I ran `SELECT TOP 100 * FROM ThatLinkedTable` in a vba to save the images to a temp folder, I got about 6MB. Can sb please provide a solution!? – Michael Haar Oct 25 '16 at 17:33