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

 

[Micron]

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 important that the field names are correct.

What do you mean by "I import a table from another db"? Are you using the Transferspreadsheet function or running an append query to get the data into the target table? If you could use an append query to append records to the target each time, the fields would always be correct.

Otherwise, I assume you're transferring from Excel to an Access table and sometimes the spreadsheet headers are incorrect? Then transfer to the target table and set the parameter in that function to NOT bring back the spreadsheet column names and your fields should always be correct.

 

[jrheath007]

Thanks for the response Micron but the situation is not as you described.

I import a table from another database (it has to be a DB not excel as there are too many rows for excel) but sometimes the person who creates the original database that I get the table from incorrectly names the columns. So I would like to determine which columns are incorrectly labelled so this can be corrected and the queries will work.

I feel like I am working in the incorrect manor as I would of though this is a common problem for people i.e. getting data from a source file and using it in a database to process the information, be great to get some feedback on this.

 

[Micron]

I would say it is a bit unusual. One would think that controls should be enforced at the beginning, not down stream. The root cause seems to be that the source table fields are not created in a consistent manner and I don't understand how this is allowed to happen, nor do I get why a db is continually being created anew instead of working with the old. If accuracy is not probable at the beginning, code at your end could rename the table fields. However, given that someone cannot create the proper names at their end, what guarantee is there that they would even create them in the correct order? This would be crucial.

What I'd need to know is, are you going to get the correct field names from a table in your db or should this be hard coded? If a table, you could have a row of proper table names in order from left to right for as many tables as you like (one row per table) BUT you'd need a form with a combo box to make the table selection. A table would also allow you to alter the names without having to edit the code, although hard coding would be easier to set up. I'd have to know the table name and proper field names in order. I assume the table to be altered is in the db you're working in. I'd also have to know the means by which this event is to run (I suggest a form button, so the name of the button as well).

 

[jrheath007]

Thanks for the response Micron, and I totally agree. I have tried to correct the field names at source but this does not seem possible and the operator has to update the field names each time to make them usable.

I wanted to upload an example file but I do not seem to be able to do this on here and I cannot get to file sharing sites while at work.

What I would like to achieve is this: have a master DB that is used each month that has a "Reference table" in this DB that has all the field names as they should be. In this DB each month we will import the new data and call this table "Current", I would like the VBA to use the "Reference Table" to confirm all the field names are correct in the "Current" table. The ideal would be to have a message pop out at the time that it finds an error and say the name of the field it has not match.

I found this video today and I think this maybe a starting point just need an if statement adding to it I think to stop of errors are found:

https://www.youtube.com/watch?v=KNYcxvWOgdo

Thanks again Justin

 

[Micron]

So if I understand, you will deal with replacing tblCurrent with a new version each cycle (you might get a prompt regarding the over-write), have a form with a button to run code to get the proper field names from tblFieldNames.FldNames and compare them to tblCurrent. So why not rename the fields to what you want rather than report the ones that don't match? I've never done that on a table before, but am pretty sure it can be done and should be able to figure it out. However, if you think there's a chance that the left to right order of tblCurrent doesn't match tblFieldNames, what do you want, a message box with a list of incorrect names or something else?

I didn't watch the whole video - looping through a recordset is not new to me. In your case, it will be necessary to loop through tblCurrent field names as well.

 

[jrheath007]

Yeah you have the idea that I am thinking of, and yes a list of incorrect fields would be good.

I was thinking that the script would work through each field name in the "Current" table and do some sort of search in the "Reference Table" to make sure there is a match, if it does not match it gets added to the list that is shown in the message box.

What you think?

Thanks

 

[Micron]

Just got email about your follow up post as I was reviewing this. If you need help to do the message box version, let me know. For now, try to get this working and we'll take it from there:

Create test tables tblCurrent (fields fldA, fldB fldC) and tblFieldNames (Name1, Name2, Name3 - values apple, orange, lemon). Don'd add a PK in either. You will need to set a reference to DAO if not already done. Put this code between the sub/end sub part of a form button click event:

Dim db As Dao.Database
Dim tdf As Dao.TableDef
Dim rs As Dao.Recordset
Dim strCurName As String, strPropName As String
Dim cntr As Integer

On Error GoTo errHandler
Set db = CurrentDb
Set rs = db.OpenRecordset("tblFieldNames")
If rs.RecordCount = 0 Then
  MsgBox "No proper field names were found."
  Exit Sub
End If

rs.MoveFirst 'not really req'd if tbl will only ever have 1 record
Set tdf = db.TableDefs("tblCurrent")
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 'field count is zero based
   strCurName = tdf.Fields(cntr).Name
   strPropName = rs.Fields(cntr)
   If strCurName <> strPropName Then
     tdf.Fields(cntr).Name = strPropName
   End If
Next

exitHere:
On Error Resume Next 'if the rs did not open, add'l error created
Set db = Nothing
rs.Close
Set rs = Nothing
Set tdf = Nothing
Exit Sub

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

It should rename fldA to apple and so on. If tblCurrent will always have 1 constant field such as an autonumber ID, change the counter from 0 to 1 or whatever the number of constant fields would be.

 

[jrheath007]

Morning Micron

I have implemented this code and if I am right this is renaming fields rather than check to see if there are errors?

This is really useful as I can use this on the sauce files to rename the fields that come out of our system. But it would be good to still achieve the original goal of error checking, because I never trust users and this script needs the table to be in the correct order, which it should always be (I can guarantee this from the system so will be great for that) but not once a user is needed.

Thanks Micron this is proving to be more useful than I original expected.

 

[Micron]

Morning Micron

I have implemented this code and if I am right this is renaming fields rather than check to see if there are errors?

Correct.

But it would be good to still achieve the original goal of error checking,

Add this to the declarations section

Dim strMsg as String

replace this similar section with this, or simply transfer the changes if you can pick out all of them

strMsg = "Incorrect Field List:" & vbcrlf & vbcrlf
rs.MoveFirst 'not really req'd if tbl will only ever have 1 record
Set tdf = db.TableDefs("tblCurrent")
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 'field count is zero based
   strCurName = tdf.Fields(cntr).Name
   strPropName = rs.Fields(cntr)
   If strCurName <> strPropName Then
     'tdf.Fields(cntr).Name = strPropName
     strMsg = strMsg & "- " & strCurName & vbcrlf
   End If
Next
msgbox strMsg

Glad to have helped, but if you can't be sure of even getting the correct order even if you have all the right names, I'm not sure how helpful I've been.