ADO Class Writer for Access Tables

carlmack · Jun 27th 2006

I've seen multi-tiered classes used in other languages as the main form of interaction with a db. The idea is that it if you create a class for each table you can use one line methods to do what we now write whole subs for. The problem for us, as I see it, is that these classes would take a long time to write so only people writing big applications can do it. However theoretically all the info to write the classes is in the database so you should be able to get the classes to write themselves.

Last night I finally made a start with the code below. At the moment I have only included one method (Dump data to spreadsheet). On my machine it is "working" in that it creates a very simple class for each table to any database you connect to.

If anybody wants to try it you need to add the required references and then change the path of the Access database in the GetDbFullPath function then run WriteAllClasses.

In needs some thinking about so I am looking for suggestions/ideas to bounce around and see where to take this eg. what methods and properties to include.

Code

' Carl Mackinder 
'
' Class Writer Module for MS Access - Requires :-
' Reference to Microsoft ActiveX Object 2.x
' Reference to Microsoft VB extensibility
' Trust access to VB project checked in macro security
'
Private Function GetDbFullPath()
GetDbFullPath = "c:\EDI\EDI.mdb" ' #### CHANGE DB PATH  AND RUN WriteAllClasses ####
End Function


Public Sub WriteAllClasses()


Dim Cn As ADODB.Connection
Dim RsTables As ADODB.Recordset
Dim RsFields As ADODB.Recordset


Call CnAccess(Cn)
Set RsTables = GetTables(Cn)
RsTables.MoveFirst
Do While Not RsTables.EOF
    Set RsFields = GetTableFields(Cn, RsTables(2))
    Call WriteClass(RsTables(2), RsFields)
    RsTables.MoveNext
Loop
Call WriteClassRefs(RsTables)
Call WriteTestMod(RsTables)
End Sub


Public Sub CnAccess(ByRef Cn As ADODB.Connection)


Dim UserId As String
Dim Password As String


UserId = ""
Password = ""
Set Cn = New ADODB.Connection
Cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & GetDbFullPath & ";", UserId, Password


End Sub


Private Sub WriteClass(TableName As String, RsFields As ADODB.Recordset)


Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Dim FieldString As String


FieldString = "Private Cn as ADODB.Connection" & Chr(13) & "Private Rs as ADODB.Recordset" & Chr(13)


If ModExists("c" & TableName) Then ModDelete ("c" & TableName)
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_ClassModule)
VBComp.Name = "c" & TableName
Set VBCodeMod = VBComp.CodeModule


RsFields.MoveFirst
'
Do While Not RsFields.EOF
    FieldString = FieldString & "Private F" & Trim(RsFields.Fields(3)) & " " & GetFieldType(RsFields.Fields(11)) & Chr(13)
    RsFields.MoveNext
Loop
FieldString = FieldString & DumpToSheetMethod(TableName)


LineNum = VBCodeMod.CountOfLines + 1
VBCodeMod.InsertLines LineNum, FieldString


End Sub


Private Function GetTables(Cn As ADODB.Connection) As ADODB.Recordset
Set GetTables = New ADODB.Recordset
Set GetTables = Cn.OpenSchema(adSchemaTables, Array(Empty, Empty, Empty, "Table"))
End Function


Private Function GetTableFields(Cn As ADODB.Connection, TableName As String) As ADODB.Recordset
Set GetTableFields = New ADODB.Recordset
Set GetTableFields = Cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, TableName))
End Function


Private Function ModExists(ModName As String) As Boolean
On Error Resume Next
ModExists = Len(ThisWorkbook.VBProject.VBComponents(ModName).Name) <> 0
End Function


Private Sub ModDelete(ModName As String)
Dim VBComp As VBComponent
Set VBComp = ThisWorkbook.VBProject.VBComponents(ModName)
ThisWorkbook.VBProject.VBComponents.Remove VBComp
Set VBComp = Nothing
End Sub


Private Function GetFieldType(FieldType As Integer) As String
Select Case FieldType
    Case 130
        GetFieldType = "as String"
    Case 3
        GetFieldType = "as Integer"
    Case 5
        GetFieldType = "as Double"
    Case 11
        GetFieldType = "as Boolean"
    Case 7
        GetFieldType = "as Date"
End Select
End Function


