ChrisO
Registered User.
- Local time
- Tomorrow, 01:56
- Joined
- Apr 30, 2003
- Messages
- 3,202
G'day all.
Aim…
To overload the internal Access MkDir Statement with a user defined MkDir Function.
Forward and Definitions…
1.
The term overloading is used here in the context of priority of scope. In VBA, when a user written Public Procedure (Subroutine or Function) has the same name as an internal Access Procedure then the user written Public Procedure takes priority. Since in VBA the overloading is only done on a Name match it, the term overloading, does not comply with the strict term other languages such as C++ may use. These other languages may define it as Name + Argument List but VBA does not. VBA simply uses the Name. Therefore, we can say that VBA is unrefined, or more general, overloading but overloading nevertheless.
2.
This overloading does not remove the use of the internal Access Procedure. While an internal Access Procedure is overloaded it can still be used by calling VBA.ProcedureName. It is important to recognize the difference. For example: If we were to overload the Access MsgBox Function and the overload raised an error we would not want to call MsgBox in the error handler. What we can do is call the internal Access MsgBox Function as VBA.MsgBox. This prevents a circular reference to the overloaded MsgBox Function. Note here that the use of VBA. restricts the scope of the compiler to the internal Access MsgBox Statement.
Requirements…
As written the following is required: -
1. Access 2000 or later.
Reason…
The internal MkDir Statement has some limitations and failings, namely:
1. It can only make one (1) directory level at a time.
2. If the directory already exists an error will be raised.
3. If point two (2) raises an error then more code needs to be added to check that circumstance.
4. It can create a directory with trailing space(s). Not good.
Method…
A user defined Function is created with the same name as the internal Access Statement, namely MkDir. Due to 'scoping' the user defined Function takes priority over the internally defined MkDir Statement. Hence, when the compiler directive #Const conOverloadMkDir is set to True, in Module 'mdlOverloadMkDir', then the user defined Function is invoked else the internally defined MkDir Statement is invoked. The user defined MkDir Function calls the API Function 'MakeSureDirectoryPathExists' and no references are required.
If the internal Access MkDir is overloaded it, the internal MkDir Statement, can still be called by qualifying it as VBA.MkDir.
A demo to overload the internal Access MkDir Statement is attached for testing. If you are happy with that then import Module 'mdlOverloadMkDir' into your Project. If you then find a problem either simply set the compiler directive #Const conOverloadMkDir to False or delete the Module.
Execution…
(Yes, these are valid directories)
Access VBA usage:
Access SQL usage:
SELECT DirectoryName, Comments, MkDir([DirectoryName]) AS [Make Directory Status] FROM tblTestMkDir
About the code…
1. It fundamentally uses the API call MakeSureDirectoryPathExists. (Resource #1)
2. It parses the Path argument to force compliance with both the internal Access MkDir Statement and the API call MakeSureDirectoryPathExists Function.
3. It removes the ability of the internal Access MkDir Statement to make a directory with trailing space(s) like 'C:\Test \'. You really don’t want to do that because, as Dave (gemma-the-husky) points out, you will waste a lot of time trying to get rid of it.
4. Probably only useful for users of non-English versions of Access. When the overload Function returns Success or Failure it returns an Integer. If it returned a Boolean then that Boolean might get typecast to a string, for example: -
If a Boolean was returned and its numerical value was -1 then True would be inserted as in…
strSQL = "SELECT * FROM [TableName] WHERE [FieldName] = " & MkDir("C:\test")
in the English version of Access we would get…
SELECT * FROM [TableName] WHERE [FieldName] = True
but in the Dutch version of Access we would get…
SELECT * FROM [TableName] WHERE [FieldName] = Waar
and that fails.
So the overload MkDir Function returns a Variant of type Integer so we get…
SELECT * FROM [TableName] WHERE [FieldName] = -1
and that works in both language versions.
5. The code enforces VBA where necessary. The reason for that is that we do not know if any other VBA Procedure has been overloaded. (Please see point #2 in Forward and Definitions…) The exception is that we want the overloading MkDir Function to return a value so we use the overloaded Name directly as in MkDir = intSuccess.
6. The overload Function can currently be supplied with four (4) return Error Mode arguments (Defaults to zero (0) )
a. 0 = Silent (No internal messages) Return Integer Success or Failure (-1=Success/True) (0=Failure/False)
b. 1 = Silent (No internal messages) Return Error Description
c. 2 = Display internal messages Return Integer Success or Failure (-1=Success/True) (0=Failure/False)
d. 3 = Display internal messages Return Error Description
However, since the code can modify the passed Path, in order to comply with both the internal Access MkDir Statement and the API MakeSureDirectoryPathExists Function, then the Directory(s) created may not be exactly the same as the Path argument passed. So you may feel the option to create a fifth (value 4) argument to return the successful Path as created.
Have fun testing…
Regards,
Chris.
Updates...
Version 1 (11-Mar-2010) (Access 2000)
Resources…
1.
MakeSureDirectoryPathExists
http://allapi.mentalis.org/apilist/MakeSureDirectoryPathExists.shtml
Search Key Words…
MkDir Overload Overloading Scope
Aim…
To overload the internal Access MkDir Statement with a user defined MkDir Function.
Forward and Definitions…
1.
The term overloading is used here in the context of priority of scope. In VBA, when a user written Public Procedure (Subroutine or Function) has the same name as an internal Access Procedure then the user written Public Procedure takes priority. Since in VBA the overloading is only done on a Name match it, the term overloading, does not comply with the strict term other languages such as C++ may use. These other languages may define it as Name + Argument List but VBA does not. VBA simply uses the Name. Therefore, we can say that VBA is unrefined, or more general, overloading but overloading nevertheless.
2.
This overloading does not remove the use of the internal Access Procedure. While an internal Access Procedure is overloaded it can still be used by calling VBA.ProcedureName. It is important to recognize the difference. For example: If we were to overload the Access MsgBox Function and the overload raised an error we would not want to call MsgBox in the error handler. What we can do is call the internal Access MsgBox Function as VBA.MsgBox. This prevents a circular reference to the overloaded MsgBox Function. Note here that the use of VBA. restricts the scope of the compiler to the internal Access MsgBox Statement.
Requirements…
As written the following is required: -
1. Access 2000 or later.
Reason…
The internal MkDir Statement has some limitations and failings, namely:
1. It can only make one (1) directory level at a time.
2. If the directory already exists an error will be raised.
3. If point two (2) raises an error then more code needs to be added to check that circumstance.
4. It can create a directory with trailing space(s). Not good.
Method…
A user defined Function is created with the same name as the internal Access Statement, namely MkDir. Due to 'scoping' the user defined Function takes priority over the internally defined MkDir Statement. Hence, when the compiler directive #Const conOverloadMkDir is set to True, in Module 'mdlOverloadMkDir', then the user defined Function is invoked else the internally defined MkDir Statement is invoked. The user defined MkDir Function calls the API Function 'MakeSureDirectoryPathExists' and no references are required.
If the internal Access MkDir is overloaded it, the internal MkDir Statement, can still be called by qualifying it as VBA.MkDir.
A demo to overload the internal Access MkDir Statement is attached for testing. If you are happy with that then import Module 'mdlOverloadMkDir' into your Project. If you then find a problem either simply set the compiler directive #Const conOverloadMkDir to False or delete the Module.
Execution…
(Yes, these are valid directories)
Access VBA usage:
Code:
MkDir "C:\AAA\Tom\Test\Test\ and some junk name like O'Brien + 12.34"
Call MkDir("C:\AAA\Tom\Test\Test\ and some junk name like O'Brien + 12.34")
Assign return status:
Me.txtMakeDirectoryStatus = MkDir("C:\AAA\Tom\Test\Test\ and some junk name like O'Brien + 12.34")
Me.txtMakeDirectoryStatus = MkDir(Me.txtDirectoryName)Access SQL usage:
SELECT DirectoryName, Comments, MkDir([DirectoryName]) AS [Make Directory Status] FROM tblTestMkDir
About the code…
1. It fundamentally uses the API call MakeSureDirectoryPathExists. (Resource #1)
2. It parses the Path argument to force compliance with both the internal Access MkDir Statement and the API call MakeSureDirectoryPathExists Function.
3. It removes the ability of the internal Access MkDir Statement to make a directory with trailing space(s) like 'C:\Test \'. You really don’t want to do that because, as Dave (gemma-the-husky) points out, you will waste a lot of time trying to get rid of it.
4. Probably only useful for users of non-English versions of Access. When the overload Function returns Success or Failure it returns an Integer. If it returned a Boolean then that Boolean might get typecast to a string, for example: -
If a Boolean was returned and its numerical value was -1 then True would be inserted as in…
strSQL = "SELECT * FROM [TableName] WHERE [FieldName] = " & MkDir("C:\test")
in the English version of Access we would get…
SELECT * FROM [TableName] WHERE [FieldName] = True
but in the Dutch version of Access we would get…
SELECT * FROM [TableName] WHERE [FieldName] = Waar
and that fails.
So the overload MkDir Function returns a Variant of type Integer so we get…
SELECT * FROM [TableName] WHERE [FieldName] = -1
and that works in both language versions.
5. The code enforces VBA where necessary. The reason for that is that we do not know if any other VBA Procedure has been overloaded. (Please see point #2 in Forward and Definitions…) The exception is that we want the overloading MkDir Function to return a value so we use the overloaded Name directly as in MkDir = intSuccess.
6. The overload Function can currently be supplied with four (4) return Error Mode arguments (Defaults to zero (0) )
a. 0 = Silent (No internal messages) Return Integer Success or Failure (-1=Success/True) (0=Failure/False)
b. 1 = Silent (No internal messages) Return Error Description
c. 2 = Display internal messages Return Integer Success or Failure (-1=Success/True) (0=Failure/False)
d. 3 = Display internal messages Return Error Description
However, since the code can modify the passed Path, in order to comply with both the internal Access MkDir Statement and the API MakeSureDirectoryPathExists Function, then the Directory(s) created may not be exactly the same as the Path argument passed. So you may feel the option to create a fifth (value 4) argument to return the successful Path as created.
Have fun testing…
Regards,
Chris.
Updates...
Version 1 (11-Mar-2010) (Access 2000)
Resources…
1.
MakeSureDirectoryPathExists
http://allapi.mentalis.org/apilist/MakeSureDirectoryPathExists.shtml
Search Key Words…
MkDir Overload Overloading Scope
Last edited:
