Can a Query call a Statement?

[ChrisO]

G’day all.

Can a Query call a Statement, meaning; can a Query call a Subroutine such as MkDir?

I can’t find a way to do it but need to make sure that they can’t.

Regards,
Chris.

 

[pbaldy]

A query can call a public function, and that function could do the MkDir. A brief test just worked, though I might not be duplicating the process you want to perform. To my knowledge, it couldn't call the MkDir directly.

 

[ChrisO]

Thanks for the reply Paul.

I want to make certain that MkDir can’t be called directly as a Subroutine from a Query because I want to overload the MkDir Subroutine with a MkDir Function which can be called from a Query.

Overloading meaning replace the internal Access MkDir Subroutine with a user written MkDir Function which can be called from a Query.

As you are probably aware, it’s a scope thing.

Edit to add:
Yes I want to re-write the internal MkDir subroutine.


Regards,
Chris.

 

[georgedwilkinson]

Oh, you have my curiosity up!

 

[pbaldy]

We can always count on Chris to come up with something "out of the box".

 

[ChrisO]

As it stands at the moment but I need to write it up...

'*******************************************************************************
'*                                                                             *
'*               Please leave any trademarks or credits in place.              *
'*                                                                             *
'*                  This code, Overload MkDir, by ChrisO via: -                *
'*                http://www.access-programmers.co.uk/forums/                  *
'*                    Original posting date: 9-Mar-2010                        *
'*                                                                             *
'*******************************************************************************

Option Explicit
Option Compare Text


' Set to False if you do NOT want to
' overload the Access MkDir statment.
#Const conOverloadMkDir = True


' KPD-Team 2000
' URL: http://www.allapi.net/
' E-Mail: KPDTeam@Allapi.net
Private Declare Function MakeSureDirectoryPathExists Lib "imagehlp.dll" (ByVal lpPath As String) As Long