Private Sub RemoveClasses()
'                                       Removes all classes from this workbook
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule


For Each VBComp In ThisWorkbook.VBProject.VBComponents
    If VBComp.Type = vbext_ct_ClassModule Then
        ThisWorkbook.VBProject.VBComponents.Remove VBComp
    End If
Next VBComp
End Sub


Private Sub WriteClassRefs(RsTables As ADODB.Recordset)
'              Overwrites the ClassRefs standard module which dimensions an instance of each class
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Dim FieldString As String


FieldString = "' This sub references all the Access classes" & Chr(13)


If ModExists("ClassRefs") Then ModDelete ("ClassRefs")
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "ClassRefs"
Set VBCodeMod = VBComp.CodeModule


RsTables.MoveFirst
Do While Not RsTables.EOF
    FieldString = FieldString & "Public Obj" & RsTables.Fields(2) & " As New c" & RsTables.Fields(2) & " " & Chr(13)
    RsTables.MoveNext
Loop


LineNum = VBCodeMod.CountOfLines + 1
VBCodeMod.InsertLines LineNum, FieldString


End Sub


Private Function DumpToSheetMethod(TableName As String) As String
DumpToSheetMethod = "Public Sub DumpToSheet(DumpRange As Range)" & Chr(13) & _
                    "Call CnAccess(Cn)" & Chr(13) & _
                    "Set Rs = New ADODB.Recordset" & Chr(13) & _
                    "Rs.Open " & Chr(34) & TableName & Chr(34) & ",Cn" & Chr(13) & _
                    "DumpRange.CopyFromRecordset Rs" & Chr(13) & _
                    "Rs.Close" & Chr(13) & _
                    "Set Rs = Nothing" & Chr(13) & _
                    "End Sub" & Chr(13)
End Function


Private Sub WriteTestMod(RsTables As ADODB.Recordset)
'              Overwrites the Test standard module with a sub for each table testing the dump method
Dim VBComp As VBComponent
Dim VBCodeMod As CodeModule
Dim LineNum As Long
Dim FieldString As String


FieldString = "' Test sub for DumpToSheet method " & Chr(13) & Chr(13)


If ModExists("Test") Then ModDelete ("Test")
Set VBComp = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)
VBComp.Name = "Test"
Set VBCodeMod = VBComp.CodeModule


RsTables.MoveFirst
Do While Not RsTables.EOF
    FieldString = FieldString & "Sub TEST" & RsTables.Fields(2) & "()" & Chr(13) & _
    "Obj" & RsTables.Fields(2) & ".DumpToSheet(Worksheets(1).range(" & Chr(34) & "a1" & Chr(34) & "))" & Chr(13) & _
    "End sub" & Chr(13)
    RsTables.MoveNext
Loop


LineNum = VBCodeMod.CountOfLines + 1
VBCodeMod.InsertLines LineNum, FieldString


End Sub

Display More

Cheers,

Carl

**Andy Pope** · Jun 28th 2006

Re: ADO Class Writer for Access Tables

Hi Carl,

You code works well, it had no problem dealing with a database of mine.

Currently each class, with the exception of the table name, is identical. Would you expand the code to add unique fieldname properties to the class?

Now I don't do Access very often so I may be missing the point so please tell me if this is the case.

So who is this technique aim at and why is it better than either the existing ADO object model or a more generic set of classes built to handle DB's.

carlmack · Jun 28th 2006

Re: ADO Class Writer for Access Tables

Hi Andy,

Thanks for testing it.

