Sometimes projects can surprise you with unexpected requirements. One such case is when you need to prepare dynamic certificates with customizable images and texts, then print them for selected students. This task was solved by linking a .txt file generated by a VB.NET application to a report in Microsoft Access. Here's how it works.
🏗️ Step 1: Writing Certificate Data to a TXT File
When the Print button is clicked, the application writes all necessary variables into a text file called data.ini in the application directory. This file is later read by Access.
Dim objWriter As New System.IO.StreamWriter(GetDataDirectory("Files\data.ini"), False)
objWriter.WriteLine(pbLogo.ImageLocation) ' logo
objWriter.WriteLine(pbMuhur.ImageLocation) ' seal
objWriter.WriteLine(pbBackground.ImageLocation) ' background
objWriter.WriteLine(lblTitle.Text) ' title
objWriter.WriteLine(lblExplain.Text) ' subtitle
objWriter.WriteLine(lblDate.Text) ' date
objWriter.WriteLine(lblDirector.Text) ' director
objWriter.Close()
Each line in the file represents a different field of the certificate.
🧑🎓 Step 2: Preparing SQL and Opening the Access Report
Once the TXT file is created, the application gathers the selected students' IDs and opens the Access database with a specific SQL filter.
Dim sql = ""
For Each row As DataGridViewRow In dgvStudents.SelectedRows
sql = sql & " or ID=" & row.Cells(0).Value.ToString
Next
Dim MSA As New Access.Application
MSA.Application.Visible = True
MSA.OpenCurrentDatabase(GetDataDirectory("Access.mdb"), False)
MSA.Application.DoCmd.OpenReport("Report", Access.AcView.acViewPreview, , Mid(sql, 5), Access.AcWindowMode.acWindowNormal)
MSA = Nothing
The Mid(sql, 5) part skips the first " or " to ensure the SQL filter is valid.
📊 Step 3: Access Report Reads and Displays Data from the TXT File
The Report_Load method in Access uses VBScript to read the TXT file line by line and populate the report with values such as images and labels.
Private Sub Report_Load()
i = 0: Dim ogrenci() As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(CurrentProject.Path & "\data.ini", 1)
Do Until objFile.AtEndOfStream
strLine = objFile.ReadLine
Select Case i
Case 0 ' logo
If Dir(strLine) <> "" Then Image1.Picture = strLine
Case 1 ' seal
If Dir(strLine) <> "" Then Image2.Picture = strLine
Case 2 ' background
Image0.Picture = strLine
Case 3 ' title
Label1.Caption = CleanText(strLine)
Case 4 ' subtitle
Label2.Caption = CleanText(strLine)
Case 5, 6 ' date and location
Label3.Caption = Label3.Caption & CleanText(strLine) & vbCrLf
Case 7, 8 ' director
Label4.Caption = Label4.Caption & CleanText(strLine) & vbCrLf
End Select
i = i + 1
Loop
objFile.Close
Set objFile = Nothing
Set objFSO = Nothing
End Sub
Each line read from the file updates the corresponding element in the Access report, including images and text fields.
✅ Conclusion
This approach may seem unconventional but it offers a flexible solution for bridging desktop applications and Access reports. By using a simple TXT file as a shared medium, dynamic certificates can be generated with rich content including images and custom text, all triggered from a VB.NET application.
This method is particularly useful in educational or certification-based applications where reports must be generated in bulk for selected individuals.