Storing Collections through VBA in Access

Question

In my Access 2013 Desktop database, I want to validate an Excel form through VBA before importing its rows to a table.

The problem is that the imported Excel table may contain in the same column values that are numeric, string, null, etc. depending on the row due to mistakes from the user, so these values have to be initially of VBA type Variant before validation.

If the row is invalid (e.g. one value is not of numeric type while it should be, one mandatory field is null, etc.), I want to be able to store its values and let the user correct it later on. I am therefore looking for a way to build a table where I could store these rows, but where the fields are kept intact, so with the possible mixed data types.

This seems not to be possible, as Access tables need apparently to have fields of a defined type, so nothing equivalent to Variant.

How could I achieve this? What would be the simplest and most efficient way to store rows of Variant values while keeping the structure of the columns? The only way I could think of so far is to concatenate these values for each row in a string using some defined separator, and store this in a String column in an Access table, but maybe there is an easier way to do?

Thanks a lot for your help.

EDIT: Reading your answers I realized that I forgot to specify that the people filling and sending the Excel forms (who typically do not have Access on their computers) are not the same users than the ones who have the Access database and need to import the data. In this configuration it is impossible to use directly Access forms to input data, which would of course simplify tremendously the task. While we try to enforce rules for people who fill the Excel form at the first place there will be mistakes and the Access application needs to cope with them. And the importing process for the users manipulating Access should be as simple as possible (these users are not experts in IT).

Answer 1

I though about your problem for a while and made a few notes as I thought below.

I would strongly advise that unless the excel workbook is providing other functionality that you do not use excel, but create an simple Access database with a form they can use to interact with "their" data. They do not necessarily need an Access license to do this. Access 2013 even has new web features that can be used if you have sharepoint to collect data via a Access created web frontend.

If you are to collect data in Excel, then you must use excel validation and VBA code to validate the data as much as possible before transferring to access.

There are complexities that may or may not be an issue for you, things like:

1. Can users edit/created/delete the same rows concurrently - what happens if they do! You may need to "lock" rows when they are downloaded from Access. But what happens if tow users add the same record, or ones deletes a row before another user commits it back again.

2. Can a user open multiple excel files and edit the data they store without committing the data to Access?

3. Can multiple users login to the same Workbook with uncommitted changes and edit the data.

Using access will simplify your code as it will be able to prevent erroneous data being entered and remove the need to deal with the above issues and others

In summary you are using excel as a front end "form" to data stored in Access. Each excel file can have data that has yet to be up

I would suggest that the primary key of a row is the "path and name" of the excel file that was used to create it & a unique numeric identifier. The unique identifier is a counter that is maintained for each excel file. The "path and filename" could be replaced by a unique identifier created for each file.

Many users enter data using multiple instances of Excel into a form which results in one or more rows being updated/inserted/deleted in an Excel table stored in each spreadsheet.

I would expect that whenever Excel is opened the user enters a username and excel will grab "their data" from Access. Alternatively a workbook might be set up for each department or "case type" and only interact with data that matches this "custom criteria set up in the workbook". Excel would not necessarily need to store data itself longterm. The workbook might always save data back to the database when it is saved.

You say Excel VBA performs limited validation on the data (but no complete validation). It should be used as much as possible and arguably should be able to do exactly the same validation that access can do. At the very least it should enforce datatypes etc (eg using the standard data validation rules on the excel data menu) or perhaps use VBA/controls to get any access data it needs to validate the data entered.

After updating the data the user can "commit" (ie save) the data to the access database. Before closing the workbook you might want to commit the data. (An issue is whether a user might open many workbooks perhaps on many machines without committing the data.)

An Access "staging" table can be created with all columns having the datatype "shorttext" and not-required.

The process that loads data from an excel table into Access, will uploaded all excel rows into this staging table. It will then validate each row in the staging table and process all valid data into the main table(s) that have data types, relationships constraints etc.. Any valid rows in the staging table are flagged as "VALIDATED & TRANSFERRED", Invalid rows are flagged as INVALID. The "VALIDATED AND TRANSFERRED ROWS" are subsequently ignored, but kept so you can check what processing has happened, perhaps only whilst testing.

The data in excel is then updated with the Valid/Invalid status from Access and suitable messages given to the excel user. The user may correct and then re-commit the data.

Each excel file has a status of "changed/Unchanged" to indicate whether a user has changed data in the file.

When a user opens an excel workbook and status is unchanged it will refresh it's data. If it's changed a refresh probably can't be done.

In order for the data in Access to be updated/deleted/inserted with the changes made in Excel, there will need to be a unique identifier for each row that cannot be changed by the users in excel. This is likely to be the Path&Filename or the UserName logged into Excel and a numeric counter (which is maintained for each file or user). (This assumes that the user will have to commit changes in one excel file before they work on another.)

Anyway, without knowing more it's difficult to fully design what you will need, but I hope these thoughts help you

Harvey