Comparing Two Access Tables?
Jim:
Use the recordset Fields collection.
In DAO, below is a direct extract from one of my applications that reads
the field names, types and sizes from the table. I was using .DBF
files, but it would only require a slight change for Access tables.
' Open Database, using Datapath
Set dbs =
DBEngine.Workspaces(0).OpenDatabase(frmSelect.cCL.DataPath, False,
False, "dBase IV")
' Open Recordset
Set rst = dbs.OpenRecordset(m_DBF_File, dbOpenSnapshot)
' Determine number of fields in database
m_NumFields = rst.Fields.Count
' Redim arrays for field names, types, sizes
ReDim m_FieldNames(m_NumFields - 1)
ReDim m_FieldTypes(m_NumFields - 1)
ReDim m_FieldSizes(m_NumFields - 1)
' Get field names, types and sizes
For i = 0 To m_NumFields - 1
m_FieldNames(i) = rst.Fields(i).Name
m_FieldTypes(i) = rst.Fields(i).Type
m_FieldSizes(i) = rst.Fields(i).Size
Next i
' Close database
dbs.Close
In ADO, if your table is attached to an ADO data control, you can
similarly access the field names, types and sizes using (for the first
field):
FieldNames(0) = Adodc1.Recordset.Fields(0).Name
FieldTypes(0) = Adodc1.Recordset.Fields(0).Type
FieldSizes(0) = Adodc1.Recordset.Fields(0).DefinedSize
I trust this helps.
John....
Quote:
> Im converting an older AS/400 RPG program to
> Visual Basic. Right now, both rely on the same
> database (but each has its own copy). My job
> is to rewrite the old RPG program using VB. The
> VB programs output must match the RPG programs
> output exactly.
> I would love to develop a tool that can automate
> this comparison process on a table by table basis.
> Each file is its own table. Ideally, I should
> simply be able to input the location of each file
> and my utility would compare the two tables, looking
> for differences in the values stored in the tables.
> I think I know how to write most of this code, but
> there is one problem I dont know how to solve. I
> would rather not have to hard code each tables
> column names. Is there a way to extract at run-time
> the names of every column in a table?
> I would prefer to write this program using ADO, but
> DAO is fine.
> Im using VB6SP3 and ADO 2.1. The database engine
> is Jet.
> Jim
> -----------------** -- Posted from CodeGuru -- **-----------------
> http://www.codeguru.com/vb The website for VB programmers.
