standalone classes for multiple access DB's to share common code 3

[1DMF]

Hi,

I have a few MS applications for different departments, each has identical code for certain features.

Each time a change is made in one I have to make the same change in all the others.

How do i take the VBA code out of MS access so it is a stand alone class that all the DB's use so I then only ever need to make changes in one place and all the applications use the class in a proper OO manner.

Would I create a separate MS DB which the others link to? or import from?

Do i turn the class module into a DLL or a .vb file?

all advice appreciated


Thanks,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Put the code in a standalone library database. Now you can add it as a reference like you would add any reference. You can now use the code as if it was in the databases to include the object browser and intellisense.

 

[1DMF]

Do I then need to distibute the linked library database?

Do I distribute it as an MDE as well as the main DB?

I have my master copy, which is packaged to an MDE and placed on the users 'z:' drive, so every user is running their own local copy so to speak.

If I link in the DB how would I change the link reference to not be my master MDB but instead point to their local MDE z: drive area?

I use the following code to relink all the tables to the SQL server, is it a similar process?

Public Function SetLinkedTables()

    Dim db As Database
    Dim Cnct As String
    Dim tdf As TableDef
    Dim sCon As Variant
    Dim a As Integer
    Dim sVar As Variant
    Dim sName As String
    
    Dim sServer As String
    Dim sPWord As String
    
    
    On Error GoTo SLT_Err

    Set db = CurrentDb

    db.TableDefs.Refresh
  
    ' Loop Table Defs
    For Each tdf In db.TableDefs
        With tdf
            'split current string to find db data
            sCon = Split(.Connect, ";")
            For a = 0 To UBound(sCon)
                If sCon(a) <> "" Then
                    sVar = Split(sCon(a), "=")
                    If sVar(0) = "DATABASE" Then
                        Cnct = sVar(1)
                        If left(.Name, 4) = "dbo_" Then
                            sName = right(.Name, Len(.Name) - 4)
                            sServer = "myIP"
                            sPWord = "myPassowrd"
                        ElseIf left(.Name, 4) = "aff_" Then
                            sName = right(.Name, Len(.Name) - 4)
                            sServer = "myIP"
                            sPWord = "myPassowrd"
                        ElseIf left(.Name, 3) = "ar_" Then
                            sServer = "myIP"
                            sPWord = "myPassowrd"
                            sName = right(.Name, Len(.Name) - 3)
                        Else
                            sName = .Name
                            sServer = "myIP"
                            sPWord = "myPassowrd"
                        End If
                        ' create connection to table
                        Call AttachDSNLessTable(.Name, sName, sServer, Cnct, "sa", sPWord)
                    End If
                End If
            Next
        End With
    Next
                       
    db.TableDefs.Refresh

    Set tdf = Nothing
    Set db = Nothing
End Function

Public Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)

    On Error GoTo AttachDSNLessTable_Err
    Dim td As TableDef
    Dim stConnect As String

    For Each td In CurrentDb.TableDefs
        If td.Name = stLocalTableName Then
            CurrentDb.TableDefs.Delete stLocalTableName
        End If
    Next

    If Len(stUsername) = 0 Then
        '//Use trusted authentication if stUsername is not supplied.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
    Else
        '//WARNING: This will save the username and the password with the linked table information.
        stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
    End If
    Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
    CurrentDb.TableDefs.Append td
    AttachDSNLessTable = True
    Exit Function
    
AttachDSNLessTable_Err:

    AttachDSNLessTable = False
    MsgBox "AttachDSNLessTable encountered an unexpected error: " & err.Description
End Function

Could i modify this code to incorporate the extra MDE Library Link?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[PHV]

I'd distribute it as an MDA

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

As PHV stated you may consider an Add-In

http://www.databasedev.co.uk/access-add-ins.html

With a reference you do not "link" but add it as a reference. You can programmatically add references.

You may want to look at both approaches.

 

[1DMF]

hmm MDA, new one on me... thanks guys... got some homework to do!

Can users open MDA files, view source code or change it?




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

