Bolte IT Consulting

The following method exports all available queries in an Access database into individual text files, which are called like the corresponding query.

Source Code

DAO does not provide the support ADODB gives you, therefore this method will only work with queries created and stored in a current MS Access database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-15
' @Description - exports all queries available in current database query definitions.
' @Param trgDir - a String representing a target directory to export
' all queries in current query definition.
' @Returns true, if all queries have been successfully exported into provided target directory.
Function exportQueries(ByVal trgDir As String) As Boolean
    Dim sql As String
    Dim name As String
    Dim q As Object
    Dim trgFile As String
 
    ' Reference a query.
    For Each q In CurrentDb.QueryDefs
        ' Get the SQL from a referenced query as text.
        sql = q.sql
        ' Get a queries name.
        name = q.name
        ' Replace special characters in file name.
        trgFile = trgDir & "\" & VBATools.replaceSpecialCharacters(name) & ".sql"
        ' Delete the target file, if already existing.
        Call VBATools.deleteFileOnHD(trgFile)
        ' Write the query text into a separate text file.
        Call VBATools.writeLineToTextFile(trgFile, sql, False)
    Next
End Function

Resources

The method writeLineToTextFile is not a standard VBA method, but can be found at following URL.

The target encoding should not be UTF-16LE but ASCII, if you intend to use a versioning tool like GIT to keep track of changes in MS Access queries.

Write a String into a text file

Replacing special characters can be a pain, if you do not rely on regular expressions.

Replace special escape characters in String

Delete a file on a users hard disc (VBScript, which can easily be adjusted to VBA).

VBScript to delete file

Hits: 10971

If you have to code in VBScript or other unpopular languages, it is up to you which tools you use for developing.

Since I like to code in Java and I have grown to love Eclipse I had a look for an editor that would allow me to combine useful tools like eGit with editing source code in Eclipse directly.

I found one that looks ok so far and it supports all kinds of text based programming languages. Below you can find the standard set of langugaes the editor is shipped with. It can be extended by a vast amount of other languages.

  • C/C++
  • C#
  • CSS
  • HTML
  • Java
  • JavaScript
  • mySQL
  • Perl
  • PHP
  • Ruby
  • VBScript
  • VisualBasic
  • XML
    Hits: 21857

Whenever I have to write data into a text file following function has proven to fulfill my needs.

Source Code

In case you want to export a file in Unicode encoding you just hand True for the parameter isUtf16Le, which will cause the FileSystemObject to encode the file in UTF-16 with low byte order mark (little endian).

Hits: 21114

If you have to register or unregister dynamic link libraries (dll) or OLE Custom Controls (ocx) via VBA you will find the following two methods helpful. OLE Custom Controls are actually dlls implementing a certain interface, which allows them to be utilized as OLE objects in programming languages.

Source Code

Below methods utilize an elevated command line (cmd executed as administrator) in order to register dlls using the windows program regsvr32.exe. The following syntax is used to elevate the command line.

1
objShell.ShellExecute "cmd", "/cregsvr32.exe " & Chr(34) & dllPath & Chr(34), "", "runas"

The part after the first comma can be replaced with any command you want to run in an elevated command line. This is potentially dangerous as you could do nasty stuff with an elevated command line. However, at least the user will be asked starting with Windows Vista, if he / she wants to allow the command to modify the system.

In order for the function ShellExecute to run as expected we will need to pass the following parameters.

Param Value Comment
Handle "cmd" The cmd.exe, which path should be available from a systems path environment variable.
Command 
"/cregsvr32.exe " & Chr(34) & dllPath & Chr(34)
 "/c" must be placed at the beginning of the command String to be executed.
File "" We do not have a file to execute an operation on.
Parameters 
"runas"
 This is an undocumented feature of the method ShelExecute, which tells it to run the speciffied executable as Administrator.

If you want the executable regsvr32.exe to display a message dialog every time a dll has been registered or unregistered successfully, you will have to remove the parameter "/s" from the command line String to be executed.

The call to regsvr32.exe will work fine for 32 Bit systems. For 64 Bit operating systems this will not work, unless the dll to be registered is placed in the correct directory. The directories to place a dll in are as follows.

  • 32 Bit dll on 32 OS can be registered anywhere. You will not have to copy it to "C:/Windows/System32/".
  • 32 Bit dll on 64 Bit OS must be registered in "C:/Windows/SysWOW64/".
  • 64 Bit dll on 64 Bit OS should be registrable anywhere, but I did not test this. If anything you might have to copy it to "C:/Windows/System32/".

The call to regsvr32.exe is the same on 64 Bit and 32 Bit OS although there are two versions (32 Bit and 64 Bit) available on a 64 Bit OS. Windows will automatically determine if a dll is of 32 or 64 Bit and call the corresponding version of regsvr32.exe. However this only works, if the dll to be registered is placed in the correct directory (see above list).

