Guide VBA Microsoft Access (MS Access) Tips and Tricks - Samples - Downloads - Discussion!

The Helper

The Helper

Necromancy Power over 9000
Staff member
Reaction score
1,727
This is the thread where I post the tips and tricks I learn and have learned on the job as a Microsoft Access Developer and Consultant (Affordable IT)

www.affordableit.com

I have been doing this for over 20 years.

Microsoft Access is awesome if you know how to use it. If you know VB6 then you will feel right at home in Microsoft Access VBA. It integrates with Excel, Word, Power Point, QuickBooks, and a huge amount of other applications. You can really do anything in MS Access. It front ends pretty much any Database as well. Especially easy with SQL Server and the Personal Edition of SQL. It also has ODBC Drivers for MySQL :)

The first tip is a real nice one. It is a Calendar Report I found online doing research for.... a Microsoft Access Calendar Report :)

http://www.access.hookom.net/Samples.htm

Gives you an easy to configure Monthly Calendar Control in a report. Easily does anything that you want to show on a schedule if you have just a date for it.

The one I downloaded was http://www.access.hookom.net/Samples/CalendarReports.zip

This Access 2000 sample shows how to create several different calendar style reports. There is a doctor/patient schedule that displays a week-at-a-glance with dates across the top and times along the left (sample). Each appointment is "drawn" with the proper size and position. A similar report places multiple doctors across the top for a single day schedule.

A standard monthly calendar is also included (sample). This report uses copies of the same subreport for each day. A gantt type report (sample) is included which displays time bars across longer periods of time.
Click to expand...

I will include pictures and my mods to it at a later time.

If you have any questions about this sample post in the thread!
 
Last edited:
Zip File Manager 7Zip - Free Download - Microsoft Access MDB File blocking in Outlook

7Zip is a Fantastic Free Zip file manager. You should never send (attach) any Microsoft Access .MDB file to an email without compressing it first.

If you need to manage compressed files (.zip or otherwise) 7Zip is the way to go. In zip format a Microsoft Access file can be compressed quite alot but if you use the 7Zip format then a Microsoft Access database can be compressed almost 50% more depending on what is in it. 7zip format is the way to go but the program handles .zip files just as well. It uses the free Dos version of though I believe.

7Zip Main Site
 
Last edited:
Microsoft Access (MS Access) Quickbooks ODBC Driver Integration - The Next Level!

Quickbooks Enterprise users get a free read only License for ODBC compliant applications like Microsoft Access (MS Access), Microsoft Word (MS Word), Microsoft Excel (MS Excel), and everything you need to access your Quickbooks Data is right there for you.

It is god awful slow though. That is something I am going to have to overcome but I will update this thread with my progress on that.

Work in Progress - Microsoft Access Quickbooks Integration with ODBC Drivers. MOAR Soon! :)

http://www.qodbc.com/qodbc.htm
 
Last edited:
Modal Property of Forms freezes VBA Code - Microsoft Access (MS Access)

If you open up a Modal Form in VBA using a Microsoft Access (MS Access) Event Procedure all code execution will stop until that form is closed. Using this you can run code after the Modal form is closed. Code execution resumes after the original Modal form open call (right after the code that opened the modal form).

You can do this using Microsoft Access (MS Access) Macros.

Very Handy
 
Middlewaresdk is an interface to ACT that you can use with any programming language. Not just .net I used Microsoft Access VBA.

This version works with ACT 8 and up I believe. I think ACT 11 and up have a different version. I will try to get more information on this but it works with ACT 10.

It is very handy to do specific tasks. Combined with the ACTReader OLEDB tool you can do anything with this baby. I am crippled in an earlier version of ACT but I can still do what I need to and more. The latest version for the latest ACT versions has lots more.

NOTE on the ACT READER - You need to have the OLEDB Data Source set up (I used a File DSN) on the Server that is running ACT. You can save your password in a file DSN. You can use the ACT READER if you are programming in an ODBC compliant environment. The middleware for ACT 8 - 10 is crippled without any account lookup functions so you will need the ACT READER to find the GUIDs to control ACT.

The newer version of the Middleware for ACT 11 and up includes better lookup functions I am told.

Great tool for interfacing any application with ACT. You could probably even suggest a new feature for the middleware to the author Kevin Ritch and he might even add it. He is a real nice guy and very open to feedback on the middleware.

http://middlewaresdk.com/

If you use the program and need any help post here and I can help.

