My initial task is to create a standard process to normalize the fields, datatypes, and values of Visual FoxPro datasets so they can be ulimately merged into one dataset. This process will be used by several people who are not necessarily technically inclined.
My logic:
For each client dataset
For each table named in FoxTableList (a list of the tables to import)
Append all records to its corresponding Access table
Then run queries on the appended tables to find anomalies
There are numerous clients, with varied numbers of datasets.
Client
Dataset1
Table1
Table2
…
Dataset2
Dataset3
And so on...
Most of the tables are in the DBC container for each dataset, some are free tables
Using the Visual FoxPro ODBC driver, I am able to import a single dataset, using pass-though queries, however, I cannot change the ODBC path to get to the next dataset without changing the path, closing Access, then relinking – sometimes twice.
So I am using an ADODB connection to get to the tables, can modify the connection string to get to the next dataset, and can Debug.Print the data using a standard SQL string strSQL=“SELECT * from tblName”.
This is when the trouble begins. I need to append the data from the recordset into a table.
A simple INSERT INTO should work (no, of course not). Whenever I modify strSQL, or create a QueryDef, the modified strSQL and the QueryDef cannot see the table and generates the error
“…Jet cannot find the input table or query “Areas” (Areas is the first table).
Since the FoxPro tables have a different number and types of fields – and records, I want to put them into an array and read from that. However, I’m so frustrated I can’t even comprehend the concept of how to use the array.
Any and all suggestions are appreciated!
The following code runs (I’ve removed the error handling from this snippet)
My logic:
For each client dataset
For each table named in FoxTableList (a list of the tables to import)
Append all records to its corresponding Access table
Then run queries on the appended tables to find anomalies
There are numerous clients, with varied numbers of datasets.
Client
Dataset1
Table1
Table2
…
Dataset2
Dataset3
And so on...
Most of the tables are in the DBC container for each dataset, some are free tables
Using the Visual FoxPro ODBC driver, I am able to import a single dataset, using pass-though queries, however, I cannot change the ODBC path to get to the next dataset without changing the path, closing Access, then relinking – sometimes twice.
So I am using an ADODB connection to get to the tables, can modify the connection string to get to the next dataset, and can Debug.Print the data using a standard SQL string strSQL=“SELECT * from tblName”.
This is when the trouble begins. I need to append the data from the recordset into a table.
A simple INSERT INTO should work (no, of course not). Whenever I modify strSQL, or create a QueryDef, the modified strSQL and the QueryDef cannot see the table and generates the error
“…Jet cannot find the input table or query “Areas” (Areas is the first table).
Since the FoxPro tables have a different number and types of fields – and records, I want to put them into an array and read from that. However, I’m so frustrated I can’t even comprehend the concept of how to use the array.
Any and all suggestions are appreciated!
The following code runs (I’ve removed the error handling from this snippet)
Code:
Function FoxHunt4()
Dim Cnxn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim rstCnt As Long
Dim varValues, varRow As Variant
Dim intRowCount, intFieldCount, i, j As Long
Dim strCnxn, strFox, strMyTable As String
‘strFox and strMyTable will be passed, here only for testing
strFox = "Areas"
strMyTable = “myAreas”
'VFP connect string
strCnxn = "Driver={Microsoft FoxPro VFP Driver (*.dbf)};" _
& "SourceDB=G:\LES\Data\Some.DBC;SourceType=DBC;"
Set Cnxn = New ADODB.Connection
Cnxn.Open strCnxn
Set rst = New ADODB.Recordset
rst.Open strFox, Cnxn, adOpenKeyset, adLockReadOnly, adCmdTable
rstCnt = rst.RecordCount
varValues = rst.GetRows(rstCnt)
intFieldCount = UBound(varValues, 1)
intRowCount = UBound(varValues, 2)
‘This is where the data should be appended to strMyTable
For j = 0 To intRowCount
For i = 0 To intFieldCount
Debug.Print "Row " & j & ", Field " & i & ": " & varValues(i, j)
Next
Next
Debug.Print rstCnt
rst.Close
End Function