Sql query or VB script? How would I do this and what would the code be?

Question

Hi,

I have 5 databases (Queens, Brooklyn, Manhattan, Staten Island, Bronx). All 5 of these are Access databases. There was originally just one database but I broke it into 5 because the original file was 2GB. I have created a list of about 3000 names that I want to search through the five databases. This list is in an Excel spreadsheet named Names.xls. What I want to do is take the names from the spreadsheet and search for them in the different databases as follows:

1) First look through the Brooklyn database and find all the records that have a last name that matches any of the names on the spreadsheet.

2) Then after that is done and the records have been pulled. I want to search through the same Brooklyn database and find all the records that have a first name that matches any of the names on the spreadsheet and combine the results of these two searches together into a new database called names_1.

3) Then I would like to take all the records that were pulled and do a search using the first names that were pulled from search 1 & 2 and search for them through the whole Brooklyn database (basically to make sure I have all occurrences of each name), and any new records that are pulled I would want them to be put into a new database called names_2

4) Then I would like to take all the leftover last names from the Brooklyn database and compare those last names with all the other databases (Queens, Staten Island, Manhattan, Bronx) and any last names that are the same I would like them to be removed from all of the databases.

5) I then want to take all the leftover names after the deletions and put them into a separate database called names_3

6) The fields in the Brooklyn database are called [first name] [last name] .

Thanks for any and all help

Answer 1

Hi !

I understand that you have five Access databases (MDB's I guess). Why don't you create 5 ODBC connections to these database files ? Just put some buttons in the excel spreadsheet, implement the event handlers in VBA and you can access the databases using the created ODBC connections. I think this method should save you much time, because I don't see any problems with it. Just open 5 connections to all ODBC connections, and do whatever SQL you like.