Problems Updating Backend Tables RRS feed

  • Question

  • I have a split DB where users enter information through a form in the Front End DB. In the BE db, there is a query that creates a new table that is used as a link for the FE. Is there a way this BE table can be created while users are in the form in the FE DB. I keep getting an error stating that it is locked.

    Friday, January 17, 2020 5:24 PM

All replies

  • Hi. How exactly are you creating the table? Are you doing it manually or by code? If by code, where are you executing it? In the FE? What is the purpose of this new table?
    Friday, January 17, 2020 6:27 PM
  • You can create linked tables from the FE file with the following VBA code:

    Dim Db As DAO.Database
    Set Db = CurrentDb
    Dim myTable As DAO.TableDef
    Dim myField As DAO.Field
    Dim myProp As DAO.Property
    Dim MyTableName As String
    Dim MyConnection As String
    MyConnection = DLookup("[Database]", "MSysObjects","[Database] Is Not Null") 'BE file location
        MyTableName = "Table Name Here"
        Set myTable = Db.CreateTableDef(MyTableName)
        With myTable
            'Field Names and Data Types
            .Fields.Append .CreateField("Field Name Here", dbDataTypeHere)
        End With
        Db.TableDefs.Append myTable
        DoCmd.TransferDatabase acExport, "Microsoft Access", MyConnection, acTable, myTable.Name, myTable.Name
        DoCmd.DeleteObject acTable, myTable.Name
        DoCmd.TransferDatabase acLink, "Microsoft Access", MyConnection, acTable, myTable.Name, myTable.Name
        Set myField = Nothing
        Set myTable = Nothing

    Friday, January 17, 2020 8:06 PM
  • I'm creating the table using MS Access Query Builder. 

    So from start to finish. We have an oracle table that constantly gets updated. I made a make table query that pulls certain information from this table based on certain parameters and does a few calculations. We will call this table A. Table  A. is created in the BE and is linked on the FE. Individuals in the FE use a form to enter information based on Table A. (Which is linked to the form).  Table A throughout the process need to be updated frequently since new information gets pushed to the oracle table on the regular. My issue is that while users are using the form in the FE, I am not able to make the new table on the BE. It basically needs to refresh so individuals can see the new data on the FE

    Tuesday, January 21, 2020 4:38 PM
  • I don't believe that any database product would allow users to do what you want to do and for very good reason. If you allow users (or anyone)  to create new tables and then edit data in those tables "on-the-fly" at any time, you will have a gigantic mess on your hands. I have never tried to link ORACLE data myself, but you should not be constantly creating new tables on-the-fly. If you can, I would link to the ORACLE data directly using queries within ACCESS. As the new data updates in ORACLE then the queries could update also.
    Tuesday, January 21, 2020 7:22 PM
  • If you are creating the same table over and over I would just truncate the table instead of re-creating it. That will remove all the data in the table. If you wish to remove only some of the rows you can run a DELETE using a WHERE clause to limit what is removed.

    Bill Mosca
    https://wrmosca.wordpress.com https://groups.io/g/MSAccessProfessionals

    Tuesday, January 21, 2020 10:23 PM
  • If I understand you correctly you are periodically completely recreating table A (an Access table in your back end database) based on a table in an Oracle database.  Firstly you won't be able to recreate table A as long as someone has it open, whether it is by a bound form, report, or query.  Ideally you would create a view in the Oracle database which presents the same information that you are used to seeing in table A, recreating all of the criteria and calculations of the make-table query that creates table A.  Then bind your form to this view instead of to table A.  If you are unable to create such a view in Oracle the next best solution would be to create a pass-through SELECT query in Access using your Oracle table (including the same criteria and calculations that you used to create table A) and bind your form to that.  Finally if you cannot create such a pass-through query you will need to link your Oracle table into your front end and create a straight Access SELECT query based on this table (again, using the same criteria and calculations that you used to create table A) and bind your form to this query instead of to table A.


    Wednesday, January 22, 2020 8:13 PM