Comparing multi paradox files

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hello all,

Thanks in advance for any help you are able to give.

I am new to coding VBA, but think that access is going to be the
easiest way for me to compare the database files.

My problem is, I have anywhere form 5 to 250 seperate paradox files and
they have to compare across 30+ different groups. I need to compare 1
field in the file(it is in the same place for all files) with a master
list.

Here is little more detail.
Each machine and a location creates a paradox file of its settings.
Each location has different settings that need to be compared.
Need to output a single report with each machine and its differences
form the master list.

Here is how I get the paradox files, each location has a master folder
with each machine having its on folder, then then 1 paradox file for
that machine in that folder.

I have been asking around the office and some of my friends, but the
biggest problem we run into is importanting each file. I have tried
both looking at using access or excel to do this, but when I go to
import to excel it is unable to get pull anything form the file, when I
import to access I get all the data from the file.

Now each file is named the same way, but each folder is unique name
with the machine serial number.

Any help will be greatly appriciated.
 
Update to Comparing multi paradox files

Ok I wrote a batch file to change the name of each file to the machine
number then move each fill into to one folder.

Then I manual linked each table into Acces.

then I found a code in my VBA book that helped me pull the data form
one file in the database into an excel sheet. If I could can get all
the tables into the workbook with out having to write code for each one
I think I can write code that gets just the data I need into one sheet.
from here it would be easy to look across each line and see which
values are not equal.

Here is my code.

Sub RetrieveTable()
Dim rs As Recordset
Dim strCriteria As String
Dim recordArray As Variant
'sets rs to pull data form database
Set rs = CreateObject("ADODB.Recordset")
'what is rs
With rs
.Source = "840006001" 'first table name
.ActiveConnection = "Parameter" 'database that has the table
.CursorType = adOpenKeyset 'how to open database
.Open 'get the table
End With
'with first worksheet in book
Worksheets("Sheet1").Activate
With Worksheets("Sheet1").[a1]
recordArray = rs.GetRows(800) 'number of records to get
For i = 0 To UBound(recordArray, 2) 'get all fields(find way to get
just field 4 and name heading with source name
For j = 0 To UBound(recordArray, 1)
.Offset(i + 1, j) = recordArray(j, i)
Next j
Next i

For j = 0 To rs.Fields.Count - 1 'sets field names
.Offset(0, j) = rs.Fields(j).Name
.Offset(0, j).Font.Bold = True
.Offset(0, j).EntireColumn.AutoFit
Next j

End With

rs.Close 'closes table

Set rs = Nothing 'reset var rs

Columns("F:I").Delete 'delete last columns of data(won't need if only
field 4 is pulled

End Sub


Thanks for any help.
 
Back
Top