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 SettingsDickMy DocumentsPayroll.mdb;"
sql = "SELECT * FROM tblEmployees;"
Set cn = New ADODB.Connection
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
'copy from immediate window and paste into class module
Debug.Print sDeclare & vbTab & vbNewLine & sCode
Set rs = Nothing
Set cn = Nothing
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"
sType = "Variant"
sPrefix = "v"
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
Public Property Get EmployeeID() As Long
EmployeeID = mlEmployeeID
Public Property Let EmployeeFirstName(sEmployeeFirstName As String)
msEmployeeFirstName = sEmployeeFirstName
Public Property Get EmployeeFirstName() As String
EmployeeFirstName = msEmployeeFirstName
Public Property Let EmployeeMiddle(sEmployeeMiddle As String)
msEmployeeMiddle = sEmployeeMiddle
Public Property Get EmployeeMiddle() As String
EmployeeMiddle = msEmployeeMiddle
Public Property Let EmployeeLastName(sEmployeeLastName As String)
msEmployeeLastName = sEmployeeLastName
Public Property Get EmployeeLastName() As String
EmployeeLastName = msEmployeeLastName
Public Property Let HireDate(dtHireDate As Date)
mdtHireDate = dtHireDate
Public Property Get HireDate() As Date
HireDate = mdtHireDate
Public Property Let Salary(dSalary As Double)
mdSalary = dSalary
Public Property Get Salary() As Double
Salary = mdSalary
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?