Results 1 to 7 of 7
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How To Loop Through BE? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have FE and BE database

    Looking for VBA code within the FE database to loop thruogh BE tables tbl_DLicense and tbl_MLicense and change dtmCreate and dtmUpdate Date fields Input Mask from 99/99/9999;0;_ to blank (in other words no Format)

    Thanks, John

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Loop Through BE? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The following code uses DAO, so you need a reference to the Microsoft DAO 3.6 Object Library.

    ' General procedure

    Public Sub RemoveInputMask(strBE As String, strTbl As String, strFld As String)
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    On Error GoTo ErrHandler

    Set dbs = DBEngine.OpenDatabase(strBE)
    Set tdf = dbs.TableDefs(strTbl)
    Set fld = tdf.Fields(strFld)
    fld.Properties.Delete "InputMask"

    ExitHandler:
    On Error Resume Next
    Set fld = Nothing
    Set tdf = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err & ": " & Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    ' Process specific tables/fields

    Public Sub RemoveSeveral()
    RemoveInputMask "serversharefolderbackend.mdb", "tbl_DLicense", "dtmCreate"
    RemoveInputMask "serversharefolderbackend.mdb", "tbl_DLicense", "dtmUpdate"
    RemoveInputMask "serversharefolderbackend.mdb", "tbl_MLicense", "dtmCreate"
    RemoveInputMask "serversharefolderbackend.mdb", "tbl_MLicense", "dtmUpdate"
    End Sub

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Loop Through BE? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Perfect!

    How would I change the code to change all the Date fields prefixed with "dtm" in just tbl_MLicense

    Thanks, John

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Loop Through BE? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Try this:

    Public Sub RemoveInputMasks()
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field

    On Error GoTo ErrHandler

    Set dbs = DBEngine.OpenDatabase("serversharefolderbackend.md b")
    Set tdf = dbs.TableDefs("tbl_MLicense")
    For Each fld In tdf.Fields
    If Left(fld.Name, 3) = "dtm" Then
    fld.Properties.Delete "InputMask"
    End If
    Next fld

    ExitHandler:
    On Error Resume Next
    Set fld = Nothing
    Set tdf = Nothing
    dbs.Close
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    MsgBox Err & ": " & Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Loop Through BE? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The above code works for pure date fields with an Input Mask property, however after further review the following two dtm fields are time field defined as follows:

    dtmSolemBeginTime Data Type=Date/Time, Format=Medium Time, Input Mask=blank
    dtmSolemOccuredTime Data Type=Date/Time, Format=Medium Time, Input Mask=blank

    and is generating RTE 3265 - Item not found in this collection when the code is run

    So I change code as follows and it is not skipping over my time fields and giving same RTE 3265

    If Left(fld.Name, 3) = "dtm" And Right(fld.Name, 4) <> "Time" Then
    fld.Properties.Delete "InputMask"
    End If

    What is wrong with the above If statement?

    Thanks, John

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How To Loop Through BE? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I don't know, but I'd do it like this:

    If Left(fld.Name, 3) = "dtm" Then
    On Error Resume Next
    fld.Properties.Delete "InputMask"
    On Error GoTo ErrHandler
    End If

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How To Loop Through BE? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •