Consolidating data from different databases

[Lord Byron]

Dear All,

I have created a survey in Access 97. This survey needs to be filled in by different users(over 1000) in different locations (150 locations).
The problem is that I need to consolidate all that information in 1 database.

Is there a way to automatically import the data from those different databases? I would prefer not to go through the hassle of doing this manually.

Many thanks in advance.

With kind regards,

Lord Byron

 

[cogent1]

This can be done with append queries. You have to make sure that the tables in all the donor databases are the same for names and datatypes as those in the host database. Then the process can be safely automated.

 

[Lord Byron]

And how would I set up these append queries to take data from the donor databases? Would this have to be done donor-database per donor-database, or is this automatically for all the donor-databases?

The table names and datatypes are exactly alike.

Thanks

 

[cogent1]

If you look up TransferDatabase in Help, it gives you the basics of transferring and linking data from donors to hosts. If you wish to automate the process, you'll have to write a function which imports\links each block of data(i.e. each table), appends it to the master table using a standard append query and then looks for the next block. If you have any familiarity with VBA, this should present no problems. If you don't please post again, and I'll be more specific.

 

[The_Doc_Man]

Or, you could set this problem up ahead of time just a little bit differently. How were you planning to get all 150 sites worth of data to your central site? File sharing across a network? E-mail the .MDB files (shudder)? Direct links to each database?

You might make your life simpler by doing an export of your table to Excel, then importing (Append to Existing Table) each file. Less to transfer that way. Then you could e-mail the workbook file (and because it is smaller, I don't shudder at that thought.)

You can write some VBA code to do .FileSearch operations on the spreadsheets. Find them one at a time, do the Import operation, then do a file Copy from your target directory to a historical directory. Or do a Name file1 As file2 to change the name to indicate that it is USED already.

 

[Lord Byron]

The_Doc_Man: I want to receive the submissions as a .mdb file per email. One of the major problems is that those 150 sites are actually located in 150 different countries. So not all of these are located in Europe or Northern America. Most of them are located in Asia and Africa.

Another problem is that working with an excel sheet is not as easy as it seems since every .mdb file contains around 17 tables which are all linked to eachother. This might not be a problem, but it would generate more work (or so I believe).

A client-server kind of methodology cannot be used since it are 150 different sites with each their own network, although linked to 1 central WAN. But all have their firewalls etc...

We have done some serious brainstorming on how we would present our users with these questions, as to make it as easy for them to fill in as possible. This unfortunately leaves me with the task to make things as easy as possible for us here at the office.

cogent1: I will have a look at this. If I have any further questions on this, I will post them here. Thanks for the tip.

 

[Lord Byron]

cogent1: I have a small question. To automate this, would I have to use these commands for every single database, or is there some kind of a loop that I could use to go through a whole list of files (preferrably also looking in subdirectories)?

This offcourse providing that I already use a function that takes the source database as a parameter to execute the different commands for the different tables with.

I have some VBA experience, but am not quite an expert in this

Thanks in advance.

 

[The_Doc_Man]

As to finding database files, you can do this with a .FileSearch (look it up in the Help files) but it does require some VBA work.

The problem is the same for 2 or 150 databases. If you design it so that linked fields based on autonumbers CAN overlap, they WILL overlap. (Per Murphy's first law.) Then you have a true nightmare on your hands.

If you haven't sent out the databases yet, there is hope because you can redesign things to meet your needs. If you HAVE sent it out, you just gave yourself a massive headache.

The ideal case is that each database has a different name based on some common prefix (like "SURVEY") plus a suffix that identifies the country or locale. So the final file name might look like SURVEY_001 (or SURVEY_London, SURVEY_Paris, etc.)

You could use a .FileSearch operation to find wildcarded file names based on a template of SURVEY_* and type Access DataBase files. So that part would be easy enough.

You can get the file name string out of the .FileSearch intact and parse it. Scanning from the rear, find the dot (.) that delimits the file type. From there, continue to scan to the front until you find the first \ / or : - then the text in between those two positions is the file name. Find the underscore. The part to the right is your suffix.

You would do this because in your MASTER database, your linkages aren't based on just the autonumber field. They would include the suffix code of the database from which the data came. You would have to redesign your MASTER copy so that the primary key is the autonumber field and the suffix field. All tables have to have the SUFFIX field to identify their sources. Then, you can just do a straight import of everything else, plus supply the suffix for each table as a constant.

Now, if you haven't sent everything out yet, you can do this ahead of time and make it even easier. In VBA you can write code to determine the name of the current database.

Dim dbCur as Database
Dim stDBName as String

Set dbCur = CurrentDB

stDBName = dbCur.Name

Now you could do that same analysis above IN EACH DATABASE (say on a FORM LOAD event) to supply the name of the database so you could just plunk that suffix directly into the remote databases as you store the data. Then the import doesn't need to know the suffix separately and you could directly append the data. The catch is that for the master table, Autonumber fields have to be Long (integer) fields instead. And your MASTER db CANNOT be used for actual survey data entry because doesn't have an autonumber field.

I know that was kind of confusing, but this is the only way I know of to allow the massive kind of import you are facing with the least amount of pain.

Remember Murphy's First Law and Murphy's First Edict - they go together here: "If it CAN go wrong, it WILL go wrong." (So... "Build it so that it CAN'T go wrong in the first place.")

Good luck. If you have already sent out the databases, you are going to need it.

 

[Lord Byron]

The_Doc_Man: The survey databases do not use an autonumber field, I already thought of the possible problems with that

The survey consists out of 2 parts. They have to fill in a part for the site itself. This should only be done once, and therefore I have designed the form so that they can only enter one record.
After that, they need to fill in the details for every user. On this form, they need to select their site from a dropdown box which looks at the other part of the survey. This box should only have one option.

As a unique identifier, I have programatically created a key consisting out of the site-code and their full name. This is done out of their sight. (I would have chosen to use their email address, but in some African countries they only have 1 email address for the entire office - the problems of working for a truly global company I'd say )

On top of all that, I have created a double check on the main form, which checks to see whether there is only 1 site entered, and whether the total number of users they entered matches with the total number of users that was entered with in the site part of the survey.

The users should only send back one copy of the survey (and only after they get the message finished on the main form), and since we have a list of contactpeople (1 per site), we will only accept the copy they send back. They would need to include a screenshot of this "finished" message with the email or the submission will be rejected.

Now to begin with, we only have 1 copy of the survey that is to be sent out. This copy will be downloadable from our intranet via a password protection. This way we can also see whether they have downloaded the survey to begin with.
I was thinking of placing the completed surveys all in subdirectories, where the directory name is the code for the site of the survey. This way, all "source" databases would have the same name.
Now offcourse placing these files in the correct directories is a terrible task to do, but thank god for students during the holiday period
Now would the solution you propose still work in the scenario of these subdirectories?

Furthermore, the survey database has already been tested by around 10 people from the office here (most of them know absolutely nothing about access etc. - to make sure that I could trap any errors). This especially done in mind of Murphy's law. I still remember surveys that were sent out (not prepared by me) and caused complete distasters. Needless to say that I will do everything I can to prevent this from happening

Do you have any other ideas of what I should do to prevent this survey from becoming my worst nightmare? If so, they would be highly appreciated.

Thanks.

 

[cogent1]

Doc_Man has more or less summed it up, I think. In any case, you seem to have a well-thought-out plan of action.

If you want to adopt a more measured approach, you could consider using the Common Dialog Control, which gives you a familiar interface for navigating to, and selecting files manually. This might not be too onerous if you receive the files in dribs and drabs.

You might also consider how you are going to deal with files that have been processed if this is not going to involve a one-off operation. Moving them to another directory or renaming them, perhaps.

When it comes to the appending, you have to pay due attention to the sequence in which the data is appended to the tables. If you attempt to append records on the many side of an integrity relationship before the one side records are in place, the append will fail and may well do so without a warning.