Loop dataset and multiple databases

In transistioning from an Access environment to Data Point, I’m trying to replicate some of the functions I routinely use. One of them being a loop array in Access VBA. I’ll try to best outline what I do in Access first.

Our databases (DB2) are mirrored across different sites due to geographic locations being managed by different groups. For example, database 123 correllates to Florida customers and database 456 correllates to California. I have a database array table (DatabaseArray) created for this:

| ID | DBName |

| 1 | 123 |

| 2 | 456 |

I use this in vba as recordset:

Dim arrCount(1 To 2) As Long
Dim db As DAO.Database, rst As DAO.Recordset
Dim intCounter As Integer, lSum As Long

Set db = CurrentDb
Set rst = db.OpenRecordset(“DatabaseArray”, dbOpenDynaset)
rst.MoveFirst
For intCounter = 1 To 2

I then use the the DBName field as a variable in my SQL:

SQL = "select distinct name, address "
SQL = SQL & vbCrLf & " & rst!DBName & ".Customers "
SQL = SQL & vbCrLf & “WHERE name = ‘John Doe’”

Next I store this into a temporary passthrough query:

Set Qdf = CurrentDb.QueryDefs(“qry_Customers”)
Qdf.Connect = “ODBC;DSN=StateSubs;UID=user; PWD = password”
Qdf.SQL = SQL
Qdf.Close

I then parse these results into my static table for later analysis:

DoCmd.RunSQL ("INSERT INTO mktbl_Customers (name, address) " & _
“SELECT name, address FROM qry_Customers”)

This goes through and runs database 123 first and then 456 second. I noticed that it seems to run much faster this way versus creating a union for both databases. Obviously this is very genearlized, but with my larger more complex queries, this saves me from having to create unions. Those just leave more room for error. I’m looking to see if I can duplicate these types of actions in Data Point.

I watch this video: http://www.toadworld.com/products/toad-data-point/m/media-library/1133.aspx but I’m not having much luck with trying to implement the variable as the database name rather than in the where clause. I also want the results dumped in to a local storage table before I finalize any network output.

Any help or tips would greatly appreciated!

Thanks in advance.

I probably see at least one solution but it heavily depends on how big is your array table (DatabaseArray) is and if this table is mostly static or not. So,

  1. How many rows this table holds?

  2. Is this table changable? If yes, how often approximately?

Thanks,

Igor.

Thanks Igor, The array table is always going to consist of 1 - 5 db’s (rows). I’ll probably just house it in local storage and it will be a static table. Since it only contains the db names, the only changes that would occur if new db’s are added, which are very few and far between.

Thanks,

Chris