Inovative Interfacing of Excel and Access

Posted On August 2, 2007 by Sneha Philipose filed under Miscellaneous

The present age of information technology overshadows the earlier technologies mainly due to its versatile and vast application in all fields of life. The durable and elegant Microsoft Excel is one of the most popular spreadsheet applications. It is widely used in handling, inspecting and processing data. Excel as a spreadsheet is an effective tool to view a set of data in different ways. Manipulation and processing data is much more easier than in any common database package. This inherent strength of spreadsheet is widely used in the data-processing community mainly due to its menu driven approach, which requires less computer operational knowledge. In general spreadsheets are very strong in data processing and report generation, but it cannot store and share data. This constitutes the main weak point of the spreadsheet application.

Those who are familiar with spreadsheet often use multiple spreadsheets for different sets of data for the same purpose. The reason is obvious. Spreadsheet stores data together with all its attributes (like formulae and heading etc.) necessary for data processing and report generations. Whereas relational database, such as Access, stores data in separate tables under a database and one can write codes in models to process the data to generate reports. In that way, in Access, data is more secure and enjoys the benefit of data independence and sharing in a networked environment. But, Access lacks the power of easy data entry and related data processing, such as, sorting, calculations (for that one should know SQL!) etc. Now is it possible to have the benefits of both the world in one go? The answer is Yes!! Using very simple macro codes, it is possible to store data of a given Excel spreadsheet in an Access table and reuse the same Excel sheet for another set of data for the same purpose and in case of necessity it’s also possible to reprocess the same set of data by reloading it into the previous locations from the access database. So, what is the benefit? One can use spreadsheet for data entry and related processing and Access for storing data for future use. In that way, it is possible to use the same spreadsheet permanently for a particular type of application, for example, calculation of income tax of salaried employees.

Excel spreadsheet is a static monolithic database structure. It contains data, formula, comments and graph all together and that is the source of its main drawback (absence of data independence). Here data is not a separate entity, the data cannot be used for some other purpose, where as in any RDBMS data structure, stored data are application independent and are used independently for different purpose in a shared network environment. Spreadsheet is very poor in record maintenance, especially when it’s a routine job, moreover, due to its open monolithic structure; data are vulnerable for getting corrupted.

Suppose there is an elegant spreadsheet to calculate the tax burden of an employee in an organization. Now, how to use it for all employees!!! A simple solution is to copy and modify it accordingly for each employee. Then what is the output? Several spreadsheets! One for each employee!! It is now very difficult to handle so many spreadsheets and simply not possible to keep a track of tax related information of the employees, as we can’t use any query over these spreadsheets. Here we have addressed this problem of data independence and multiple spreadsheets with an example of simple Student Information System and tried to solve it by connecting Excel, Access together using Visual Basic. The solution is very simple and has some added advantages also.

How to get the data independence?

To achieve the data independence in Excel, one has to separate out the data from all other spreadsheet attributes like formula, colour, text attributes etc. This can be done by storing only the data into Access database tables and reloading the data into the same spreadsheet for data reprocessing. This will help us to use the same spreadsheet/workspace for different sets of data.

How to do it?

The whole process can be briefly stated as below:

§ Design the workspace of the spreadsheet as per requirement.
§ Enter data of a particular set.
§ Process the data and generate report as per requirement.
§ Store only the data in Access database with the help of a macro.
§ Clear the spreadsheet to load another set of data for processing.
§ Store the new set of data again in the Access and repeat the process for all sets of data
§ If necessary, reload data from Access tables into the spreadsheet.
§ Modify the data, if necessary, and store the modified data in the database.

As an example let us considered a semester result processing system of an engineering college (Figure 1) with basic inputs like admission no, session, semester, discipline and marks of three subjects. There are two components of the two mechanism, the backend and the front-end. The backend consists of Access and the front-end is the Excel Spreadsheet. The basic approach is to enter the data through spreadsheet and store the data in Access to make the same spreadsheet reusable for other set of data.

Create an Access Database

Create an Access database on student information system for semester examination marks processing system, say, stuinfo.mdb with the following fields: admission no.(Admno), name (Stuname), with Marks, say, thermodynamics (TRM), machine drawing (MCM), environment engineering (EVS), total marks(Total) and Average (Average).

It is possible to import Access database table into Excel by query of table. This can be achieved through new database query of "Get External Data" of Data Menu. This method copies the table as per the mentioned query to the location pointed by the cursor. But if it is required to copy in a particular predefined format then it is not possible. Whereas copying an Excel spreadsheet into Access database (to separate tables) is not possible through any menu item.

Figure 2: Excel Spreadsheet with data and command buttons

Create an Excel Spreadsheet

A new Excel sheet (Figure 2) may be created with two command buttons and a tabulation sheet of marks. First button is to Save excel data (pure) in the marks table of Access and the next one is to Load the same from the tables of Access to the previous locations of the spreadsheet. The Save button checks the Access table for the key fields, if it is not there, it calls addition routine to add records, whereas if its already there, then it goes for updation, giving an opportunity of updation of the spreadsheet. For modification of old data, one has to load the data in the Excel sheets, then save the modified data again in the Access database tables using add/update button. So these two functions (Save & Load) give us the necessary two-way communication between Excel and Access.

Figure 3: Access Database with two tables

Save Macro

This is the first step of the macro development. The purpose of this command button is to save the Excel data sheet to Access database. Functionally this can be divided in three routines, namely, OpenDatabase, ReadExcelRecords(), and AddtoDatabase. AddtoDatabase calls two subroutines, UpdateExistingRecord and AddNewRecords, depending upon the existence of the records in the database tables,. All routines are as in following program listings:

Sub SaveToAccess()
flag = OpenAllFiles()
Session = "0304"
Semester = "BTCSE4"
If flag Then
Call ReadExcelRecords()
Call AddToDatabase()
End If

End Sub

Macro – 1 Main Save routine

Function OpenAllFiles() As Boolean

                Dim dbPath As String
                dbPath = "d:\Excel\stuinfo.mdb"
                On Error GoTo dbErrors
                Set dbContact = DBEngine.Workspaces(0).OpenDatabase(dbPath, False)
                   OpenAllFiles = True
                Exit Function
dbErrors:
                OpenAllFiles = False
                MsgBox (Err.Description)

End Function

Macro –1.1 Open database

Sub ReadExcelRecords()
    Dim a As String
    Dim startr As Integer
    Dim endr As Integer
    Dim c As Integer
    Dim m As Integer
    Dim n As Integer
    Dim r As Integer
    Dim i As Variant
    Dim x As Variant
    Dim StuRecordTemp(20) As Variant
    startr = 13
    endr = 26
    c = 0
    ColSet = Array("B", "C", "D", "E", "F", "G", "H", "I")
    m = 0
    NoOfStudents = 0
    For r = startr To endr
        n = 0
            For Each i In ColSet
                a = i & r
                Range(a).Select
                x = ActiveCell.Value
                StuRecordTemp(n) = x
                n = n + 1
            Next
                n = 0
                If (StuRecordTemp(1) = Session And UCase(StuRecordTemp(2)) = Semester) Then

                    For Each i In ColSet
                        StuRecord(m, n) = StuRecordTemp(n)
                        n = n + 1
                    Next

                    NoOfStudents = NoOfStudents + 1
                    m = m + 1
                End If

Next r

End Sub

Macro – 1.2 Read data from excel spreadsheet

Sub AddToDatabase()
Dim i As Integer
Dim j As Integer

Dim MatchRollNo As Boolean

     sqlNames = "Select * "
     sqlNames = sqlNames & " From Marks"
     sqlNames = sqlNames & " Where Session = "
     sqlNames = sqlNames & "'" & Session & "'"
     sqlNames = sqlNames & " and Semester = "
     sqlNames = sqlNames & "'" & Semester & "'"
     sqlNames = sqlNames & " Order by Session,Semester,RollNo"

Set rsAllNames = dbContact.OpenRecordset(sqlNames)
j = rsAllNames.RecordCount
i = 0
Do While i < NoOfStudents
    With rsAllNames
    If (j > 0) Then
    .MoveFirst
    ‘ Seek the record in the table
    MatchRollNo = SeekRollNo(!RollNo, StuRecord(i, 0),
rsAllNames)

‘If found then update else add

    If MatchRollNo And Not (.EOF) Then
       Call UpdateExistingRecords()
    Else
                        Call AddNewRecords (i)
' if there is record of the given semester but not the 'particular                                                                                          ‘adm. no.

End If
Else

Call AddNewRecords (i) ' if there is no record in the access

End If

i = i + 1 ‘Next Record

End With

Loop

rsAllNames.Close ‘Close Record Set

dbContact.Close
‘Close the Database

End Sub

Macro - 1.3 Check the database tables and call add or update macro

Sub UpdateExistingRecords()

On Error GoTo ErrorHandler
' Enable error-handling routine.

Dim i As Integer
'Student no counter

Dim j As Integer 'Subject Counter

Dim MatchAdmno As Boolean
Dim MatchSubject As Boolean
Dim rsAllNames As Recordset

Set rsAllNames = dbContact.OpenRecordset(sqlNames, dbOpenDynaset)

rsAllNames.Edit
rsAllNames.Update

i = 0
Do While i <= (NoOfStudents - 1)
    With rsAllNames
        j = 0
        .MoveFirst
        MatchAdmno = SeekRecords(!Adm_no, Stu_Array(i, 1), rsAllNames)
        Do While MatchAdmno And Not (.EOF)          'j <= (NoOfSubjects - 1)

                .Edit
                !SRN = Stu_Array(i, 0)
                !RollNo = Stu_Array(i, 1)
                !Session = Session
                        !Semester = Semester
                If (splflg) Then
                !subje_pf = IIf(!Subje_ftsp <> "SP", "SF", "SP")
                Else
      !subje_pf = IIf(!Subje_ftsp <> "P", "F", "P")
                End If

!PassFail = IIf(Trim(!remarks) <> "Passed", "F", "P")
.Update
j = j + 1
.MoveNext
If Not (.EOF) Then
If (!Adm_no = Stu_Array(i, 1)) Then
MatchAdmno = True
'MatchAdmno = SeekRecords(!adm_no, Stu_Array(i, 1), rsAllNames)
Else

MatchAdmno = False

End If
End If

      Loop
      End With
     'rsAllNames.MoveLast
     'rsAllNames.Edit
     i = i + 1
Loop
Exit Sub
' Exit to avoid handler.
ErrorHandler:
' Error-handling routine.

    If Err.Number <> 0 Then
    msg = "Error # " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description & " Adm no: " & Stu_Array(i, 1) _
            & " Sub Code: " & Sub_array(j, 1)
    MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
End If

Resume Next ' Resume execution at next line
' that caused the error.
End Sub

Macro - 1.4 Read data from spreadsheet and update Access tables

Sub AddNewRecords(i As Integer)
On Error GoTo ErrorHandler ' Enable error-handling routine.

Dim col As Integer
With rsAllNames
        .AddNew
        col = 0
        !RollNo    = StuRecord(i, col)
        !Session   = StuRecord(i, col + 1) 'Trim(Mid$(Session, 3, 2) & Mid$(Session, 8, 2))

        !Semester = StuRecord(i, col + 2)
        !MCD       = StuRecord(i, col + 3)
        !THR        = StuRecord(i, col + 4)
        !MCP       = StuRecord(i, col + 5)
        !Total       = StuRecord(i, col + 6)
        !Average = StuRecord(i, col + 7)
       .Update
       .AddNew
End With
Exit Sub                                                                                 ' Exit to avoid handler.

ErrorHandler:
' Error-handling routine.
    If Err.Number <> 0 Then
    msg = "Error # [AddNewRecords] " & Str(Err.Number) & " was generated by " _
            & Err.Source & Chr(13) & Err.Description & " RollNo: " & StuRecord(i, 0)
            MsgBox msg, , "Error", Err.HelpFile, Err.HelpContext
    End If
    Resume Next
' Resume execution at next line
' that caused the error.

End Sub

Function SeekRollNo(admno As String, TempStu As Variant, rsAllNames As Recordset) As Boolean

    Dim MatchAdmno As Boolean
    With rsAllNames
    MatchAdmno = False
    Do While Not (MatchAdmno) And Not (.EOF)
    If (admno = TempStu) Then
        SeekRollNo = True
        MatchAdmno = True
        Exit Do
    Else
        SeekRollNo = False
   End If
    .MoveNext

    If Not (.EOF) Then
                  admno = !RollNo

    '     MatchAdmno = SeekRecords(!adm_no, Stu_Array(i, 1))
    End If
    If (.EOF) Then
        SeekRollNo = False
        Exit Do
    End If
    Loop
    End With
End Function

Macro - 1.5 Read data from spreadsheet and write into Access tables

Figure 4: Marks table with added records

Load Macro

The purpose of the macro is to load records from the access tables into the excel data sheet. While loading program takes care of the positions of the different fields in the spreadsheet. So using this button one can call old records into the spreadsheet and modify it, after that using the save button he can save the modified data can be saved back into the database table.

Sub LoadToExcel()
flag = OpenAllFiles
Session = "0304"
Semester = "BTCSE4"
If flag Then
Call LoadRecords()
End If
End Sub

Macro – 2 Main Load Routine

Sub LoadRecords()
Dim i As Integer
                 'Student no counter
Dim r As Integer
'Subject Counter
Dim c As Integer
Dim FirstTimeSp As Boolean
Dim FirstTimeRg As Boolean
Dim MatchAdmno As Boolean
Dim MatchSubject As Boolean
Dim rsAllNames As Recordset
Dim sr As Integer
Dim subcode As Variant
sqlNames = "Select * "
     sqlNames = sqlNames & " From Marks"
     sqlNames = sqlNames & " Where session = "
     sqlNames = sqlNames & "'" & Session & "'"
     sqlNames = sqlNames & " and semester = "
     sqlNames = sqlNames & "'" & Semester & "'"
     sqlNames = sqlNames & " Order by
Session,Semester,RollNo"

Set rsAllNames = dbContact.OpenRecordset(sqlNames, dbOpenSnapshot)

NoOfStudents = rsAllNames.RecordCount

If (NoOfStudents > 0) Then
r = 13
‘Starting row position of the spreadsheet
c = 2 ‘Starting column position of the spreadsheet

    For i = 1 To NoOfStudents
    With rsAllNames
        Cells(r, c)       = !RollNo
        Cells(r, c + 1) = !Session
        Cells(r, c + 2) = !Semester
        Cells(r, c + 3) = !MCD
               'Machine Drawing

Cells(r, c + 4) = !THR 'Thermodynamics

        Cells(r, c + 5) = !MCP    'Computer Programming
        Cells(r, c + 6) = !Total
        Cells(r, c + 7) = !Average
       .MoveNext
   End With
    r = r + 1
    Next
End If
rsAllNames.Close
dbContact.Close
End Sub

Conclusion

Any technical innovation should be evolutionary in nature rather than revolutionary. This is achievable only when benefits of existing techniques/applications are optimally used. The discussed in this article uses the advantages of both the Excel and Access in a hybrid mix. It is appears that the data independency is achievable and it is also possible to reuse the same spreadsheet for different sets of data. The outcomes of the approach are:

§ Data independency is achievable in a spreadsheet.
§ Same spreadsheet can be used for more than one set of data.
§ Excel works as frontend to backend MS Access.
§ Since data is stored permanently in Access tables, interfacing with different platforms is also possible.
§ Maintenance of records can be made much more systematic and effective, compared to the record maintenance in Excel.

Added on August 2, 2007 Comment

Inovative Interfacing of Excel and Access

End Sub

Function OpenAllFiles() As Boolean

End Function

End Sub

‘If found then update else add

End Sub

Function SeekRollNo(admno As String, TempStu As Variant, rsAllNames As Recordset) As Boolean

Comments

Post a comment