none
Create linked table from VBA RRS feed

  • Question

  • I have a bunch of tables that are created using INSERT INTO , SELECT INTO, and CREATE in a vba module. We are looking at splitting the database into front and back end, so we would create the tables in the back end and link to the front end.

    Can this be done in VBA from module in front end?

    This is for Access 2.0

    Thursday, June 7, 2018 7:35 PM

Answers

  • Why do you need to create a temporary table?  A report would normally be based on a query which restricts the rows returned on the basis of parameters in the query, or by filtering the report by means of the WhereCondition argument of the OpenReport method of the DoCmd object.

    Ken Sheridan, Stafford, England

    • Marked as answer by JHarding08 Tuesday, June 26, 2018 6:33 PM
    Friday, June 8, 2018 10:33 PM

All replies

  • Hi,

    Not sure about the Access 2.0 part but if it has the TransferDatabase command available, you could try using it.

    However, if you have a front end and execute a Make-Table query from it, I thought the table would be created locally, so there's no need to link to it, really.

    Just my 2 cents...

    Thursday, June 7, 2018 7:38 PM
  • I read about the TransferDatabase and will try that in 2.0.  The make table queries will be created locally and we need all tables shared for all users
    Thursday, June 7, 2018 8:53 PM
  • I read about the TransferDatabase and will try that in 2.0.  The make table queries will be created locally and we need all tables shared for all users

    If TransferDatabase is available, it should be able to the following:

    1. Move the local table to the back end, and then

    2. Create a linked table to it

    Which means, you'll have to execute the command twice with a delete local table command in between.

    Good luck!

    Thursday, June 7, 2018 8:56 PM
  • Can this be done in VBA from module in front end?

    This is for Access 2.0

    Hi JHarding08,

    You can create an Object referring to the data-database (BE), and create there your table.

    An example could be:

      Set tmp_db = Data_db
      
      If (Not Tabel_exists_db(cur_item & "_tbl", tmp_db)) Then
        Set cur_tabel = tmp_db.CreateTableDef(cur_item & "_tbl")
        Set cur_field = cur_tabel.CreateField(cur_item & "_id", dbLong)
        cur_field.Attributes = cur_field.Attributes + dbAutoIncrField
        cur_tabel.Fields.Append cur_field
        tmp_db.TableDefs.Append cur_tabel
      
        tmp_db.Execute "CREATE INDEX Key0 ON " & cur_item & "_tbl (" & cur_item & "_id)" & "WITH PRIMARY"
      End If
    
      tmp_db.Close
    

    This routine in FE creates in the BE (Data_db returns the object to the BE) the table (e.g. Person_tbl) with an autonumber Person_id as primary key.

    In FE you also need to create a table, using:

      Set cur_tabel = CurrentDb.CreateTableDef(cur_item & "_tbl")
      cur_tabel.SourceTableName = cur_item & "_tbl"
      cur_tabel.Connect = ";DATABASE=" & DataDB_naam
      CurrentDb.TableDefs.Append cur_tabel
    

    Study these examples, and tune for your needs.

    I use A2003 on this moment, but if I remember right, I also used these in A2.0.

    Imb.

    Thursday, June 7, 2018 9:11 PM
  • The following code is an example, albeit in a rather different context.  The methodology would equally apply in your case, however:

        ' create BoM_Temp table in temporary database.
        ' note that this one has no constraint as it will be
        ' necessary to add duplicates of major/minor part number
        ' values in separate rows
        strSQL = "CREATE TABLE BoM_Temp (MajorPartNum LONG NOT NULL, " & _
                "MinorPartNum LONG NOT NULL, Quantity LONG NOT NULL)"
        dbsTemp.Execute (strSQL)
        
        ' check if link to BoM_Temp table exists and if not create link
        On Error Resume Next
        Set tdf = dbs.TableDefs("BoM_Temp")
        If Err <> 0 Then
            DoCmd.TransferDatabase acLink, "Microsoft Access", strTempDb, acTable, "BoM_Temp", "BoM_Temp"
        Else
            varFld = tdf.Fields(0)
            If Err <> 0 Then
                ' refresh link if current link invalid
                tdf.Connect = ";DATABASE=" & strTempDb
                tdf.RefreshLink
            End If
        End If
        Err.Clear

    The above code snippet is from a module in a database which generates a bill of materials by simulating recursive querying by incrementally inserting rows into a temporary table in a temporary external database created earlier in the routine.  The dbsTemp object variable returns a reference to the external database object, the dbs object variable returns a reference to the current database object, and the strTempDb variable holds the full path to the external database as a string expression.

    Ken Sheridan, Stafford, England

    Friday, June 8, 2018 3:48 PM
  • I checked with the access 2.0 documentation and looks like transferdatabase is available.

    One question - if there are four users, each with the front end MDB, if I do create temp table, move temp table, link temp table, how do I keep all four MDBs up to date with all the linked tables if one is added by one of the users?

    Locally, what the report generation function does is look at a person table and then create a temp table (drop an recreate) for each person with data from the master data table.Since anyone could run this report, we'd have to drop and recreate the tables locally and remotely to ensure the latest data for the report

    • Edited by JHarding08 Friday, June 8, 2018 9:36 PM
    Friday, June 8, 2018 9:22 PM
  • Why do you need to create a temporary table?  A report would normally be based on a query which restricts the rows returned on the basis of parameters in the query, or by filtering the report by means of the WhereCondition argument of the OpenReport method of the DoCmd object.

    Ken Sheridan, Stafford, England

    Friday, June 8, 2018 10:33 PM
  • Why do you need to create a temporary table?  A report would normally be based on a query which restricts the rows returned on the basis of parameters in the query, or by filtering the report by means of the WhereCondition argument of the OpenReport method of the DoCmd object.

    Ken Sheridan, Stafford, England

    • Marked as answer by JHarding08 Tuesday, June 26, 2018 6:33 PM
    Friday, June 8, 2018 10:33 PM
  • I checked with the access 2.0 documentation and looks like transferdatabase is available.

    That's good to know.

    One question - if there are four users, each with the front end MDB, if I do create temp table, move temp table, link temp table, how do I keep all four MDBs up to date with all the linked tables if one is added by one of the users?

    Locally, what the report generation function does is look at a person table and then create a temp table (drop an recreate) for each person with data from the master data table.Since anyone could run this report, we'd have to drop and recreate the tables locally and remotely to ensure the latest data for the report

    When one user creates a temp table, how important is it for others to see what the first person created? Couldn't they just create their own temp table, for their own use? If so, then you could just let all users create their own temp table in the front end. There's really no need to move it and link to it if users will be deleting and recreating them regularly.

    Just my 2 cents...

    Saturday, June 9, 2018 12:34 AM
  • The more I think about it, the more this makes sense with how the report generation works.Temp tables will be created as part of that process, based on a linked table that is standard.  So it is true that each temp table generated will be only part of that one report generation at that time.

    Thank you all for helping me think clearly.

    Monday, June 11, 2018 4:31 PM
  • Hi,

    You're welcome. We're all happy to assist. Good luck with your project.

    Monday, June 11, 2018 4:40 PM