How to dynamically name an export file in Access

How to dynamically name an export file in Access

I recently had a situation where I needed to archive exported files produced by Access (that were imported into another downstream system) for debugging purposes. In order to archive all the files into the same folder, all the files obviously need to have different names so they don’t overwrite each other. Adding the date to the file name is a pretty good way of going about renaming the files as it guarantees uniqueness, and we’d like to have that information readily visible anyway. The exports were being kicked off by a macro, so the archive files needed to be created as part of this macro. To make this happen we’ll need to do the following:

1. Create a VBA Module that contains a function to create our dynamic name, and save the export file to a specified location.

2. Modify/Create a Macro that will kick off our VBA Module.

These instructions are for Access 2010, however I originally did this in Access 2003. Aside from the subtle differences of where items are located in the menu etc. I can verify that this method will work for both 2003 and 2010.

So let’s get to it.

1.  First thing we want to do is create a new Module in Access. Click on the “Create” tab at the top of the Access window. There should be a “Macros & Code” section available once you’re on this tab. Click “Module” (highlighted in yellow below) to bring up the “Microsoft Visual Basic for Applications” window.

clip_image002[8]

2.  Once the editor opens up it should automatically have created a Module named “Module 1” and opened up a code editor window for the module that’s blank aside from the text “Option Compare Database”. We want to add the following code directly below that text:

Public Function DynamicFileNameExampleFunction()

Dim Current_Date As String

Dim File_Name As String

Current_Date = Format$(Now(), “yyyy\-mm\-dd hhmmssAMPM”)

File_Name = “Z:/ExampleFolder/ExampleExport-” + Current_Date + “.xls”

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, “TestTable“, [File_Name]

End Function

There are a few things you’ll need to modify in the code to fit your particular situation. Use the highlighted colors in the code above and the key below to modify your code as needed.

· The name of the view or table you want to call for your export file.

· The location/path you want to save your export file at. (This path must exist. It will not auto create a folder that doesn’t exist.)

· The file name you want your export saved as. (The date and time will be added after this.)

Once completed your editor should look like this:

clip_image004[8]

 

3.  Save the module, however DO NOT name the Module the same name as the function (DynamicFileNameExampleFunction in our example). Naming the Function and the Module the same name will cause your macro call to fail later on. I’m going to name our Module “TestModule”.

clip_image005[8]

Once the Module is saved, close the VBA Module editor so that you’re back on the main Access screen. We should now see our new Module in the Objects window as seen below.

clip_image006[8]

 

3.  All that’s left to do now is create/edit our macro to call our function in the VBA Module. The create Macro button was directly to the left of our create Module button in Step 1. Once the Macro designer is up you’ll want to select “Run Code” for the action, and then type in our function name “DynamicFileNameExampleFunction()” as the code to run. Once completed, your Macro should look like the screen shot below.

clip_image007[8]

 

5. Now just save your Macro and double click on it to run it. Each time your macro is run, you should create a new export file with the date and time appended to the name. I clicked mine 3 times in a row just to generate a few examples.

clip_image009[8]

Not too difficult, but while I was researching how to do this I came across a lot of people needing a similar solution. Hope this helps out.

 

How to reference a Stored Procedures name dynamically

When executing custom logging from within a procedure, you almost always want to include the name of the procedure that actually executes the logging action, be it an error, row counts, specific events or whatever it is you're wanting to keep tabs on. A great way of getting the procedure name without having to hardcode it (horrible idea) into the procedure is to use @@PROCID and OBJECT_NAME()to retrieve the name of the procedure. This way you never have to worry about a hardcoded "procedure name" value falling out of sync with the actual name of the procedure executing the logging statement. As an example, the following code creates a procedure named "usp_ProcIDTest" and then calls it.

CREATE PROCEDURE usp_ProcIDTest

AS

SELECT OBJECT_NAME(@@PROCID) 'ProcName'

GO

EXEC usp_ProcIDTest

The result of this procedure being called would be the procedures name

clip_image001[4]

While a procedure that returns its own name isn't particularly useful, one can see how this would be pretty handy if we were making an insert into our logging table. You could even create a separate logging procedure that you just pass the @@PROCID value into....I'll leave that ball in your court though.

 

The basics of good table design

When you're designing tables for a new database or schema, there are a few things that EVERY table should have. A little forethought can save you a ton of time, and a lot of data cleanup/scrubbing down the road. Anyway figured I'd run down a quick list of things I always do....and that the inherited DB I'm currently working on sorely lacks.

1. Every table should have CreatedDate and ModifiedDate columns. These columns should both be set to a default value of the date and time the row was created. In SQL Server this can easily be done by setting the default to GETDATE(). In addition the ModifiedDate should be updated by any procedure or query that updates any value for the row. This information is invaluable if you need to track down when things went wrong in your DB

2. Every table should have CreatedBy and ModifiedBy columns. Even if you're the only user in the system, add the columns to your tables. It's surprising how often a system built for 1 turns into a system used by many.

3. Define a primary key/unique constraint on every table. Just the exercise alone of doing this can help you understand your data better, not to mention it prevents duplicates.....a very common problem.

4. Build relationship constraints between your tables. This helps prevent orphaned data in your database...again a very common problem. It also makes your database much easier for someone other than you to understand. The very exercise of defining the relationships will also solidify your knowledge of the data you're dealing with, and maybe even uncover some oversights.

5. Keep Null values out of your database when possible. If it's required information, let the database know that by having the column not allow NULLs, or by assigning a default value if appropriate.

I wanted to keep this list pretty short, but these 5 things will go a LONG way to improving data integrity in your database. They will also help anyone tasked with implementing enhancements or fixing bugs in the future.