Microsoft Access help needed

[Hawkmoon]

It's me again, crying out for more computer help. I'm trying to create a database in Microsoft Access to track "stuff" in my house. I have the basic database pretty well worked out -- the table is done and I have about 60 records in it to test it, and I have a data entry form that's clean and neat and gets the job done. But ...

I'm migrating over to Access from Excel because I want the more flexible reporting capability, and because I really want to be able to include photos of the items for insurance purposes. And I can't figure out how to get the photos into the records.

In the interest of full disclosure (since in this case it may make a difference), I have been working in Access 2003, but I have Access 2016 on this computer. I would prefer to stay in 2003 because I loathe the ribbon menu structure in Office 2007 and newer, but from what I've read Access 2003 can't handle JPEG images so it's possible that I may be stuck using Access 2016. Ideally, I'd like to keep the actual image files separate and just have Access display (and print) from links to the external files, but if the easiest way to work is to just bring the files into the database itself ... so be it.

Do we have any Access gurus around here?

[AZRedhawk44]

I'm only middling at Access, but I deal in MS-SQL all day.

If you're looking to store the image in the database itself in a table, then the data type you're looking for is called a BLOB.  Binary Large OBject.  They may call it something slightly different in Access/Jet, like a LONGBINARY or something like that.  From there it's a matter of your VBA skills to build a form and handle the transfer of the image from your application into the database.

https://support.microsoft.com/en-us/help/194975/how-to-read-and-write-blobs-using-getchunk-and-appendchunk

Otherwise, storing the image as a URL link to a file system should be rather straightforward, and a varchar should be sufficient to the task.

[Hawkmoon]

I'm not up to writing code within Access. And I don't see a datatype "BLOB" or "Binary Large Object" offered as a data type anyway.

After much searching and looking at incomprehensible videos on Youtube produced by Indians whose English is incomprehensible, I fianlly stumbled upon this:
https://www.youtube.com/watch?v=vH5UHck4vkg

It's only two minutes long, and (after a false start), it works. The false start was that I've been using Access 2003, and in 2003 it didn't work. Once I realized that the video showed a version of Access with the new, ribbon style menu, I opened the database in Access 2016 and it works. Bonus -- once photos are added in 2016, they display correctly if the database is opened in Access 2003.

Caveat: The images have to be stored in a bitmap format. JPEGs need not apply.

Bottom line: It's a bot clunky, but I can accomplish what I want to do without writing code and without resorting to aftermarket programs.

[HeroHog]

Glad you got it figured out.

As a side note, I have some pretty complex Excel 2007 files and I am getting away from Office as fast as I can and was pleasantly surprised to find that SoftMaker FreeOffice 2018 ran them perfectly! Macros and VB Code and all!!!
 

[Hawkmoon]

Was it I who clued you in about FreeOffice? I have it on an Android tablet and on my primary desktop computer. It's not my default office suite on the desktop, but the fact that I can have a suite that looks and acts the same, and reads and writes the same files, on both a Windows computer and an Android tablet is reason enough to give it a look.

The fact that they did what Microsoft should have done, and gave us a choice between the classic menu structure and the "ribbon" menu structure is a bonus. It's just too bad they don't have a real database as part of the suite. They claim the database is built into the word processor, but from a quick look it seems to me that's really nothing more than an address book. It's not up to even my modest database requirements.

[HeroHog]

Someone on THIS Forum, probably you. I was trying Open Office but the compatibility was lacking. This, however, has been VERY impressive!

[Hawkmoon]

Open Office seems to have been pretty much abandoned. On that front, Libre Office (the other fork of Open Office) is where the action is.

Fact is, if I wanted to use Libre Office Base, it will generate a usable database a lot faster than Access. I was able to pretty much replicate days of work in Access in a couple of hours in Base, all while trading e-mails with a friend in Greece. And Base allows JPEG photos.

But ... oh my freakin' God is Libre Office SLLLLLLOOOOOOOOOWWWWWW. My friend in Greece says it runs faster on a solid state drive, but I don't have one of those.

[Hawkmoon]

Update: With the database sorted out, I've been able to format a report that spits out a list of all the firearms. What I'm looking for now is a way to print out in a formatted way just the record for one firearm. It doesn't look like I can do that directly with a report. Do I need to create a query -- and then insert the results of that query into a report?

Can anyone walk me through getting started on setting this up?

[cordex]

What do your tables look like?

[Hawkmoon]

Only one table (with a small lookup table to standardize entries in two fields). Basically the essential info for each item:

• Make
• Model
• Serial Number
• Caliber
• Barrel Length
• Purchase Date
• Purchase Price
• Finish
• Condition
• Current Value
• Photo
• Photo
• Photo
• Notes

[cordex]

What is your unique identifier for the table?  Do you have an auto-increment numeric ID field, or would you use make/model/serial number as a multi-column ID?

If you're wanting to pull a single record for a firearm you need to be able to reference that firearm somehow in a way that lets the computer know that it is distinct from all the others.  Then you can set up a report that lists the guns, you can choose one (say from a drop down box) and send the ID of that one gun to a query.

Then your query would look something like:
SELECT * FROM myGatz WHERE ID=2
or alternately
SELECT * FROM myGatz WHERE Make='Colt' AND Model='1911' AND SerialNumber='3'

[Hawkmoon]

The table does include an auto-increment ID field, and it also includes serial number.

[cordex]

The table does include an auto-increment ID field, and it also includes serial number.

Auto increment ID is the way you want to go.  While it may not be an issue for your, you can't guarantee that every firearm will have a unique serial number or even necessarily have a serial number at all.

Easiest solution within Access is to build a form that displays the data.  Try the Form Wizard for a quick way there.  Then you can add a search button to look for a gun based on whatever criteria you want, or you can just page through the records.