I would still look at a library database located on your network. The deployment and updating may be easier. I would research both options and weigh the pros and cons.

 

[1DMF]

when you say library database , do you mean just another ms acces smde which is referenced?

as that MDA url calls them librarys also, so bit confused what you mean.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Yes just a database full of nothing but code. Make it an mde or accde. Now in your databases add a reference to this, just like you add any reference.

Assume my mde/accde is called "HelloWorld.accde"
If I add it as a reference in my databases then in code

I can do
HellowWorld.SomeprocedureOrFunction

I have never tried referencing something on the network, normally I would send out an updated MDE/ACCDE using a batch and store in a common location on the local machine.

 

[1DMF]

normally I would send out an updated MDE/ACCDE using a batch and store in a common location on the local machine.

That's exactly what I do, only there is only the one MDE before.

I'm just learing about OOP in Access, never knew I could create classes , then accessor methods via the 'property' feature.

Though why is the setter called 'Let' not 'Set'?

Man this OO thing seemed like another world that I never wanted to visit, now I'm really getting into it.

can't beleive how bad my procdural code was till I learned a bit of OOP, just got to work out the logic of MS access forms and a separate model.

Gotta get me head round how I can encapsulate the state and protocol I need to perform actions and where I declare objects for use.

I take it I just create a new MDB and start designing my classes.

Then export to MDE and reference it from the main MDB application?

then wrap that up into an MDE?

ripping apart an application written over 6 years with bad procedural coding and then trying re-think everything in an OOP environment is certainly taxing on the ole grey cells!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Do you have a copy of Desktop Developers Handbook by Litwin and Getz? Really good examples of OO in Access. Highly recommend it. Even an older version (save money) would be good. Most of my code is class modules so that I can reuse functionality. I have demonstrated this several times on Tek_Tips and in a few FAQs, but one of the most powerful things you can do in VBA is to build classes that capture form and control events.

It is called a Let because if the property assigns a value to a standard data type then you are letting the property equal some standard data type.
You only set properties that are objects.

 

[1DMF]

It's a bit different than my current VB course, they have Get & Set accessor methods of the property, do the same function though.

Little snag, why can't i create a constructor?

in my clsClass I put

Public Function new()

I get a compile error (Expected: identifier) ?

Do I assume the top of the class is where you just declare the instance attributes and perform any default assigning as there is no direct 'new' constructor in VBA?




"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

VBA has does not provide the full range of OOP that VB or VB.net. It is limited but there is still a lot that you cand do.

Take a look at FAQ702-6304 you may find interesting. Here is a class module that demonstrates how to turn a standard combobox into a "Find as You Type" combobox. This then requires one single line of code to instantiate the class. Once you start coding this way, you will greatly expand what you can do with Access.

 

[1DMF]

Thanks, MajP

I still don't get how I initialise class attributes if there isn't a contructor.

I tried to do it at the top of the class with

Private sMacID   As String
sMacID = GetWSID()

but that errors with

Invalid outside procedure

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

See if this answers the question.

Option Compare Database
Option Explicit
'Class name: DummyClass
Private mName As String
Private mWeight As Double
Private mShipDate As Date
Const minWeight = 0.25

Private Sub Class_Initialize()
  'Initialize and terminate are the only events that a class has
   'Unfortunately you cannot pass in parameters.
   'But you could hard code some defaults
   mName = "Not Assigned"
   mWeight = minWeight
   mShipDate = Now
End Sub

Public Sub MyClassInitialize(theName As String, theWeight As Double, theShipDate As Date)
  'So fake it with your own initialize procedure
  mName = theName
  mWeight = theWeight
  mShipDate = theShipDate
End Sub

Public Property Get Name() As String
  Name = mName
End Property

Public Property Let Name(ByVal theName As String)
  'This would be a Set if assigning to an object
  mName = theName
End Property

Public Sub PrintObjectInfo()
  'This is a class procedure
  Dim strInfo As String
  'I can use the property here
  strInfo = "Name: " & Me.Name
  'I did not make a property for weight, I use the public variable
  strInfo = strInfo & vbCrLf & "Weight: " & mWeight
  strInfo = strInfo & vbCrLf & "ShipDate: " & mShipDate
  Debug.Print strInfo
