Is it possible to create a generic module available to a number of databases? (1 Viewer)

whitespace · Sep 15, 2005

I want to create a number of functions that can be called from a number of different databases.

At the moment we store the same functions in a module in each of the databases but when we want to change something we have to update them all which is not only time consuming but it is easy to miss out or forget about databases. We have ended up with lots of versions of the same function spread across a number of databases - if that makes any sense?

In the same way that you have a linked table, is it possible to create a 'linked' module so that all databases can look at one source for functions? Failing that can I create a module that can be called by making it available in the references dialog box (in a similar way that you make the Excel object library available) - I don't know much about that - is it creating a class or something and/or will I need VB6 to do it?

Thanks loads for any help

-Suzy

Mile-O · Sep 15, 2005

Been a while since I used Access, but, if I remember correctly, you save your database with the module as an MDE, then add it to the References.

whitespace · Sep 15, 2005

Thanks SJ, that works great - although I think some users are running Access 2000 - so may have some problems with that - but thanks a lot.

Just out of interest though - was I barking up the wrong tree with classes? What are they used for?

Mile-O · Sep 15, 2005

whitespace said:
Thanks SJ, that works great - although I think some users are running Access 2000 - so may have some problems with that - but thanks a lot.

You should be able to convert back to 2000, make an MDB, and use that as a reference too.

was I barking up the wrong tree with classes? What are they used for?

Yes. Classes Modules are used to encapsulate code so that coding elsewhere is easier, and you can just call in the function(s) under the Class.

An example would be creating a class, named CRecords like this:

Code:

Option Compare Database
Option Explicit

Private cn As ADODB.Connection
Private rs As ADODB.Recordset

Const strRecordset As String = "qryMyRecords"

Private Sub Class_Initialize()
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open strRecordset, cn
End Sub

Private Sub Class_Terminate()
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

Public Function Records()
    Records = rs.Recordcount
End Function

A crappy example, but it'll suffice.

Then, with your new class module, you could use it in a module:

i.e.

Code:

Dim clsRecords As CRecords
Set clsRecords = New CRecords
MsgBox clsRecords.Records
Set clsRecords = Nothing

Is it possible to create a generic module available to a number of databases? (1 Viewer)

whitespace

Registered User.

Mile-O

Back once again...

whitespace

Registered User.

Mile-O

Back once again...

Similar threads

Users who are viewing this thread