Last night I added a prefix to the field names (I guess I should use LET and GET but I havn't done much with classes before so its hard work) and added another method which creates a new sheet and dumps the table.

I am not sure where this will go or if it would be much use so I am looking for ideas.

One idea I had was to create a sheet which is a mirror of the table by dumping it and disconnecting the recordset, then after making changes to the sheet creating a recordset of the sheet, then disconnecting the sheet recordset set the Access recordset to the sheet recordset. This way MAYBE we could send the data back to Access in one go. ie you would have the flexibility of an Excel being a mirror of the Access table but with programatic control. I know you can do similar things with linked tables but they have limitations and you have to create them. For me this might be a nice way to load test data to a database. In the longer term I was thinking of SQL server rather than Access where it can be hard to get data in.

Another possibility I thought of was to have an Excel form that directly links to an Access table.

If your interested see attached version 2

Thanks again for the feedback.

Carl

**Andy Pope** · Jun 28th 2006

Re: ADO Class Writer for Access Tables

Here is an example of using a generic set of classes.
The 3 classes handle database, tables and fields for specified db file.

This is just the testing routine, which will dump out the data to the activesheet. Notice the reuse of the dumptosheet routine and that the code module contains no code relating to the class functionality. Only the declaration of objects and the use of there properties and methods.
[vba]Sub TestMe()

Dim clsDB As CDB_File
Dim clsTable As CDB_Table
Dim clsField As CDB_Field
Dim lngCol As Long

Set clsDB = New CDB_File

ActiveSheet.Cells.Clear
lngCol = 1

clsDB.Filename = "C:\temp\test.mdb" ' #### Change to test database

For Each clsTable In clsDB.DBTables
With ActiveSheet
.Cells(1, lngCol) = clsTable.Name
.Cells(2, lngCol) = clsTable.DBFields.Count & " Field(s)"

If lngCol Mod 2 = 0 Then
clsDB.DumpToSheet clsTable.Name, .Cells(3, lngCol)
Else
clsTable.DumpToSheet .Cells(3, lngCol)
End If
lngCol = lngCol + clsTable.DBFields.Count
End With
Next

Set clsDB = Nothing

End Sub[/vba]

Does the generic approach make it easier or harder for you to use?

carlmack · Jun 28th 2006

Re: ADO Class Writer for Access Tables

Andy,

Very interesting approach. I will study it tonight and come back to you.

Many thanks,

Carl

A9192Shark · Jun 28th 2006

Re: ADO Class Writer for Access Tables

Andy and Carl,

I have not gone through teh code in or posts in detail but thought I would drop one use in for information purposes.

I built a class builder using Excel to generate a text file based on the fields in a table that I imported to the VBE so that I could build collections of class objects to rapidly find data in a table. That is I reproduced a table as a collection. When you are doing lots of searches (50+) on lots of tables (30+) to follow a structured calculation for 30k+records it was much quicker to load all of the lookup tables into memory as collections than to keep executing find statements on a recordset.

Alan.
PS Think I posted the code to this site a while ago???

carlmack · Jun 28th 2006

Re: ADO Class Writer for Access Tables

Alan,

Great ! Sounds like it will be well worth a look at. I did a couple of searches but could not find it. Can you post the link please.

Cheers
Carl

carlmack · Jun 28th 2006

Re: ADO Class Writer for Access Tables

Andy,

I just read you post again. On my machine it does create unique fieldname properties for each class so there must be something wrong.

I'll try it on another machine.

Cheers
Carl

**Andy Pope** · Jun 28th 2006

Re: ADO Class Writer for Access Tables

To clarify, it creates private variables within each class object that correspond to tables field names.

But as yet does not expose these as public or provide properties with which to manipulate them.

carlmack · Jun 28th 2006

Re: ADO Class Writer for Access Tables

Andy,

Understood. You are correct. I have some work to do !!

Cheers
Carl

A9192Shark · Jun 29th 2006

Re: ADO Class Writer for Access Tables

Carl,

Attached is an Excel workbook that will build classes based on information you type in. Somewhere I have code that broke the table down into the required format. I did it as a two stage process so that I could add extra fields or exclude certain fields from the class as I wished.

Note that the code is writen for arrays but it does not work. I could not pass both the index and the value. I think I have to pass a variable that is itself a class and has the two properties index and value. I will work on that at some point!

Thanks to Andy Pope for helping with the development a long time ago. One of the key things he helped with was the Properties Property to allow dynamic selection of the property that you wish to assign a value to.

Thanks and Good Luck,

A.

carlmack · Jun 29th 2006

Re: ADO Class Writer for Access Tables

Hi Andy & Alan,

I went through Andy's generic set of classes. It has taught me a great deal (not all sunk in yet) and given me lots of ideas so many thanks indeed Andy.

My guess is the generic classes approach could probably achieve 80% of what I was thinking about before. This morning I also talked it through with some VB.NET programmers who use classes all day long. Some of their stuff is even more mind boggling.

I will have a look at Alan's work tonight.

Thanks a lot guys,

Carl

ADO Class Writer for Access Tables

Participate now!

Share