VBA scripting for MS Office 2000 (MSAccess v9)

Applicant must be familiar with MS-Access 2000 (Ver [url removed, login to view]), Scripting in VBA, and interested in lucrative follow-up work. This is a fictional project to evaluate the different approaches and competencies of various bidders.

A small widget marketing company require code which will allow them to incorporate text files of sales data into MSAccess for report generation.

They require an MS Access form to read data from a user supplied text file. The text file will be [tab] seperated data with CRLF's at the end of each record.

The form will allow the user to input a file location (With the option of browsing to the file location) and will then show a brief summary of the file (its date from the first line, the group from the second line and the number of records the file contains) - A button marked [IMPORT] will then 'enable'

and clicking this will add the files records to an existing MS-Access table. Alternatively a [CANCEL] button will be available which will close the form.

The file format is as follows. Firstly, the file will have a two line header :

Date : [tab] dd/mm/yy [crlf]

Group : [tab] text [crlf]

Followed by a [tab] delimited and CRLF terminated list of field names, such as:

firstname [tab] lastname [tab] age [crlf]

Followed by multiple records in the same format, such as:

John [tab] smith [tab] 32 [crlf]

Indiana [tab] jones [tab] 21 [crlf]

Helen [tab] troy [tab] 23 [crlf]

which will continue to the end-of-file.

Two tables will be created/updated by this form:

'Users' - Which will hold a single record for each user comprising of the following fields :

- A primary autoincrementing key

- Firstname

- Surname

- Age

And a second table 'records' which will comprise of the following fields :

- A reference to the user to which this record relates

- date

- sales

As you can see 'users' has a one-to-many relationship with 'records'

If the tables do not exist they will be created. The data in the top line of the file ('date :') will be treat as an additional field to be included for each record.

for example, the textfile :

date : [tab] 01/01/99 [crlf]

group : [tab] blah [crlf]

firstname [tab] lastname [tab] age [tab] sales [crlf]

John [tab] smith [tab] 32 [tab] 10 [crlf]

Indiana [tab] jones [tab] 21 [tab] 20 [crlf]

Helen [tab] troy [tab] 23 [tab] 30 [crlf]

would show the summary 'You have selected file c:[url removed, login to view] which contains 3 records dated 01/01/99'

And result in the following tables being created in MS-Access.

'users' table:

1: John Smith 32

2: Indiana Jones 21

3: Helen Troy 23

'records' table

1: (1) 01/01/1999 10

2: (2) 01/01/1999 20

3: (3) 01/01/1999 30

... where the bracket numbers indicate a pointer to a record in the 'users' table

If we were to add the next days textfile (02/01/1999) the 'users' table would not change (Unless a new username was encoutered) but the 'records' table might look like this:

1: (1) 01/01/1999 10

2: (2) 01/01/1999 20

3: (3) 01/01/1999 30

4: (1) 02/01/1999 5

5: (2) 02/01/1999 10

6: (3) 02/01/1999 15

The handling of duplicates. Whilst the table will eventually contain multiple dates for each username, as seen above, any record which contains a date -and- user combination which already exists in the database will be considered an amendment and will replace the existing record:

This, adding (2) 01/01/1999 22 to the previous table would result in :

1: (1) 01/01/1999 10

2: (2) 01/01/1999 22 &lt;--- an amended entry

3: (3) 01/01/1999 30

4: (1) 02/01/1999 5

5: (2) 02/01/1999 10

6: (3) 02/01/1999 15

not

1: (1) 01/01/1999 10

2: (2) 01/01/1999 20 &lt;--- Error

3: (3) 01/01/1999 30

4: (1) 02/01/1999 5

5: (2) 02/01/1999 10

6: (3) 02/01/1999 15

7: (2) 01/01/1999 22 &lt;--- Error

The final consideration is that field order MUST be checked and any unexpected fields ignored. This is important as field order cannot be garaunteed and some textfile reports may include additional information which is surplus to requirement.

Upon request I shall provide several text files which, when added via the form, will result in a given final table in order that you can verify your script against my requirements.

There is a considerable amount of future work available to the chosen bidder as we are migrating our report-generating software and require various new ASCII reports pulled into MSAccess in differing ways. I may accept more than one bid for this work and will favour the bidder whom I feel has submitted the better design (So you may see this project relisted if I can only accept one bid at a time - if so, previously accepted bidders should not apply as their submission is already noted)

Please note that the examples in this project have little to do with the output from Avaya definity callcenter reports which we will finaly require processing, however, the work is similar enough to verify competence.

Future work may involve handling real-life reports from our callcentre and so bidders must be prepared to sign a standard non-disclosure document upon being accepted for further work. Whilst there is no obligation to accept further work from us although we do ask that you only bid on this project if you are interested in more lucrative work of a similar nature.