Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33

    Unanswered: editing linked tables

    I have an access database that has been split and I'm trying to add a field to a table in the backend file. Access however, will let me view the table but not save my changes. Anyone know how to get around this?

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you cannot edit linked BE tables in FE design view!
    open the BE, edit the tables there.

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2006
    Location
    Floating around NW;UK
    Posts
    11
    Werener,

    Here is some code I used to add some fields and change a field of BE database it worked fine.

    Code:
    Option Compare Database
    Option Explicit
    Dim TermDate As Date
    Dim AgeOver As Long
    Dim strDocName As String
    Dim strLinkCriteria As String
    Dim strSQL As String
    Dim strDBPath
    Dim strDBFile
    Dim strBckUpDrv
    Dim rstMarina As DAO.Recordset
    Dim dbs As DAO.Database
    Dim arrTables, eleTable
    Dim tdf As TableDef
    Private Sub Form_Open(Cancel As Integer)
    Dim fld
    Me.lblFrmHdr1.Caption = "Marina Details"
    Me.lblFrmHdr2.Caption = "Version 1.3c"
    arrTables = Array("Boat", "InvDtl", "InvHdr", "Marina", "Owner")
    strDBPath = CurrentDb.Name
    strDBFile = Dir(strDBPath)
    strDBPath = Left$(strDBPath, Len(strDBPath) - Len(strDBFile))
    Set dbs = CurrentDb
    ' Drop all the tables(links)
    For Each eleTable In arrTables
        If fExist(eleTable) Then
            dbs.TableDefs.Refresh
            Set tdf = dbs.TableDefs(eleTable)
            Debug.Print "Deleting table: " & tdf.Name
            dbs.TableDefs.Delete tdf.Name
        End If
    Next eleTable
    'Link all the tables
    For Each eleTable In arrTables
        dbs.TableDefs.Refresh
        If Not fExist(eleTable) Then
            Set tdf = dbs.CreateTableDef(eleTable)
            tdf.Connect = _
                ";DATABASE=" & strDBPath & "Data\LiveData.mdb"
            tdf.SourceTableName = eleTable
            dbs.TableDefs.Append tdf
        End If
    Next eleTable
    dbs.Close
    Set dbs = Nothing
    'Check if LiveDatabase needs upgrading to Version 1.3a
    Set dbs = OpenDatabase(strDBPath & "Data\LiveData.mdb")
    dbs.TableDefs.Refresh
    Set tdf = dbs.TableDefs!Owner
    Set fld = tdf!Car_Reg
    If fld.Size <> 64 Then
    ' Data database needs updating, let operator know
    
    ' THIS IS THE STUFF VERNER!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
    
    ' Change Car_Reg from 12 to 64 charaters (cater for more than one vehicle)
        strSQL = MsgBox("New version detected." & vbCrLf & "Starting update.", , "System Update")
        ' Create new field of correct size
        tdf.Fields.Append tdf.CreateField("TempFld", dbText, 64)
        ' Copy old field to new
        strSQL = "UPDATE DISTINCTROW [Owner] SET [TempFld]=[Car_Reg];"
        dbs.Execute strSQL
        ' Remove old field
        tdf.Fields.Delete "Car_Reg"
        ' Rename new field to old name
        dbs.TableDefs("Owner").Fields("TempFld").Name = "Car_Reg"
    ' Change Marina record to store "backup device drive" and "Vat rate"
        Set tdf = dbs.TableDefs!Marina
        'add the fields in the table
        tdf.Fields.Append tdf.CreateField("BckUpDrv", dbText, 2)
        tdf.Fields.Append tdf.CreateField("VATRate", dbSingle)
    ' Set Marina record to default "backup device drive" and "Vat rate"
        strSQL = "SELECT * " _
                & "FROM Marina " _
                & "WHERE Marina.ID = 1;"
        Set rstMarina = dbs.OpenRecordset(strSQL, dbOpenDynaset)
        With rstMarina
            If .EOF Then
            Else
                .Edit
                !BckUpDrv = "A"
                !VATRate = 17.5
                .Update
            End If
        End With
        rstMarina.Close
        Set rstMarina = Nothing
    ' Job done , let operator know
        MsgBox "Updated!"
    End If
    dbs.Close
    Set dbs = Nothing
    Hope this makes sense. This is my first excursion from lurking in the bushes.

  4. #4
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33

    thanks

    Thanks, let me give this a try.

  5. #5
    Join Date
    May 2005
    Posts
    119
    Make sure you close the FE database. If any of the tables are in use, it won't let you save your changes...

  6. #6
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33

    unfortunately still stuck

    I tried opening the backend file and nothing happens. Access seems to think a little and asks me for a login but afterwards nothing appears. I tried to modify the table using vba like irgo suggested and its telling me the table is locked for editing. (probably because the code is in the front end) I'm running out of ideas here..

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Did you try Izy's suggestion?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Feb 2006
    Location
    Floating around NW;UK
    Posts
    11
    If the table is open because of a form it will be locked for editing.

  9. #9
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33
    I know, I tried to open just the BE file and nothing happens. Access starts but doesn't show me anything.. Is there something i'm missing?

  10. #10
    Join Date
    May 2005
    Posts
    119
    Sounds like your database window might be hidden! Click on the Windows Menu, then Unhide. You will see the open database listed if it is hidden. Select your database and click OK. You should then see your tables.

    You can change this option by clicking on Tools, Startup, then check the box called "Display database window".

    Hope this helps!
    Krista

  11. #11
    Join Date
    Dec 2005
    Location
    Lancaster PA, USA
    Posts
    33
    Actually I figured it out, ..whoever designed this db enabled some goofy security on the BE file, you have to hold the shift key when logging in to even be able to see the tables

  12. #12
    Join Date
    May 2005
    Posts
    119
    FYI - the shift key disables (temporarily) the autoexec macro from running on startup. The macro, most likely, hides the database window.

  13. #13
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    Holding Shift also ignores any options you have set in Tools/Startup.

Posting Permissions

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