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.)
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 Settings\Dick\My Documents\Payroll.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
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:
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?
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
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.
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!
To create a classmodule with some code
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
I’ll check out ADOX, thanks Rob. It sounds like we should open source this project. I think it would be the first open source VBA project ever!
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.
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.
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.
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.