By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,503 Members | 2,730 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,503 IT Pros & Developers. It's quick & easy.

Passing a field to be deleted - contains Special Chars - function doesn't acknowledge

100+
P: 133
There is a field that contains special character when it is passed to a function, it doesn't acknowledge that the field exist in the table. When I remove the special chars, it passes the field and performs a delete on the field.

The field name is [Sales Order Date/Time Created] from table SO_Data.

Thank you in advance for your assistance.

The sub that calls the functions:
Expand|Select|Wrap|Line Numbers
  1. If ifFieldExists("[Sales Order Date/Time Created]", "SO_Data") Then
  2.         'Call the Function
  3.             DeleteField "SO_Data", "[Sales Order Date/Time Created]"
  4.     Else
  5.         MsgBox "Field Does not exist", vbInformation, "Field - [Sales Order Date/Time Created], Table: SO_Data"
  6.     End If
  7.  
the functions:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Function ifFieldExists(FieldName As String, TableName As String) As Boolean
  5.    Dim rs As Recordset, Db As Database ' DAO Vars
  6.  
  7.    'References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
  8.    'Set references by Clicking Tools and Then References in the Code View window
  9.    'Checks if Field exists.
  10.    'USAGE: ifFieldExists "FIELDNAME", "TABLENAME"
  11.  
  12.    On Error GoTo NoField 'If there is no Field capture the error.
  13.  
  14.    Set Db = CurrentDb()
  15.  
  16.    'If Field is there open it
  17.    Set rs = Db.OpenRecordset("Select " & FieldName & " from " & TableName & ";")
  18.    ifFieldExists = True
  19.    rs.Close
  20.  
  21. ExitHere:
  22.  
  23.    Set rs = Nothing
  24.    Db.Close
  25.    Set Db = Nothing
  26.    Exit Function
  27.  
  28. NoField:
  29.  
  30.    'If Field is not present set function to false
  31.  
  32.    ifFieldExists = False
  33.  
  34.    With Err
  35.       MsgBox "Error " & .Number & vbCrLf & .Description, _
  36.             vbOKOnly Or vbCritical, "ifFieldExists"
  37.    End With
  38.    Resume ExitHere
  39.    Exit Function
  40. End Function
  41.  
  42.  
  43. Function DeleteField(tblName As String, strField As String) As Boolean
  44.  
  45.    Dim Db As DAO.Database
  46.    Dim tdf As DAO.TableDef
  47.  
  48.    On Error GoTo errhandler
  49.    ' References: Microsoft Access 11.0 Object Library, Microsoft DAO 3.6 Object Library
  50.    ' Set references by Clicking Tools and Then References in the Code View window
  51.    ' Returns True on Success, False otherwise.
  52.    ' Accepts
  53.    ' tblName: Name of Table that the Field is located
  54.    ' strField: Name of  Field to delete
  55.    ' Deletes Field strField in Table tblName.
  56.    ' USAGE: DeleteField "tblName", "strField"
  57.  
  58.    Set Db = CurrentDb()
  59.  
  60.    'Set tblName to your table mine is 1A
  61.    Set tdf = Db.TableDefs(tblName)
  62.  
  63.    'Execute the delete
  64.    tdf.Fields.Delete (strField)
  65.    DeleteField = True
  66.    MsgBox "Delete Field " & strField & " Complete."
  67.  
  68.    Db.Close
  69.  
  70. ExitHere:
  71.  
  72.    Set Db = Nothing
  73.    Set tdf = Nothing
  74.  
  75.    Exit Function
  76.  
  77. errhandler: 'There is an error return as False
  78.  
  79.    DeleteField = False
  80.    With Err
  81.  
  82.       MsgBox "Error " & .Number & vbCrLf & .Description, _
  83.             vbOKOnly Or vbCritical, "deletefield"
  84.    End With
  85.  
  86.    Resume ExitHere
  87. End Function
  88.  
  89.  
  90.  
Oct 22 '12 #1

✓ answered by zmbd

try this...

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function poc20121022(ztablein As String, zinfield As String) As Boolean
  5. '
  6. 'Based on code located at
  7. 'http://bytes.com/topic/access/answers/943863-passing-field-deleted-contains-special-chars-function-doesnt-acknowledge#post3731091
  8. 'by z
  9. '
  10. 'Usage: if you were looking for field [Table2].[Jump/Here]
  11. 'then you would enter: POC20121022("Table2", "jump/here")
  12. '
  13. Dim zdb As DAO.Database
  14. Dim ztdefs As DAO.TableDefs
  15. Dim ztable As DAO.TableDef
  16. Dim zfield As DAO.Field
  17. '
  18. On Error GoTo z_error_trap
  19. '
  20. Set zdb = CurrentDb
  21. Set ztdefs = zdb.TableDefs
  22. Set ztable = ztdefs(ztablein)
  23. For Each zfield In ztable.Fields
  24. Debug.Print "Compairing: " & UCase(zfield.Name) & " to: " & UCase(zinfield)
  25.     If UCase(zfield.Name) = UCase(zinfield) Then
  26.         poc20121022 = True
  27.     End If
  28. Next zfield
  29. '
  30. z_return_from_error:
  31. '
  32. Set zdb = Nothing
  33. Set ztdefs = Nothing
  34. Set ztable = Nothing
  35. Exit Function
  36. z_error_trap:
  37. MsgBox Err.Number & vbCrLf & Err.Description & _
  38.     vbCrLf & ztablein & "." & zinfield & _
  39.     vbCrLf & "search resulted in an error." & _
  40.     vbCrLf & "function will return a value of FALSE", vbCritical, "An Error Occured"
  41. '
  42. poc20121022 = False
  43. Resume z_return_from_error
  44. End Function
  45.  
This looks at the current database table collection.
If the table is found, then it starts looking at the fields within the table.
If the table is not found it will error and tell you so then return false.
If the table is found and the field is found then it returns true
etc...
You can rename as needed
You can remove the debug print as needed.

Share this Question
Share on Google+
6 Replies


zmbd
Expert Mod 5K+
P: 5,287
OK,
I haven't read thru your code yet; however, here is the number one advise I can give you:
When naming fields, files, folders, or what have you
use only alphanumeric and the underscore do NOT use spaces, do NOT use any special charactors.
Find a list of reserved word for SQL and VBA - Do NOT use these words when naming fields, files, folders, or what have you

You have already ran into why this is the strongest advise I can give you based on almost three decades of programing (if you'll allow me to back to my earliest CBM-Pet it is certainly 30 years)

Please save yourself and others that follow alot of heartburn and change those field names.


OK... now I'll take a closer look at your code.
Oct 22 '12 #2

100+
P: 133
I totally agree; however, this is data that is extracted from an external system. I have no control over the import field.
Oct 22 '12 #3

Rabbit
Expert Mod 10K+
P: 12,315
You may not have control over the fields being exported, but you have control over the field it gets imported to.
Oct 22 '12 #4

100+
P: 133
When altering, the parameter without the brackets, the function acknowledges the field.

The tables in the db, get uploaded to a SQL db. Applications reference the SQL table that use the same field names from the imported file.
Oct 22 '12 #5

zmbd
Expert Mod 5K+
P: 5,287
try this...

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function poc20121022(ztablein As String, zinfield As String) As Boolean
  5. '
  6. 'Based on code located at
  7. 'http://bytes.com/topic/access/answers/943863-passing-field-deleted-contains-special-chars-function-doesnt-acknowledge#post3731091
  8. 'by z
  9. '
  10. 'Usage: if you were looking for field [Table2].[Jump/Here]
  11. 'then you would enter: POC20121022("Table2", "jump/here")
  12. '
  13. Dim zdb As DAO.Database
  14. Dim ztdefs As DAO.TableDefs
  15. Dim ztable As DAO.TableDef
  16. Dim zfield As DAO.Field
  17. '
  18. On Error GoTo z_error_trap
  19. '
  20. Set zdb = CurrentDb
  21. Set ztdefs = zdb.TableDefs
  22. Set ztable = ztdefs(ztablein)
  23. For Each zfield In ztable.Fields
  24. Debug.Print "Compairing: " & UCase(zfield.Name) & " to: " & UCase(zinfield)
  25.     If UCase(zfield.Name) = UCase(zinfield) Then
  26.         poc20121022 = True
  27.     End If
  28. Next zfield
  29. '
  30. z_return_from_error:
  31. '
  32. Set zdb = Nothing
  33. Set ztdefs = Nothing
  34. Set ztable = Nothing
  35. Exit Function
  36. z_error_trap:
  37. MsgBox Err.Number & vbCrLf & Err.Description & _
  38.     vbCrLf & ztablein & "." & zinfield & _
  39.     vbCrLf & "search resulted in an error." & _
  40.     vbCrLf & "function will return a value of FALSE", vbCritical, "An Error Occured"
  41. '
  42. poc20121022 = False
  43. Resume z_return_from_error
  44. End Function
  45.  
This looks at the current database table collection.
If the table is found, then it starts looking at the fields within the table.
If the table is not found it will error and tell you so then return false.
If the table is found and the field is found then it returns true
etc...
You can rename as needed
You can remove the debug print as needed.
Oct 22 '12 #6

zmbd
Expert Mod 5K+
P: 5,287
actually... we could remove the field in the same poc20121022 function by using the delete field method with the for loop.
I'll leave that as an exercise.
Oct 22 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.