Access referencing columns in tables

[gregsaho]

I am having trouble referencing specific columns or fields in tables. I have two tables and i am wanting to make a loop that cycles through each field in the two tables(they are the same column names) and produces any records that don't match. An example of a loop would be most helpfull but i'll definately settle for someone telling me how to reference specific columns other than by names. by an index number or something would be great.

 

[TXWizard]

To reference fields in two tables, you must use unambiguous names, fully qualifed by the table name; e. g. CUSTOMER.LastName. However, if it's the field NMAES that you want to compare, you can use the Fields collection of the Tabledef or Recordset object.

 

[MichaelRed]

In the general sense, yoou cannot compare fields as efficiently via 'code' as you can in a query. Ms. A. provides a few wizzards for the novice programmer to help with a few of the more common tasks -such as finding where recordsets have un-matched values. While the 'wizzard' limits the number of fields which it will check, it is not (usually) all that difficult to 'review' the constrct and find any number of ways to expand the number of fields - or just construct some queries with 'calculated' fields which include the concatenation of all that is relevant which may easily be included in the unmatched group.


In a somewhat different response, finding ALL of the individual fields which do not match between two recordsets is often tried (done) as one step in reconciling two recordsets and bringing them into synchronization. This is generally acconplished much more efficiently with a simple update query. Properly constructed, it will change any field in the 'destination' which is different than the corresponding field in the source, as well as adding entire records to the destination which are missing from the source. Of course, you MAY need to be quite careful in setting upi the join conditions between the two recordsets.





MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[TXWizard]

You're preaching to the choir. <G>

My point was that the original poster seemed unaware of the need to declare column names unambiguously when you are working with two or more tables that contain like named fields.

 

[gregsaho]

I don't think any of these suggestions would work because i have 2500 fields to comapare. I have a master record that has to be compared to about 10 other records to see each other record is the same as the master record. The fields can't be updated automatically cause they must be reviewed before they are updated. I started off trying to just tediously use a ton of OR criteria statments in the query design but that didn't work so i was hoping there was a way through code that i could just reference a field index and then use a loop to cycle through all the fields comparing them. There are way to many fields to be compared using the normal query design screen as far as i can tell so maybe this type of query is not even possible with Access?? Any example code would be helpfull as well. Thanks

 

[TXWizard]

I agree, and that's one of many reasons that there is a way to do it in code. Here is an example from production code that I just finished last week. The task at hand is different, but it will give you the idea.

    Dim adoConn As Connection
    Dim fldCust As Field
    Dim rstCust As ADODB.Recordset
    Set adoConn = CurrentProject.Connection
    Set rstCust = New ADODB.Recordset
    rstCust.CursorType = adOpenDynamic
    rstCust.CursorLocation = adUseServer
    rstCust.CacheSize = 1000
    rstCust.LockType = adLockOptimistic
    Debug.Print "Finished properties of Recordset."
    rstCust.Open "CUSTOMER", adoConn
    For Each fldCust In rstCust.Fields
        If varImportFieldNames(lngJ) = fldCust.Name Then
            blnValidField = True
            Exit For
        End If
    Next fldCust

HTH.