End Sub

Private Sub Class_Terminate()
 'Initialize and terminate are the only events that a class has
 'Do clean up here if necessary
End Sub

Public Sub testClass()
  'dimension
  Dim dcDummy As DummyClass
  Dim dcDummy2 As DummyClass
  'initialize
  Set dcDummy = New DummyClass
  'the defaulat intialize event occurs
  dcDummy.PrintObjectInfo
  Set dcDummy2 = New DummyClass
  'use fake initialize as constructor
  dcDummy2.MyClassInitialize "Dummy2", 12.5, Now
  dcDummy2.PrintObjectInfo
  'now use properties to change
  dcDummy2.Name = "New Dummy 2"
  dcDummy2.PrintObjectInfo
End Sub

 

[1DMF]

Man - sometimes 1 star is never enough!



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

If you are following that concept, then look at the demo for the FAYT combobox. Because this is where the real power of class modules for VBA and especially Access.

I have a combo, and a form as properties of the class. I define those "withEvents". That means once I set them as a property, I can capture their events. So even though there is no event procedures on the form's module, I can capture the events inside of my class and do things. This allows me to extend the functionality of an object. Since these are objects you can see that I have Set and Get properties instead of Let and Get. Example

Public Property Get FilterComboBox() As Access.ComboBox
Set FilterComboBox = mCombo
End Property

Public Property Set FilterComboBox(TheComboBox As Access.ComboBox)
Set mCombo = TheComboBox
End Property


So for practice I would be a simple class where you pass in a textbox as a property. See if you can capture the events like got focus or after update. Then in your class do something when these events occur. Your code on the form then would be something like this.

public mtb as MyTextBox

Private sub form_Open()
set mtb = new MyTextBox
end sub

But your class will be able to capture the events for that, textbox.

Also going back to the MDA. I read up on it some more. If you build your library DB with just code, you should still deploy it as an MDA. There is a little to doing this, and would recommend a good resources such as the Desktop Developers Handbook (Letwin and Getz). I found no good resources on the Net.

 

[1DMF]

Well I still can't seem to get my class to work, I'm unable to declare a variable as the class type from the main MDB.

I used the 'references' section and pointed it to the MDE with the class code in it.

But then in the main MDB although in the tree on the left I can see the references to this MDE, it isn't allowing me to declare any variables of the class type?

error is...

User-defined type not defined

I must be doing something stupid, but not sure what it is?



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[1DMF]

aha , I think!

I was trying to declare the public variable in the main DB 'global code' section, now although I want the main DB to use this var, its delaration has to be in the 'global code' part of the 'library' MDE.

Even though it is delared in the same MDB as the class code resides it can be seen from the main DB which references it.

I guess inheritance and scope isn't quite as you'd expect in MS Access either.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Modules: Creating a reference to a Class in a Library database

Author(s)
Terry Kreft


(Special thanks to Steve Arbaugh for pointing this out online, based on the findings of Michael Kaplan originally.)

When we set a reference to library (MDA) database, normally the class modules in the MDA are not visible, while standard modules are. In order to create an instance of a Class residing in an MDA, you have to follow one of these two ways.

The easiest way is to write a function in your Library db which returns an instance of your class.

The second one is a bit more involved.

1) Select the Class module in the DB window
2) Choose the "File, Save As/Export..." menu item
3) Choose "To an External File or database" from the dialog and click OK.
(Should be the default anyway)
4) Change the Save as type to "Text Files ..." and click export
5) Locate the file you have saved and open it in a text editor
At the top of the file you will find the following line
Attribute VB_Exposed = False
Change this to
Attribute VB_Exposed = True
and save the file
6) Rename your Class module
7) Create a new empty class module with the old name
8) In design view for the new Class module -
Go to the "Insert, File..." menu item and select the amended text file.

That is it, you now have an externally creatable class.