How to Register and Add References to a DLL in MS Access with VBA code

Here’s given an article which will guide you to "How to register DLL File in system using VBA" MS Access. This article supposed to store the DLL in the table in binary format and to extract that binary data into useful DLL File, which we discuss few steps later that how to store DLL in table in binary form.

This article is explained through an example here:-

Firstly create a Table in the Database which contains fields show below in fig: - 1.1. This table contains the fields as DLLName, DLLPath and DLLObject.

How to register and add reference of DLL through VBA code Fig-1.1

Fig-1.1

Create a form with one Button. When the user click on this Button it checks for the existance of the DLL file at the path which is specified in the table against the DLL. If no file is present at the path, then the DLL file is extracted from the table and written to the path and then automatically register the DLL file and automatically attach the reference to that DLL File. Fig: - 1.2 show the form

How to register and add reference of DLL through VBA code Fig-1.2

Fig-1.2

After click on the "Load DLL" button then message window will appear. This window show the "DLL registered successfully" . As shown in fig: - 1.3

How to register and add reference of DLL through VBA code Fig-1.3

Fig-1.3

In Database DLL file store in Binary form. For convert the DLL file in binary form, we use a function "LoadPicIntoDatabase". This function has 6 parametres "file path","Table Name","field Name", "primary field Name","Primary filed value" and "primary field type".

For run the function press "CTRL + G" for open immediate window. Now run the function by passing 6 parameter in the function. If this function return the "TRUE" then your DLL file convert in Binary form and store in the table. Fig: - 1.4 show the run function in immediate window.

How to register and add reference of DLL through VBA code Fig-1.4

Fig-1.4

After run the "LoadPicIntoDatabase" function DLL file saved in the database in Binary form. After saving data in table field value show "Long Binary Data" .As shown in fig: -1.5

How to register and add reference of DLL through VBA code Fig-1.5

Fig-1.5

Here is the VBA Code provided for Store DLL file in Table in binary form:-

Public Function LoadPicIntoDatabase(sFilePathAndName As String, TableName As String, FieldName As String, PrimaryFieldName As String, PrimaryFieldValue As Variant, PrimaryFieldType As String) As Boolean
On Error GoTo ErrHandler
'Test to see if the file exists. Exit if it does not.
If Dir(sFilePathAndName) = "" Then Exit Function
LoadPicIntoDatabase = True
'Create a connection object
Dim cn As ADODB.Connection
Set cn = CurrentProject.Connection
'Create our other variables
Dim RS As ADODB.Recordset
Dim MStream As ADODB.Stream
Dim strQry As String
strQry = "SELECT TOP 1 " & PrimaryFieldName & "," & FieldName & " FROM " & TableName & " WHERE " & PrimaryFieldName & " = " & PrimaryFieldValue
Set RS = New ADODB.Recordset
'Configure our recordset variable and open only 1 record (if one exists)
With RS
.LockType = adLockOptimistic
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Open strQry, cn
End With
'Open our Binary Stream object and load our file into it
Set MStream = New ADODB.Stream
MStream.Open
MStream.Type = adTypeBinary
MStream.LoadFromFile sFilePathAndName
'add a new record and read our binary file into the OLE Field
RS.Fields(FieldName) = MStream.Read
RS.Update
'Edit: Removed some cleanup code I had inadvertently left here.
Cleanup:
On Error Resume Next
RS.Close
MStream.Close
Set MStream = Nothing
Set RS = Nothing
Set cn = Nothing
Exit Function
ErrHandler:
MSgBox "Error: " & Err.Number & " " & Err.Description
LoadPicIntoDatabase = False
Resume Cleanup
End Function

Here is the VBA Code provided for register the DLL files using VBA :-

Option Compare Database
Dim IsWriteSuccessful As Boolean
Private Sub Command0_Click()
Dim rsR As Recordset
Dim strTargetPath As String
Set rsR = CurrentDb.OpenRecordset("SELECT * From DLLReference")
If Not rsR.EOF And Not rsR.BOF Then
rsR.MoveFirst
While Not rsR.EOF
If Len(Dir(rsR!DLLPath, vbDirectory)) = 0 Then
strTargetPath = rsR!DLLPath
IsWriteSuccessful = BlobToFile(strTargetPath, rsR("DLLObject"))
RegisterCmd = "regsvr32 " & """" & strTargetPath & """"
Call Shell(RegisterCmd, vbHide)
With Access.References
.AddFromFile strTargetPath
End With
End If
rsR.MoveNext
Wend
End If
MSgBox "DLL registered successfully"
End Sub

Code for "BlobToFile" function used for extracting DLL File from the binary data :-

Public Function BlobToFile(strFile As String, ByRef fldField As Object) As Long
On Error GoTo Err_BlobToFile
Dim intFileNum As Integer
Dim abytData() As Byte
BlobToFile = 0
intFileNum = FreeFile
Open strFile For Binary Access Write As intFileNum
abytData = fldField
Put #intFileNum, , abytData
BlobToFile = LOF(intFileNum)
Exit_BlobToFile:
If intFileNum > 0 Then Close intFileNum
Exit Function
Err_BlobToFile:
MSgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Error writing file in BlobToFile
" BlobToFile = 0
Resume Exit_BlobToFile
End Function


DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.