MS ACCESS 2003 triggers (Query Event), and Excel i

Question

I am working on a project in a company, and because of such i am bound to use MS ACCESS 2003 operating on XP (although, i don't think the OS is relevant here). Because the company is using other application (and also external sources), the data that is used as input in the table is not always sanitized. However, i noticed we can always get the data in .XLS (excel) format.

The Database was composed of more than 40 tables, with redundancies, no keys, and no indexes; in other word it was a mess. After a lot of troubles I was able to improve the design and reduce the number of tables.

Yet, I found myself facing few challenges. Most of these challenges could be overcome with triggers, but after reading many answers in different forums I came to understand that they do not exist in access 2003 and should be replaced with queries linked to the forms. One of the problem with such a solution is that it needs a form and a button. So I thought, I would implement such queries when importing the data from the excel files using a form to do so. Since the tables are not exactly following the format of the excel files, I need directions to learn how to import specific excel columns from specific sheets in changing directories to the corresponding columns of the tables in the Db.

Also, and this is where it gets more challenging (at least for me), I have different tables that are not directly connected (because of for example bridge tables), yet I still need to keep some integrity between the data of all tables. If we have table A, B, C; table A being connected to table B that is connected to table C: I need the Rows of table C to be inserted or deleted upon insertion of rows in table A. Also, I need some columns of table C to be consistent (in term of values) with columns of table A. I know this might seem at first like a problem in design, but I assure you it is not. I am sorry for not giving details of the tables but I signed a non-disclosure clause.

Finally, and this might seem like a “stupid” question, but I can’t find in access a place in the table design to impose some mathematical validation rules on some columns depending on other columns. I found that we can have validation rules on a single column (for example Is Null Or >= 0), but for some columns their values should depend on other columns. For example, column B should equal 1.2*column A(sometimes these columns are in the same table, but not always).

Thank you for your help. I am much grateful for any assistance you can provide in any of the problems I am facing; and I stay at your disposition for any additional piece of information you might need.

Answer 1

I agree with user2174085, there is quite a lot in your question.

My approach would be to model your data in classes and use these to write to your database. This would allow you to validate your data as you require, and to import the data into as many tables as you require.

This approach is standard in CRUD applications.

Here is a simple example for illustration purposes with the steps (I hope) clearly described):

1) Define our example database:

Table1: Clients (ID, Name, AddressID)

Table2: Addresses (ID, Street)

2) Define our example spreadsheets:

Clients: ID, Name, AddressID 1, Bob, 1 2, Jim, 2

Addresses: ID, Street 1, Gold Street 2, Derp Street

3) Define our 2 classes which allow us to model a single Client and a single Address.

To use these in MS Access, add a class in the code editor window and paste the below classes in. For more information this is a good vba classes resource:http://www.cpearson.com/excel/classes.aspx

Client class:

Option Compare Database
Option Explicit

Dim intID As Integer
Dim strName As String
Dim intAddressID As Integer

'here we define a query (QueryDef) which is in the database (usp_Clients_InsertRecord), populate its parameters from the class variables and execute it to write the record
Public Sub Insert()
    Dim qdfTemp As QueryDef
    Set qdfTemp = CurrentDb().QueryDefs("usp_Clients_InsertRecord")
    With qdfTemp
        .Parameters("pName") = strName
        .Parameters("pAddressID") = pAddressID
    End With
    qdfTemp.Execute
End Sub

Address class:

Option Compare Database
Option Explicit

Dim intID As Integer
Dim strStreet As String

'here we define a query (QueryDef) which is in the database (usp_Addresses_InsertRecord),     populate its parameters from the class variables and execute it to write the record
Public Sub Insert()
    Dim qdfTemp As QueryDef
    Set qdfTemp = CurrentDb().QueryDefs("usp_Addresses_InsertRecord")
    With qdfTemp
        .Parameters("pStreet") = strStreet
    End With
    qdfTemp.Execute
End Sub

You can perform validation at this point, you could for example create a client record, then call a validate method that tells you if the data is ok.

The reason the ID is not written is because it is an autonumber in the database. It is still included in the class because we may need to create a client / address record from data in the database, and use its information to write another record. For example, to write a client record, we may need to retrieve an address record, complete with AddressID that is used in writing the client record.

4) The above classes use queries (stored procedures) to write to the database, here is an example query:

usp_Clients_InsertRecord

PARAMETERS pName Text (255), pAddressID Long; Insert into Clients(Name, AddressID) Values (pName, pAddressID);

5) This is all well and good, but how do we get our data from excel, into the class, to write it to the database? To do that we use managing classes, these are classes that contain a load of Client or Address records, loaded from the spreadsheet and stored in a collection. This collection is looped over, calling the Insert method to write the records to the database.

This provides an answer to your question of how to write data to tables A, then B then C. You would create 3 classes, populate them with data, then write A to the database, then write B using the ID of the last written record to table A (retrieved using a query) and so on.

Here is an example of a managing class:

Option Explicit
'our clients collection
Private mcolClients As Collection
'adding objects to the clients collection
Public Function AddByParameter(byval Name as string, byval AddressID as integer)
    dim objClient as Client
    Set objClient = New Client
    with objClient
        .strName = Name
        .intAddressID = AddressID
    end with
    mcolClients.Add objClient, Name
end function
'constructor
Private Sub Class_Initialize()
    Set mcolClients = New Collection
End Sub
'you need this to be able to iterate over a collection
Public Function NewEnum() As IUnknown
  Set NewEnum = mcolImportQuestions.[_NewEnum]
End Function
'you can then iterate over the collection, calling the insert method on each record:
public Sub InsertAllClients
    dim objClient as Client
    for each objClient in mcolClients
        objClient.Insert
    next objClient
end function

I think I've gone a bit, if this is useful please let me know / ask questions and I can flesh it out a bit :)

Answer 2

Honestly, you might want to ask each of these questions separately. We all have day jobs and can only dedicate a little bit of time to help! :o)

The one question I can quickly answer is the one about data integrity. If you click on Tools and then Relationships, you can set up relationships between your tables. It is in these relationships where you can "enforce referrential integrity". This is where Access will automatically delete records in related tables to keep your data clean.