Results 1 to 11 of 11
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Changing Tables or Renaming

    I need help to find the best way to change tables.

    I have an inventory (electronic parts database) with many Tables, Queries, and Reports.
    I now have a form that I can use to change table names, backup and change to Startup or Sample parts table. Only problem is it doesn't work with relationships.

    Using DoCmd.CopyObject , "tblParts", acTable, PartsName_global or DoCmd.CopyObject , "tblParts", acTable, "tblPartsBSample" ...

    I have tblParts and tblBins (locations) that needs to change for either Startup or Sample table or load a backup table.


    What would be the best way to accomplish this? VBA or Query or both? Can I delete relationships and them reinstall them? (not likely).
    I'm using Access 365 on a split single user database.
    Is only used to setup the Database to send to other users, tblStartup with only one record, tblBins would be empty. Sample tblParts, 400 items with tblBins info.

    I hope i'm clear on what I need.
    Thanks,
    Mad-Tom

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,805
    you should NEVER change table names.
    you can copy existing to a 'backup' , but there should be no method to rename working tables.

  3. #3
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Maybe changing table names is not what I'm doing. I'm renaming tblPartsBackup to tblParts or tblPartsStartUp to tblParts. Changing table info. I'm always using tblParts as my database table.
    Thanks

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    15,476
    ???Hmmmm??

    Tom, better tell us more about what you are trying to accomplish. Changing table names in an operational system would be extremely rare. Changing table names in the model or prototype - maybe. But this NOT a common event.

    Perhaps you should post a copy of your database to ensure we're all talking about the same thing.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,664
    I would try to work out a scheme with a split database. Different Backends for different purposes. Frontend stays the same for both.

  6. #6
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Different Backends for different purposes. Frontend stays the same for both is what I want. I'm just using a form to change up Backend to set it up for a new user.
    What I have now works using a form to backup tables, reload a table, or empty a table. I can also set my ver number and product code in my FE table.
    Problem is if I have relationships this form doesn't work.
    I found VBA code to delete relationships and another to add them, this may work.
    All this is for is to make the database easy to setup to share.

  7. #7
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    My code

    This is the code I'm using to change my table's data. tblParts name dosen't change, only the data, PartsName_global is the table name from a combobox.
    All this works for me untill I add relationships to my tblParts.
    Is there any way around this, without deleting relationships? Looking for options! Form used to setup database.
    Thanks!

    Code:
    Private Sub cmdReloadParts_Click()   'reload table tblPartsOn Error Resume Next
        DoCmd.Close acForm, "frmParts"   'close all forms
        DoCmd.Close acForm, "frmBuild"
        DoCmd.Close acForm, "frmSearch"
        DoCmd.Close acForm, "frmHelp"
        DoCmd.Close acForm, "frmColorCode"
        If IsNull(Me.Combo10) Then                               'check if Combox is emply
            MsgBox "You must choose Table in combobox below"
            Exit Sub
        End If
        If Not IsNull(Me.Combo10) Then               'add Combox name to PartsName_global
            PartsName_global = Me.Combo10
            MsgBox PartsName_global
        Else
            'MsgBox "Enter Table"
        End If
        If PartsName_global = "tblParts" Then                              'check if Combox is emply
            MsgBox "Can't use this table! This table is loaded!" & vbCrLf & "Select another table from combo box!"
            Exit Sub
        End If
        If TableExists(Me.Combo10) = True Then        'check if table exist, exit if no table
            'MsgBox "valid Table"
        Else
            'MsgBox "Not a valid Table"
            Exit Sub
        End If
         Dim OutPut As Integer
            OutPut = MsgBox("Do you want to replace Table?" & vbCrLf & "Do you want to continue?", vbOKCancel + vbQuestion + vbDefaultButton2)
        If OutPut = 1 Then
            'MsgBox PartsName_global
        Else
            Exit Sub
        End If
        DoCmd.CopyObject , "tblParts", acTable, PartsName_global ' copy PartsName_global to tblParts from combox selection
    End Sub

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,664
    Maybe this could help. If you have to delete your relationships in order to manipulate the tables, this code can re-establish relationship between 2 tables.
    Edit: The error handler calls a function, you'll have to change that to a message box display.

    Code:
    '---------------------------------------------------------------------------------------
    ' Method : fcnCreateRelation
    ' Author : davegri
    ' Date   : 2/27/2021
    ' Purpose: Create 1 to many relationship between 2 tables
    '   PrimaryTname is Parent table name
    '   ForeignTnams is Child table name
    '   PrimaryFname is Primary key Field name in Parent table
    '   ForeignFname is Foreign key Field name in Child table pointing back to parent
    '---------------------------------------------------------------------------------------
    Public Function fcnCreateRelation( _
        PrimaryTname As String, _
        PrimaryFname As String, _
        ForeignTname As String, _
        ForeignFname As String) As Boolean
        On Error GoTo fcnCreateRelation_Error
        Dim db As DAO.Database
        Dim NewRelation As DAO.Relation
        Dim RelatingField As DAO.Field
        Dim RelationName As String
        fcnCreateRelation = False
        RelationName = PrimaryFname & "$$" & ForeignFname
        Set db = CurrentDb()
        Set NewRelation = db.CreateRelation(RelationName, PrimaryTname, ForeignTname)
        Set RelatingField = NewRelation.CreateField(PrimaryFname)
        RelatingField.ForeignName = ForeignFname
        NewRelation.Fields.Append RelatingField
        db.Relations.Append NewRelation
        fcnCreateRelation = True
    fcnCreateRelation_EXIT:
        On Error Resume Next
        Set db = Nothing
        Set NewRelation = Nothing
        Set RelatingField = Nothing
        Exit Function
    fcnCreateRelation_Error:
        Select Case Err
            Case 3012       'already exists, ignore error
            Case Else
                Call fcnErrorMsg(Err.Number, Err.Description, " fcnCreateRelation of VBA Document Form_frmConversionDataset", , True)
        End Select
        Resume fcnCreateRelation_EXIT
    End Function
    Last edited by davegri; 02-27-2021 at 09:20 AM. Reason: Error msg

  9. #9
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Do you have a function fcnErrorMsg(Err.Number, Err.Description, " fcnCreateRelation of VBA Document Form_frmConversionDataset", , True)

  10. #10
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,664
    Just change it to:

    Code:
          Case Else            
              MsgBox Err.Number & ", " & Err.Description & " in fcnCreateRelation"
    Last edited by davegri; 02-27-2021 at 10:54 AM. Reason: edit

  11. #11
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    2,664
    Here's a little utility you may find useful. It will set up relational integrity links within a specified BE.

    MadTom-davegri-config-rel.zip

    Click image for larger version. 

Name:	MT.png 
Views:	19 
Size:	17.0 KB 
ID:	44422

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-21-2020, 11:48 AM
  2. renaming linked tables
    By MrMarmite in forum Import/Export Data
    Replies: 1
    Last Post: 04-23-2020, 10:24 AM
  3. Replies: 32
    Last Post: 09-15-2015, 06:13 PM
  4. Changing Tables Front-End DB are Linked To
    By JoeM in forum SQL Server
    Replies: 8
    Last Post: 03-31-2015, 11:09 PM
  5. Linked Tables and changing filename
    By a2music in forum Database Design
    Replies: 1
    Last Post: 02-28-2011, 03:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums