Creating Classes from Access Tables

As I mentioned earlier, I want to develop a framework generating app to create some of the code necessary to read and write to and from an Access database. This is iteration 1 of about 100.

Start with this table in Access

Set a reference (Tools – References) to Microsoft ActiveX Data Objects 2.8 Library.

Then run this code. (It won’t work for you without changing the connection string.)

Sub MakeClass()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim adField As ADODB.Field
Dim sql As String
Dim sCon As String
Dim sType As String, sPrefix As String
Dim sDeclare As String
Dim sCode As String
Dim sVariableName As String

Const sNL As String = vbNewLine & vbTab & vbNewLine

'create strings for ado connection
sCon = "DSN=MS Access Database;DBQ=C:Documents and SettingsDickMy DocumentsPayroll.mdb;"
sql = "SELECT * FROM tblEmployees;"

Set cn = New ADODB.Connection
cn.Open sCon

Set rs = cn.Execute(sql)

'sDeclare will hold the declarations secton
'sCode will hold the Property statements
sDeclare = "Option Explicit" & sNL

For Each adField In rs.Fields

ConvertADOType adField.Type, sType, sPrefix
sVariableName = "m" & sPrefix & adField.Name

sDeclare = sDeclare & "Private " & sVariableName & " As " & sType & vbNewLine

sCode = sCode & "Public Property Let " & adField.Name & "(" & sPrefix & _
adField.Name & " As " & sType & ")" & sNL
sCode = sCode & vbTab & sVariableName & " = " & sPrefix & adField.Name & sNL
sCode = sCode & "End Property" & sNL

sCode = sCode & "Public Property Get " & adField.Name & "() As " & sType & sNL
sCode = sCode & vbTab & adField.Name & " = " & sVariableName & sNL
sCode = sCode & "End Property" & sNL

Next adField

'copy from immediate window and paste into class module
Debug.Print sDeclare & vbTab & vbNewLine & sCode

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

End Sub

It uses this helper sub

Sub ConvertADOType(lType As Long, ByRef sType As String, ByRef sPrefix As String)

If lType = 3 Then
sType = "Long"
sPrefix = "l"
ElseIf lType = 202 Then
sType = "String"
sPrefix = "s"
ElseIf lType = 135 Then
sType = "Date"
sPrefix = "dt"
ElseIf lType = 5 Then
sType = "Double"
sPrefix = "d"
Else
sType = "Variant"
sPrefix = "v"
End If

End Sub

Obviously that’s not going to cover all of the types, but it’s a start. To be honest, I don’t need this thing to do 100% of the work, just 98% of it. So if there’s some touch up after the fact, I’m OK with that.

When I paste the resulting code in a class module, I get:

Option Explicit

Private mlEmployeeID As Long
Private msEmployeeFirstName As String
Private msEmployeeMiddle As String
Private msEmployeeLastName As String
Private mdtHireDate As Date
Private mdSalary As Double

Public Property Let EmployeeID(lEmployeeID As Long)

mlEmployeeID = lEmployeeID

End Property

Public Property Get EmployeeID() As Long

EmployeeID = mlEmployeeID

End Property

Public Property Let EmployeeFirstName(sEmployeeFirstName As String)

msEmployeeFirstName = sEmployeeFirstName

End Property

Public Property Get EmployeeFirstName() As String

EmployeeFirstName = msEmployeeFirstName

End Property

Public Property Let EmployeeMiddle(sEmployeeMiddle As String)

msEmployeeMiddle = sEmployeeMiddle

End Property

Public Property Get EmployeeMiddle() As String

EmployeeMiddle = msEmployeeMiddle

End Property

Public Property Let EmployeeLastName(sEmployeeLastName As String)

msEmployeeLastName = sEmployeeLastName

End Property

Public Property Get EmployeeLastName() As String

EmployeeLastName = msEmployeeLastName

End Property

Public Property Let HireDate(dtHireDate As Date)

mdtHireDate = dtHireDate

End Property

Public Property Get HireDate() As Date

HireDate = mdtHireDate

End Property

Public Property Let Salary(dSalary As Double)