#If conOverloadMkDir Then
Public Function MkDir(ByVal vntPath As Variant) As Boolean
    ' Author       : ChrisO (http://www.access-programmers.co.uk/forums/)
    ' Date         : 9-Mar-2010
    ' Revision     : None
    ' Contributors : KPD-Team 2000 (URL: http://www.allapi.net/)
    Dim lngPos     As Long
    Dim strChr     As String
    Dim blnSuccess As Boolean
    
    ' Check for Null or ZLS.
    If Len(vntPath) Then
        ' Set the initial return valve.
        blnSuccess = True
        
        ' The standard MkDir works with a '/', the API does not.
        vntPath = Replace(vntPath, "/", "\")
        
        ' Scan the Path for invalid characters.
        For lngPos = 1 To Len(vntPath)
            Select Case Mid$(vntPath, lngPos, 1)
                Case "*", "?", Chr$(34), "<", ">", "|"
                    ' Set the return value to not valid.
                    blnSuccess = False
                    MsgBox "Character '" & Mid$(vntPath, lngPos, 1) & "' not allowed in Directory name." & vbNewLine & _
                    "Directory was not created.", _
                    vbCritical + vbOKOnly, _
                    "Create Directory Failure"
                
                ' Check for ':' other than following the drive letter.
                Case ":"
                    If lngPos <> 2 Then
                        ' Set the return value to not valid.
                        blnSuccess = False
                        MsgBox "Character ':' only allowed after Drive letter." & vbNewLine & _
                        "Directory was not  created.", _
                        vbCritical + vbOKOnly, _
                        "Create Directory Failure"
                    End If
                    
                    ' Check if ':' is followed by '\'
                    If lngPos <> Len(vntPath) Then
                        If Mid$(vntPath, lngPos + 1, 1) <> "\" Then
                            ' Set the return value to not valid.
                            blnSuccess = False
                            MsgBox "Character '\' required after Drive letter and ':'" & vbNewLine & _
                            "Directory was not  created.", _
                            vbCritical + vbOKOnly, _
                            "Create Directory Failure"
                        End If
                    End If
            End Select
            
            ' Exit loop on not valid.
            If Not (blnSuccess) Then Exit For
        Next lngPos
    
        ' If valid so far.
        If (blnSuccess) Then
            ' Add last '\' if not supplied.
            If Right(vntPath, 1) <> "\" Then vntPath = vntPath & "\"
            
            ' Check if Drive letter specified and path length is NOT correct.
            If InStr(vntPath, ":") And Len(vntPath) < 4 Then
                ' Set the return value to not valid.
                blnSuccess = False
                MsgBox "Incorrect Directory length after Drive letter." & vbNewLine & _
                "Directory was not  created.", _
                vbCritical + vbOKOnly, _
                "Create Directory Failure"
            Else
                ' Try creating the directory and return the result.
                ' If Drive letter is not specified,
                ' create the directory relative to current directory.
                ' A return value of 0 from the API is a failure.
                blnSuccess = Not (MakeSureDirectoryPathExists(vntPath) = 0)
            End If
        End If
    End If
    
    ' Return the result.
    MkDir = blnSuccess

End Function
#End If

Work in progress...

 

[ChrisO]

First draft……………….

Overloading the MkDir Statement.

G’day all.

A demo to overload the internal Access MkDir Statement is attached.


Requirements…
As written the following is required: -
1. Access 2000 or later. It uses the Replace() Function.


Aim…
To replace the internal Access MkDir Statement with a user defined Function.


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 = True 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 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.


Reason…
The internal MkDir has some limitations, 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.


Execution…
(Yes, they are valid directories)
Access VBA usage:

MkDir "C:\Tom\Test\Test\      slkjfsklf  '  lkjdljalsd" 
Call MkDir("C:\Tom\Test\Test\       slkjfsklf  '  lkjdljalsd")
Assign return status:
Me.txtMakeDirectoryStatus = MkDir("C:\Tom\Test\Test\  slkjfsklf  '  lkjdljalsd")
Me.txtMakeDirectoryStatus = MkDir(Me.txtDirectoryName)

Access SQL usage:
SELECT tblTestMkDir.DirectoryName, tblTestMkDir.Comments, MkDir([DirectoryName]) AS [Make Directory Status] FROM tblTestMkDir

Regards,
Chris.


Resources…
1.
MakeSureDirectoryPathExists
http://allapi.mentalis.org/apilist/MakeSureDirectoryPathExists.shtml


Updates...
First release Version 1 (9-Mar-2010)


Search Key Words…
MkDir Overload

 

[gemma-the-husky]

chris

it seems strange to change mkdir

why not just create a mymkdir function, and replace any instances of mkdir in your code with that. that way, you retain the oriinal functionality if you need it.

you could even have an optioinal flag in the new routine to just use the original routine. Seems more flexible.


=========
function mymkdir(newdir as string; optional oldmethod as boolean = false) as boolean

if oldmethod then
mkdir(newdir)
exit function
end if

... otherwsir do your new thing

end function

 

[ChrisO]

>>it seems strange to change mkdir<<

Why do you think I’m changing MkDir? It’s still there for those who wish to use it.
I’m overloading the MkDir Statement with a MkDir Function.

If you don’t like it, don’t understand it or don’t want to use it then fine, don’t.
If you have questions then ask.

But given that overloading in VBA is not common did you even try it and, if so, did you find an error?
It’s there for destructive testing not for knocking something that others might find useful.

function mymkdir(newdir as string; optional oldmethod as boolean = false) as boolean

if oldmethod then 
mkdir(newdir)
exit function
end if

... otherwsir do your new thing

end function

Do you really think I would write something like that? Have I ever?

The attachment I wrote took ~6 hours; it was not a quick ‘dash off to site’.

Please do others the courtesy of taking the same time to think about it, test it and write something that they might find useful.

 

[vbaInet]

Getting a bit stressy there ChrisO

The follow-up to that statement sounded like a suggestion or an idea. I doubt if Dave was chastising you.

 

[ChrisO]

The problem is that the idea in no way presents the notion of overloading a Statement/Function in VBA and that is what it is all about.

What other ideas may come of it? What other ideas may be lost by some normal, counter reply or method? Given the quality of the reply, why should anyone expect anything else? Is it really trying to expand the knowledge of others? What value is there in the status quo when it stifles something comparatively new? What good is it? What good does it do anyone?

Stressy?
It happens every time; something new to a reader invokes negativity even when, and perhaps because of, they don’t understand it. The only safe way to post on the net is to quote the status quo as chanted. Don’t make waves, don’t try anything that might be a little unusual, don’t dare challenge the status quo.

But when the dust settles and the guru’s (they’re the people who sit on a mountain in Tibet) come to grips with it, it starts to get used (note I didn’t say plagiarized but it happens). The next step is that the status quo softens. When that happens it becomes more sociably acceptable so others are now allowed to think differently, but still within the new bounds of the status quo.

34 years programming, the last 30 professionally, and it still happens.
Why have to fight to try and bring something a little different?

function mymkdir(newdir as string; optional oldmethod as boolean = false) as boolean

if oldmethod then 
mkdir(newdir)
exit function
end if

... otherwsir do your new thing

end function

I can take that sort of thing apart in my sleep.

Stressy?
No, way past that, now it’s just bollocks.

 

[vbaInet]

34 years!!! That's a long career Chris. Dave probably didn't pick up on the fact that you were effectively overloading the function as opposed to overwriting it.

Would subclassing also work in this case? Just wondering.

 

[ChrisO]

How were you thinking of sub-classing the MkDir Statement?

I have no knowledge of sub-classing internal Access Statements but if you can do it then go for it. However, if it’s substantially different from overloading then it would become an entirely new subject and not replace the overloading which, so far as tested, already works.

So, to me, a new subject would equate to a new thread.

(This site has been slow in the last hour…must be provider backup time. If it doesn’t get better soon I’ll kiss you goodnight. That reminds me of a movie ‘The Long Kiss Goodnight’)

Stuff it; it’s too slow to be usable…goodnight.

 

[gemma-the-husky]

wow

Chris, I must have upset you in an earlier life.

OK, so replace wasnt exactly the correct word.

===============
It just seemed strange to me to actually go to the trouble to define a function to replace an existing function - when you could get the same result by giving it a different name.

I thought about it again, and I have messed around with an alternative idea, which I shall post shortly.


=================
Before doing so, I have a question

I tried with your code, using leading and embedded spaces, to create

C:\Tom\Te st\Te st\ slkjfsklf ' lkjdljalsd

which worked fine


however with trailing spaces, it didnt work fine

C:\ Tom \ Test \T es\ slkjfsklf

returning false

=========
my variation of a mkdir has enabled me to create folder names With trailing spaces

so, i now have created a folder called

[folder 11 ] - with 3 trailing spaces

but i cannot delete or change this now as I get "Cannot Delete From specified disk." I cannot even delete the folder in DOS cmd window.

Before I go any further, I want to confirm if anyone knows a way of getting rid of this file.

======
because of this, I will modify my code to include a routine to trim the folder names of any folders/subfolders that are created - to avoid these errant spaces.

 

[vbaInet]

How were you thinking of sub-classing the MkDir Statement?

I have no knowledge of sub-classing internal Access Statements but if you can do it then go for it. However, if it’s substantially different from overloading then it would become an entirely new subject and not replace the overloading which, so far as tested, already works.

So, to me, a new subject would equate to a new thread.

(This site has been slow in the last hour…must be provider backup time. If it doesn’t get better soon I’ll kiss you goodnight. That reminds me of a movie ‘The Long Kiss Goodnight’)

Stuff it; it’s too slow to be usable…goodnight.

Subclassing wouldn't be an easy job at all. It's the referencing that's the difficult bit. I may look into that at some point just for the sake of it. Yes, it would require a new thread if I get to doing it.

That's the movie with Samuel L J right!! Can't really remember much about it.

Cheerios.

 

[ChrisO]

Dave.

I don’t want to strip out any characters which are valid in a Windows directory.

The aim of the internal MkDir Statement is to make a valid directory and that includes spaces and most other junk we can throw at it.

But one of the problems with the internal MkDir Statement is it simple raises an error when it encounters an incorrectly formatted directory name. Another problem with it is it can only make one level of directory at a time. If we try to make more than one level it errors on that as well. It also errors if a directory already exists or if we are appending onto an existing directory.

:, \, /, *, ?, “, <, >, | these characters are invalid in a directory name but two of them : and \ are required if we want to create a root directory or multiple directories. But some configurations are invalid such as C: or C:Fred but C:\\\Fred is valid. So far I’ve put in a lot of time test for invalid directories and I think I’ve got most of them. I haven’t, as yet, tested for UNC paths but they should work.

Now the reason for overloading the MkDir name is that the Module should be able to be imported into any existing project without making any other change. If we were to do it with a different name we would need to rename the existing MkDir names. However, with the compiler directive #Const conOverloadMkDir we can turn it on or off to test the difference between the standard and overloading version.

The overloading makes it easier and safer for testing and for reverting back if required.

So no, I don’t want to change anything unless it’s a bug or a shortcoming.

Edit to add
Dave, thanks for the testing and I will force the trim of trailing spaces.

 

[gemma-the-husky]

Chris

I have now spent some time looking at this. The principle of creating all the folders in the path seems a good one, and I wrote something to do it, by using the original mkdir to create the folders, one at a time. I also wrote code to take out all leading/trailing spaces from each folder specified in a file path

but on close examination, and unless I am missing something, this all seems to be done anyway, by the library function MakeSureDirectoryPathExists (except the stripping out the spaces bit)

I was completely unaware of the existence of this useful function - until I examined your code carefully to understand how you were actually forming the folders - and I am still somewhat uncertain exactly what your version adds to this. Is it just pre-vetting the structure of the complete path?

I haven't tested the MakeSureDirectoryPathExists, Doesn't that function do all that for you?


I appreciate that doing it this way enables your existing code to use the changed functionality without needing to search and replace the MkDir statements - although you may need to anyway, if they are just creating single folders at a time.



==========
ok here's my code which takes a string

"c:\ tom folder \ fred folder \ bill folder \ ", and strips out all leading trailing spaces to leave

"c:\tom folder\fred folder\bill folder\"


as I mentioned in the course of this, I managed to create some folders with trailing spaces, called eg "new folder 14 " - and I am unable to delete them at all , because of the trailing spaces. Not to worrry.

function massage(dstrg as string) as string

'these variables used to massage folder string, and strip out all embedded leading/trailing spaces - note the exit function is where it is, because I originally included this as a gosub/return.


Dim pos As Long
Dim startfrom As Long
Dim lseg As String
Dim mseg As String
Dim rseg As String

'parse the string and clear out leading/trailing spaces in each folder
'so "c:\ some folder  \ some folder 2 \
'becomes
'so "c:\some folder\some folder 2\

'this is a bit verbose because instr works slightly differently if starting form position 0

'I also ended up treating the string as three segments as the compiler was objecting to doing it all in one line - the trim(mid) usage I think, but maybe I 'had the syntax off slightly

'the left segment upto the first \ we are checking
'the middle segment which is the current folder
'the right segment after the second \ we are checking


        startfrom = 0
        
        While True
            If startfrom = 0 Then
                pos = InStr(dstrg, "\")
            Else
                pos = InStr(startfrom, dstrg, "\")
            End If
            
            If pos > 0 Then
                If startfrom = 0 Then
                    lseg = ""
                Else
                    lseg = Trim(Left(dstrg, startfrom - 1))
                End If
                
                If startfrom = 0 Then
                    mseg = Left(dstrg, pos - 1)
                Else
                    mseg = Trim(Mid(dstrg, startfrom, pos - startfrom))
                End If
                
                rseg = Trim(Mid(dstrg, pos))
                
                dstrg = lseg & mseg & rseg
                pos = InStr(startfrom + 1, dstrg, "\")
            
            Else
                If startfrom = 0 Then
                    lseg = ""
                Else
                    lseg = Trim(Left(dstrg, startfrom))
                End If
                
                rseg = Trim(Mid(dstrg, startfrom + 1))
                
                dstrg = lseg & rseg
                
[COLOR="Red"]               massage = dstrg
               exit function[/COLOR]
            End If
            startfrom = pos + 1
        Wend

end function

 

[ChrisO]

MakeSureDirectoryPathExists can not create an invalid Windows directory.

When it fails the MakeSureDirectoryPathExists Function returns 0. At that point we would need to use another API call to get the error number and perhaps another API call to get the error description.

The pre-vetting is being done to flag the possible error before the MakeSureDirectoryPathExists raises it. This has to be done because I want the overloading MkDir Function to behave with the same syntax as the internal MkDir Statement.

Example: The internal MkDir Statement can handle MkDir "C:/Fred/" but a direct call to MakeSureDirectoryPathExists fails. Therefore, pre-vetting is required so that the MakeSureDirectoryPathExists call can perform the same as the internal MkDir Statement at least in that instance.

To me it is a requirement of overloading that the new method performs at least as well as the old method. We don’t want the old methods shortcomings or its errors. What we want is the equivalent correct functionality plus any enchantments.

And by the way, when testing the internal MkDir Statement I too got a directory which now can’t be deleted. I’ve modified the overloaded version to remove any space before the next ‘\’ and that seems to prevent the problem.

So that too is worthwhile in that it behaves better than the MkDir Statement.

 

[gemma-the-husky]

Ah - I see

I think this is a FAR simpler alternative then, which I came across

Just use DIR command to see if the folder already exists

dir(wholestring,vbdirectory)

this will return a null string if it doesnt (which we expect)

but produces a trappable vba error if the string is not a valid dir.
or even if the drive is not available.

vba error 68 for drive not available
vba error 76 for illegal characters

I couldnt find/achieve any other error levels despite trying a lot of alternatives
==============
my code just creates folders one at a time with vba.mkdir

i was using the dir on each bit, so I didnt recreate folders that already exist
eg - you want to create c:\tom\apps\new folder\data

but the folder c:\tom\appps already exists

so i was testing with dir

the whole thing - which turned out to validate an illegal file name

but then
c:\tom
c:\tom\apps
c:\tom\apps\new folder
c:\tom\apps\new folder\data

to determine which ones i need to create. And since the whole string is valid

===========
Finally, I just tried this [a couple of moments ago] to see the effect - and my version
"c:\folder\\\ 15\final\ " ignores the superfluous \ characters - it actual determines that there is no folder to create in between the sets of \\ chars, and just goes on to the next one.

and just creates

"c:\folder\15\final"


I suppose you could just search for \\ together, and reject the string at the outset.

============
would this be useful to you?

 

[ChrisO]

All that stuff has been around for years, I’ve seen it many times by many different people. I have used it myself over the past 14 years. I’m trying to overload the internal statement; I’m not looking to simple rehash stuff that has already been done.

I’m not interested in that method, thanks.