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)
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
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.