Funny enough, the call of method ShellExecute does not work as expected, if the command is passed in a String variable. Maybe an issue to do with type conversion as it works fine if I pass the command variable declared as Variant.

The moethod is actually supposed to return a numeric value, but no matter if the call succeeds or fails it always seems to return 0, which would mean my system is out of resources. For now I will have to live without a proper error handling on this one.

Register DLL

The following method registers a dll or ocx.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-25
' @Description - Registers a dynamic link library (dll) available under provided path.
' The executable regsvr32.exe used to register the dll is set to silent, so no messages in case of failure or success are presented to a user.
' The method will return true, if calling the elevated command prompt succeeded.
' If registering the dll actually worked cannot be checked by this method,
' since this task is actually left to the elevated command prompt.
' @Param dllPath - a String providing the full file path to a dll or ocx.
' @Returns true, if registering the dll succeeded, else false.
Public Function registerDLL(ByVal dllPath As String) As Boolean
    Dim ret As Boolean
    Dim objShell As Object
    
    On Error GoTo errHandle:
    
    Set objShell = CreateObject("Shell.Application")
    objShell.ShellExecute "cmd", "/cregsvr32.exe /s " & Chr(34) & dllPath & Chr(34), "", "runas"
    
    ret = True
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
        ret = False
    End If
    
    registerDLL = ret
End Function

Unregister DLL

The following method unregisters a dll or ocx.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
' @Author - Alexander Bolte
' @ChangeDate - 2016-03-25
' @Description - Unregisters a dynamic link library (dll) available under provided path.
' The executable regsvr32.exe used to unregister the dll is set to silent, so no messages in case of failure or success are presented to a user.
' The method will return true, if calling the elevated command prompt succeeded.
' If unregistering the dll actually worked cannot be checked by this method,
' since this task is actually left to the elevated command prompt.
' @Param dllPath - a String providing the full file path to a dll or ocx.
' @Returns true, if unregistering the dll succeeded, else false.
Public Function unregisterDll(ByVal dllPath As String) As Boolean
    Dim ret As Boolean
    Dim objShell As Object
    
    On Error GoTo errHandle:
    
    Set objShell = CreateObject("Shell.Application")
    objShell.ShellExecute "cmd", "/cregsvr32.exe /u /s " & Chr(34) & dllPath & Chr(34), "", "runas"
    
    ret = True
    
errHandle:
    If Err.Number <> 0 Then
        Err.Clear
        ret = False
    End If
    
    unregisterDll = ret
End Function

Unit Tests

Below two simple unit tests.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
Sub testRegisterDll()
    Dim f As FileDialog
    
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    With f
        .Filters.Clear
        .Filters.Add "OLE Custom Control", "*.ocx"
        .AllowMultiSelect = False
        
        If .Show Then
            Call VBATools.registerDll(.SelectedItems(1))
        Else
            MsgBox "Registration of dynamic link libraries aborted.", vbExclamation, cDialogTitle
        End If
    End With
End Sub
 
Sub testUnregisterDll()
    Dim f As FileDialog
    
    Set f = Application.FileDialog(msoFileDialogFilePicker)
    With f
        .Filters.Clear
        .Filters.Add "OLE Custom Control", "*.ocx"
        .AllowMultiSelect = False
        
        If .Show Then
            Call VBATools.unregisterDll(.SelectedItems(1))
        Else
            MsgBox "Registration of dynamic link libraries aborted.", vbExclamation, cDialogTitle
        End If
    End With
 End Sub

References

SheelExecute

https://msdn.microsoft.com/en-us/library/windows/desktop/bb762153%28v=vs.85%29.aspx

Powershell and manual registration of dll, however the options provided in the methods at below URL will only work, if you are logged in as administrator and since I am admin on my machine, I am not too sure even then it will work without an elevated command prompt. I ran into the error code 0x80040201, which basically says that I have insufficient rights to register or unregister the dll (see second URL).

My solution still prompts the user with a confirmation message but at least it works as long as the user is allowed to elevate the command prompt as admin.

http://www.myengineeringworld.net/2013/06/register-unregister-dll-file-through-vba.html

https://blogs.msdn.microsoft.com/rajakedar_ganta/2012/06/06/dllregisterserver-failed-with-the-error-code-0x80040201/

The following URL finally gave me the hint I needed in order to get things running.

http://stackoverflow.com/questions/5944180/how-do-you-run-a-command-as-an-administrator-from-the-windows-command-line

Hits: 15321

If you want to remove line breaks or other characters from Oracle fields, the following article might help.

One scenario is for example export of data into text files.

SQL

1
translate(' example ', chr(10) || chr(13) || chr(09), ' ') 

The above will replace all line breaks (chr(10)), all tabs (chr(09)) and all carriage returns (chr(13)) with a space (' ').

The Oracle function 'translate' is applied on the whole String and might show better performance than regular expressions depending on the complexity of your regex.

Enjoy.

Hits: 21368