mdSalary = dSalary

End Property

Public Property Get Salary() As Double

Salary = mdSalary

End Property

It looks right and it compiles, so that’s good.

Here’s my todo list:

  • Create the class module rather than copy and paste
  • Name the class module based on the table name
  • Select the database and table from a list on a userform
  • Establish Parent Child relationships between two classes
  • Create functions to fill the class from a recordset
  • Create a userform to add/edit/delete records

What if you ran through all the steps of this utility and you had a working CRUD program? That’d be pretty cool, I think. Sure, the userform wouldn’t be just what you want, but it would give you a great starting point with the underlying structure in place.

Looking at the list, I expect this utility to be functionally complete in 2014 and polished in 2036. But I already used what I have here today and it saved me some time. Anything to add to this list?

11 thoughts on “Creating Classes from Access Tables

  1. I reckon ADOX (Microsoft ADO Extensions for DDL and Security) would be a better bet for discovery of tables within your database and their fields and data types.

    Rob

  2. I looked into this type of thing awhile ago. Unfortunately, I wasn’t able to find any object-relational mapping (ORM) tool or any other kind of persistence tool for VBA. Closest thing I found was JCFramework for VB6, but it looked too complex to quickly port to VBA, so I just abandoned the idea. But I wish you well in your adventures with this more lightweight framework, Dick.

  3. Writing/ injecting code with code. Does this help at all?

    a$ = “Private Sub Workbook_Open()” & vbCr & _
    “Worksheets(1).Unprotect” & vbCr & _
    “Worksheets(1).Range(“”B9?”).value = Replace$(Name,””.xls””,””””)” & vbCr & _
    “Worksheets(1).Range(“”A7?”).value = “”Current filename “” & Path & “””” & Name” & vbCr & _
    “Worksheets(1).Protect” & vbCr & _
    “Saved = True” & vbCr & _
    “End Sub”
    .ActiveWorkbook.VBProject.VBComponents.Item(“ThisWorkbook”).CodeModule.AddFromString (a$)

    My contents of a$ are just an example of something I have running; please replace it with something which is useful to you. Watch out for those double double quotes!

  4. To create a classmodule with some code

    Sub newclassmodule()
      With ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_ClassModule)
        .Name = “testklasse”
        .CodeModule.AddFromString Replace(“private Sub gemakkelijk ()# ‘ Dit is slechts een voorbeeld # End Sub”, “#”, vbCr)
      End With
    End Sub
  5. Dick, once you get to a stage you’re happy with, it might actually be a very good project to open source – it seems like just about every current language but VBA has an open source Data Access Layer code generator (or some other kind of data persistence tool). I could see this being pretty useful for a lot of people.

  6. Hey Dick, I have started a collabarative effort with some guys over at VBAExpress to build a database generator. The original aime was to capture some definitions of the database and then build the database on the fly (a bit like the table driven menu builders). My original idea was just an Excel table, and some clas modules at the back to handle the different database types. I started this collabaration partly because I felt the tool could be useful, more as a way of doing a collabarative project to see what evolved out of a group as against a singleton.

    We are already talking multiple front-ends (Excel tables, Access databases, foems, et al), which would generate XML defintions that are passed to a COM backend. This might make an interesting addition, the data discovery would not be necessary as the inputs would be defining them when defining the database.

  7. Bob: Sounds interesting. I started this based on some work I was doing with Ruby on Rails. It does everything soup to nuts. All you do is create the database, and it handles all the CREATE and DROP statements so that your database is managed completely within the Rails framework. I didn’t want to go that far because it would be too much work. Maybe as each of us gets further along, we should see about combining them.

  8. Dick, that sounds like a date. I think there is definitley some potential symbiosis here, so I will let you know when we have something to show. Don’t expect it to be soon, progress on a collabarative effort is slow, especially when it is just a side of the table affair.

  9. Hi Dick

    6 and a half years later I came across this thread. Did anything ever happen on the database generator ?

    Nick

  10. Nick: No, nothing happened on my end. I don’t remember if Bob and the others ever did anything with it.

Leave a Reply

Your email address will not be published. Required fields are marked *