error check table field names

[jrheath007]

Hi all

I have a DB where I import a table from another DB. The DB that I import this table into already has querys set up so it is import that the field names are correct.

so I would like some VBA script to work its way through each of the field names in the imported table and create an error message if the field name does not match. I would also like vba script to stop at the point that if finds the error so that it does not run the queries.

I hope this makes sense and would truly appreciate help with this as been looking all day.

Thanks Justin

 

[jrheath007]

Morning Micron

The code works a treat, thank you very much. These are the VBA's I ended up with:

Code to provide list of incorrect fields:

Private Sub Commandtest_Click()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim strCurName, strPropName, DLTable, REFTable, strMsg As String
Dim cntr As Integer


DLTable = Forms![Field Labelling]![Text38]
REFTable = "1 - Ref Sheet"
Set db = CurrentDb
Set rs = db.OpenRecordset(REFTable)
'error checking
On Error Resume Next
Set tdf = db.TableDefs(DLTable)
If Err.Number = 0 Then
    Err.Clear
Else
    MsgBox "Table not found."
    Err.Clear
    Exit Sub
End If
'****Field Check****
On Error GoTo errHandler
If rs.RecordCount = 0 Then
  MsgBox "No proper field names were found."
  Exit Sub
End If


rs.MoveFirst
Set tdf = db.TableDefs(DLTable)
If tdf.Fields.Count <> rs.Fields.Count Then
  MsgBox "The field count is different between the tables."
  Exit Sub
End If


For cntr = 0 To tdf.Fields.Count - 1
   strCurName = tdf.Fields(cntr).Name
   strPropName = rs.Fields(cntr)
   If strCurName <> strPropName Then
     strMsg = strMsg & "Incorrect Field Name - " & strCurName & vbCrLf
   End If
Next
MsgBox strMsg


exitHere:
On Error Resume Next
Set db = Nothing
rs.Close
Set rs = Nothing
Set tdf = Nothing
Exit Sub


errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub[/CODE

[U][B]Code to update field names in an external DB:[/B][/U]
[CODE]Private Sub Commandtest_Click()


Dim db As DAO.Database
Dim db1 As DAO.Database
Dim tdf As DAO.TableDef
Dim rs As DAO.Recordset
Dim strCurName, strPropName, DLDoc, DLTable, REFTable As String
Dim cntr As Integer


DLDoc = Forms![Field Labelling]![Text35]
DLTable = Forms![Field Labelling]![Text38]
REFTable = Me.Combo44.Value
'error checking
On Error Resume Next
Set db1 = OpenDatabase(DLDoc)
If Err.Number = 0 Then
    Err.Clear
Else
    MsgBox "File not found."
    Err.Clear
    Exit Sub
End If
On Error Resume Next
Set tdf = db1.TableDefs(DLTable)
If Err.Number = 0 Then
    Err.Clear
Else
    MsgBox "Table not found."
    Err.Clear
    Exit Sub
End If
'****Field Update****
On Error GoTo errHandler
Set db = CurrentDb
Set rs = db.OpenRecordset(REFTable)
If rs.RecordCount = 0 Then
  MsgBox "No proper field names were found."
  Exit Sub
End If


rs.MoveFirst
Set tdf = db1.TableDefs(DLTable)
If tdf.Fields.Count <> rs.Fields.Count Then
  MsgBox "The field count is different between the tables."
  Exit Sub
End If


For cntr = 0 To tdf.Fields.Count - 1
   strCurName = tdf.Fields(cntr).Name
   strPropName = rs.Fields(cntr)
   If strCurName <> strPropName Then
     tdf.Fields(cntr).Name = strPropName
   End If
Next
MsgBox "Field Names Have Been Updated"


exitHere:
On Error Resume Next
Set db = Nothing
Set db1 = Nothing
rs.Close
Set rs = Nothing
Set tdf = Nothing
Exit Sub


errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere
End Sub

Both of these code have variables now that are set from input boxes on a form.

Thanks again Micron
Justin

 

[Micron]

a point or two I would change:

Private Sub Commandtest_Click()

Dim strCurName, strPropName, DLTable, REFTable, strMsg As String 
[I]not how you declare multiple variables on one line. The first 4 are variants because you have not declared them as anything else[/I][I]
Dim strCurName as String, strPropName as String...[/I]

REFTable = "1 - Ref Sheet" 
[I]bad idea to use special characters in an object name, save for underscore
[/I]
[I]same Dim problem here[/I]
Dim strCurName, strPropName, DLDoc, DLTable, REFTable As String