This is a continuation of my earlier post:
That I have created because of my recent video on the subject:
Following up on my post:
I thought we could look at how we can determine the next AutoNumber Value that will be assign when a new entry is made in a table.
Continue reading
Have you ever wanted to hide a table, query or other database object from prying eyes?
Today, I thought we explore a few of the available options to do so. I’d like to explore:
Every once in a while I see a question regarding wanting to reset an autonumber’s counter/seed value to a specific number.
If the numbers have a big jump in numbering between the last entry in the table and a new one you are creating, and you are simply wanting to eliminate this gap, then performing a Compact and Repair should do the trick.
So say you delete a large quantity of records from a table, performing a Compact and Repair will reset the value to the very next number in the sequence based on the current maximum value in the table.
If you wipe a table completely, then performing a Compact and Repair will set the numbering back to 1 (or whatever the set seed value is).
Continue reading
So after posting about closing individual objects:
I thought it simply made sense to create a single procedure that did it all.
Continue reading
Have you ever needed to close any and all open tables? Ensure that no tables are still open?
It is remarkably easy to do!
Continue reading
In this article I want to touch upon Field Captions. Why you should Love them and Hate them!
The first thing we need to do is understand what the caption property is exactly:
So have you ever tried to retrieve the Created Date/Time. Pretty easy right as there are a couple ways to attack this!
You can simply query the MSysObjects table:
SELECT MSysObjects.DateCreate FROM MSysObjects WHERE (([Name] = 'Form1') AND ([Type] = -32768));
Or perhaps use DAO Containers and Documents:
CurrentDb.Containers("Forms").Documents("Form1").Properties("DateCreated").Value
But what about the Modified Date/Time?
So you would think that we could simply modify the above query of the MSysObjects table to:
SELECT MSysObjects.DateUpdate FROM MSysObjects WHERE (([Name] = 'Form1') AND ([Type] = -32768));
Or the DAO Containers and Documents to:
CurrentDb.Containers("Forms").Documents("Form1").Properties("LastUpdated").Value
But those are not reliable and don’t always work properly! (especially for Forms and Reports). Instead, they seem to return the Created Date/Time again.
Continue reading
I recently saw a post in an Access forum get sidetracked into a discussion/argument regarding whether Access has table Fields or Columns?
It amused me and thought I’d make a brief post about it.
Now, if you have experience with pretty much any other database, tables are comprised of Columns (SQL Server/Azure, MySQL, PostgreSQL, ORACLE, …):
I thought I’d share a simple function that can iterate through a database’s tables to identify those containing ‘complex’ fields.
Simply put those, that use magic behinds the scene to store multiple values in hidden system tables. In Microsoft’s words:
Basically, Attachments and Multi-Valued fields.
I’ve touched upon this in the past, MVF are EVIL. You never want to use hidden feature like these. You want to build your own related table structure that you are in control of. MVF can’t be upsized, can’t be used in union queries, … Attachments lead to database bloating and are always ill-advised except for very limited usages!).
Hence, when I take over a database, I run a series of checks, amongst others, identifying MVF so I can review them to see if they legitimately have their place or should be replace properly.
Continue reading