NOTE - The VB6 examples included with the SDK download are wrong. The interface works through a jobcontrol.txt file that the ACT plug in reads for instructions. Any application that can write a command to that jobcontrol.txt file can use this middleware. The thing is that you need to load supporting files that are not well documented in the VB6 examples, the examples are wrong. If you look to the dotnet source code that is included you can find out what the file names are of what files you need to load for the commands you write to jobcontrol.txt to work. The vb6source code implies that the middleware writes guids and it does not. The only thing you write to jobcontrol.txt is commands and anything else goes in support .dat files.

ie if you do a cpop you need to write a guid to the proper .dat file. The dotnet source code contains all the file names.
 
Last edited:
td.connect - how to determine table attachments

The DAO Tabledefs object has a connect property that tells you what the connection string is for an Attached table (where the table actually exists).

You can read that property with the following code

Code: 
dim db as dao.database, td as dao.tabledef

set db = currentdb 'using the current ms access database can be changed to access any database

set td = db.tabledefs("nameofattachedtableintableslist")

msgbox "The connect property is " & td.connect", vbokonly

set td = nothing
set db = nothing

You can modify this value then call the refreshlink to progmatically change table attachments.
 
Last edited:
Hi Chris,

Yes, the documentation for MiddlewareSDK for ACT! by Sage 2008 is indeed rather thin. :)

A proper version will be written in Spring. Thanks for your enthusiasm Chris. It is much appreciated.

And for the valid criticism about the examples. But you have figured most of it out.

As you know, the old version for Sage ACT! 2008 will always remain free.

The newer version for Sage ACT! 2011 and up, will have a Developers License at a jolly reasonable price.

There are exciting plans for MiddlewareSDK for Sage ACT! 2011+ and it will be fully documented in the near future.

Comparing MiddlewareSDK to the SDK for Symantec ACT! (2004 and older) provides the insight that, effectively, being a "PlugIn" for use whilst ACT! is running, MiddlewareSDK is an "App Object".

I have also developed a Database Object too. I call that ACTServerware.exe and it works when ACT! is not running.

ACTServerware.exe works in a very similar manner. (Writing out instructions and then using a Control File)

Importantly Chris, ACTServerware.exe will never be free. So again, like MiddlewareSDK for Sage ACT! 2011/2012, it will have a price-tag.

So far, ACTServerware.exe has brought in about $25k in software development in the first year of coming into existence. I expect that figure to be at least ten times that in the second year of life. (I am an eternal optimist)

Cheers again for your grand enthusiasm Chris.

~Kevin~ ( http://V8Software.com )
 
I have done a full project on the QODBC and it is super powerful. I upgraded to the write version and now create Invoices and Purchase Orders in Quickbooks from MS Access. If anyone has any questions on accessing Quickbooks from Access or any other Office Product ask in this thread.

Quickbooks QODBC Microsoft Access Connection Microsoft Office

The Helper said:
Microsoft Access (MS Access) Quickbooks ODBC Driver Integration - The Next Level!

Quickbooks Enterprise users get a free read only License for ODBC compliant applications like Microsoft Access (MS Access), Microsoft Word (MS Word), Microsoft Excel (MS Excel), and everything you need to access your Quickbooks Data is right there for you.

It is god awful slow though. That is something I am going to have to overcome but I will update this thread with my progress on that.

Work in Progress - Microsoft Access Quickbooks Integration with ODBC Drivers. MOAR Soon! :)

http://www.qodbc.com/qodbc.htm
Click to expand...
 
Last edited:
Microsoft Access Expert Advice thread post here for answers to Microsoft Access Questions especially the older versions like Access 2003 and getting those versions updated to the latest version or keeping them going on the old stuff. I cannot believe there are so many views on this thread and nobody has any questions but there are alot of great Access and VBA resources out there so it is hard to compete I suppose.
 
I am getting a license for Total Access Emailer really soon and cannot wait to get started working with that. FMS Inc has the greatest Access stuff and this emailer solves all the problems I have had sending automatic emails out of Microsoft Access and being able to customize them. It fixes all the problems with sending emails from Access by using an SMTP server and the proper encryption. Nice! I am going to be posting more about this once I start getting into it!
 
