Creating a New Table in a backend then linking the table to a front end

[NickWren]

I want to start off saying that I am still a beginner at access so forgive me if I use wrong terms or don't really clarify what I am talking about.

I have a form where we create new facilities; once the new facility information is entered, the VBA command will check the "FacilityName" if there is something there then it will do the following;

    DoCmd.RunCommand acCmdSaveRecord
    If Not IsNull(Me.FacilityName) Then
    Forms!frmFacilityMainEntry.Refresh
    Forms!frmFacilityMainEntry.Requery
    
    New_Facility = Me!FacilityName
    
    Dim objAcc As Object
    Set objAcc = New Access.Application
    objAcc.OpenCurrentDatabase ("\\Wrenlab01\administration\DATABASE\RESEARCH DATABASE\MULTIPLE MYELOMA DATABASE\MultipleMyelomaWrenLIS- BackEnd.accdb")
    
    objAcc.DoCmd.CopyObject "\\Wrenlab01\administration\DATABASE\RESEARCH DATABASE\MULTIPLE MYELOMA DATABASE\MultipleMyelomaWrenLIS- BackEnd.accdb", "tbl", acTable, "tblNewFacility"
    objAcc.DoCmd.CopyObject "\\Wrenlab01\administration\DATABASE\RESEARCH DATABASE\MULTIPLE MYELOMA DATABASE\MultipleMyelomaWrenLIS- BackEnd.accdb", "tblNew", acTable, "tblNewFacilityPatient"
    objAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", "\\Wrenlab01\administration\DATABASE\RESEARCH DATABASE\MULTIPLE MYELOMA DATABASE\MultipleMyelomaWrenLIS.accdb", acTable, "tbl", "tbl", Yes
    objAcc.DoCmd.TransferDatabase acImport, "Microsoft Access", "\\Wrenlab01\administration\DATABASE\RESEARCH DATABASE\MULTIPLE MYELOMA DATABASE\MultipleMyelomaWrenLIS.accdb", acTable, "tbl", "tblNew", Yes
    objAcc.DoCmd.Rename "tbl" & New_Facility, acTable, "tbl"
    objAcc.DoCmd.Rename "tblNew" & New_Facility, acTable, "tblNew"
    objAcc.CloseCurrentDatabase
    Set objAcc = Nothing
    DoCmd.Rename "tbl" & New_Facility, acTable, "tbl"
    DoCmd.Rename "tblNew" & New_Facility, acTable, "tblNew"
    End If
    DoCmd.Close acForm, "frmFacilityMainEntry"

This code will take template tables and create a copy of the table and put it in the backend; however the names remain as "tbl" and "tblNew", so I added the "DoCmd.Rename" and it still doesn't change the name of the tables. I am looking for this to be generic so we can keep creating more facilities.

Maybe I need a database refresh command because I notice the tables don't show up until I close and reopen the backend.

 

[Ranman256]

you would do this manually.
You should already have all tables you need design and functioning in a working app.
So if you need a new table, manually add it. This should not happen often.

 

[plog]

Databases aren't suppose to work like that. A table is a place to relate fields which store data, by creating a table for every facility you are storing relevant data in table names which defeats the whole purpose.

Instead of a new table for each facility, you should simply have a Facility table with a field in which you store the facility. That way, that table contains all your data and when you want just one facilities information, you build a query upon it.

You really need to rethink this whole process and move toward getting your data into a proper table structure.

 

[NickWren]

I can show you my table structure; but I know it is 100x easier to have just one table for all the facilities and a field to identify which facility it is associated with; but when your boss wants it done a certain way, you try to accommodate them. I mean clearly it is possible if there are commands for vba to do such a task; just the table names are giving me difficulties.

 

[Ranman256]

And some bosses dont understand databases and programming.

 

[Minty]

I'm afraid you are the designated database bod - not your boss. If you make a solid working system unless he's going to be maintaining it he should have no interest in you doing your job sensibly.

Please take the advice, you will end up with a complete pigs ear of a database otherwise, and trust me he'll be the first to moan when he wants to change something else and its a right royal pain the arse for you to do because of his apparent db structure requirements...

"I have the t-shirt"