You must log in or register to reply here.
General chit-chat
Help Users
  • No one is chatting at the moment.
  • Varine Varine:
    I would be there for days, even with my camera set up slides can take a long time, and if they want perfect captures I really need to use my scanners that are professionally made for that. My camera rig works well for what it is, but for enlargements and things it's not as good.
  • Varine Varine:
    I've only had a couple clients with that so far, though. I don't have a website or anything yet though.
  • Varine Varine:
    Console repair can be worthwhile, but it's also not a thing I can do at scale in my house. I just don't have room for the equipment. I need an office that I can segregate out for archival and then electronic restoration.
  • Varine Varine:
    But in order for that to be real, I need more time, and for more time I need to work less, and to work less I need a different job, and for a different job I need more money to fall back on so that I can make enough to just pay like, rent and utilities and use my savings to find these projects
     +1
  • Varine Varine:
    Another couple years. I just need to take it slow and it'll get there.
  • jonas jonas:
    any chance to get that stolen money back?
  • jonas jonas:
    Maybe you can do console repair just as a side thing, especially if there's so much competition business might be slow. Or do you need a lot of special equipment for that?
  • jonas jonas:
    I recently bought a used sauna and the preowner told me some component is broken, I took a look and it was just a burnt fuse, really cheap to fix. I was real proud of my self since I usually have two left hands for this kinda stuff :p
  • tom_mai78101 tom_mai78101:
    I am still playing Shapez 2. What an awful thing to happen, Varine, and hopefully everything has been sorted out soon. Always use multi-factor authentication whenever you have the opportunity to do so.
     +1
  • Varine Varine:
    I think all of the money is accounted for now, and all the cards have been changed out, so I think for the most part it's taken care of now. Just need to go through and make sure all of my accounts are secured again, it's just time consuming.
  • Varine Varine:
    And yeah everything has 2 factor turned on now, or at least everything I can think of at the moment.
  • Varine Varine:
    The consoles don't need too much equipment that I don't already have. I would like to get a reflow oven, but I don't really want to buy one so I'm thinking about modifying a toaster oven I have to make something that will work for what I'm doing.
  • Varine Varine:
    I have the soldering irons and reflow and all that, but without an oven it's kind of hard to build mod chips and things like that. I made a handful of them with a hot air station, but it's a pain.
  • Varine Varine:
    The only thing I'm not really set up for is BGA rework. I've done it before a little bit, but not reliably, and that equipment is wildly expensive. You need X-rays and shit.
  • Varine Varine:
    I also have a couple 3D printers. I'm not super good with those and need to get an enclosure built, but they'll be useful for some aesthetic mods I've been thinking about. At least I can use them to do designs and then just have someone else print out the parts for me once I know they work.
  • Varine Varine:
    I also use them to make adapters for all my camera shit, but that's also an on the side thing for now. Lens adapters get really expensive.
  • Varine Varine:
    I've been trying to do some little art pieces as well, but I'm not much an engineer so they haven't gone great. I got some new things showing up to try and play with
  • Varine Varine:
    I want to build this tesserect kind of thing with mirrors, and I've been trying to make this like black hole diorama. In my head it looks really cool, but I kind of thought I could form polarizing lenses into a sphere but I tend to just destroy them every time I try.
  • Varine Varine:
    So I got a new idea, but I'm not sure how to make it work like I want without being able to get a polarizer curved. I think they are made out of PVA typically, and I thought I could just heat it up a little bit to soften the film, but that clearly isn't working. So I'm going to try a few other things, I'm thinking if I put a mirror film over the polarizing film I might get something cool. I have some polarized LED's as well, and I think if I make a central light source I can use the mirrors combined with the polarizers to make that central light APPEAR black. I have next week off so I'm going to spend some time trying to figure it out
  • Varine Varine:
    The tesserect works, at least. I just need to figure out how to be able to assemble it, but I think I have a pretty good idea of how to go about it. Or at least a prototype of it. I'll post some pictures next week
  • jonas jonas:
    That last bit sounds like the last entry in a scientist's journal in a destroyed research facility in a post-apocalyptic video game
  • Varine Varine:
    lol it's not that exciting
  • Varine Varine:
    Shiny tho
  • Varine Varine:
    Basically it's a cube with a two way mirror on the inside, and then a smaller cube suspended in that with a mirrors on the outside of it. Kind of like those infinity pictures where they use two mirrors to go forever. Only it's twelve mirrors
  • Varine Varine:
    And the tiniest LED strip I could find

      The Helper Discord

      Staff online

      Members online

      Online now:
      448 (members: 4, guests: 444)

      Share this page

      Affiliates

      Hive Workshop NUON Dome World Editor Tutorials

      Network Sponsors

      Apex Steel Pipe - Buys and sells Steel Pipe.
      Top