I have one table field which was having foreign key relationship with other tables and I wanted to field size I removed relationship which it had with other tables in the database.
Still it does not allow me to change the field size. even after deleting relationship with other table.
you should be able to delete the link, save relationship, then alter the field size, repair relationship.
if still not working, copy the field to another 'copy' field : MyField2,
delete the old field (and the relationship)
resize new name field, rename new field to old field, reset relations.
Not true.
You can modify linked tables easily using code. All of these are possible:
- create or delete table
- add / modify / delete fields
- add / remove primary key
- add / remove indexes
For example this will add a text field size 5 to a linked table:
You can't alter the table design if any object based on that is open - query/form/report. Check & close other related objects
Not true.
You can modify linked tables easily using code. All of these are possible:
- create or delete table
- add / modify / delete fields
- add / remove primary key
- add / remove indexes
For example this will add a text field size 5 to a linked table:
No Colin, it does not look like it is working, I removed all the relationship with which was refering to the question table, compacted and close all the objects.
I am getting error message as attached in picture.
Please advise. [please note that this is 2003 database (.mdb) and I am working it with on 2010 access version.
currentdb refers to the db where the code is running and so that would be the FE. You CANNOT modify the structure of the linked table in the FE. You need to create an instance of the BE database
Set db = DBEngine.Workspaces(0).OpenDatabase(Me.txtFullDocName)
in this example, Me.txtFulDocName = the full path to the database that contains the tables I want to modify
I've run into this exact situation in a split DB. You need to be able to modify the BE because the relationships that are being enforced are where the tables are located. Not the linked tables, but the actual tables in the shared BE.
That usually means down-time if this is a shared BE file. And if so, that ALSO means everyone needs a new copy of the FE file, ...
First, it wouldn't surprise me that the FE needs updating for a BE change, and
Second, if you need to do this sort of thing, you should have a developer FE and BE set aside for testing for exactly this situation.
In that case, you make the changes to the developer FE & BE (AND you carefully write down each change). Then when it all works in the developer copy, you take the system offline, update the production master FE & shared BE, and then put it all back online. But in that case, you either notify every user to download a new copy OR you use one of the many tools you can find in this forum to update user FE files automatically.
I didn't go into all the caveats since I was on my way out. Updating a BE that is being actively shared whether you do in remotely via DDL or by simply opening the shared BE and modifying objects is just plain crazy.
I have an application that is sold to the public and therefore I don't have access to the BE when upgrades need to be made. That means I need to create a programatic solution. Since the user has the choice of using SQL Server or ACE as the BE, I provide a SQL Server script and I also create an Access database that updates the BE to the new version. I leave the SQL Server script to the DBA if that is the client's BE but the Access app they get to run themselves. The first thing the Access app does it to check for the lock file of the BE. If it is there, the upgrade will not run. If the lock file is not there, the app renames the BE so that no one can jump on in the middle of the process. Then it backs up the BE and runs all the various updates on the Be. The final step is to make another backup and then rename the BE back to the original name.
Both Pat and Doc have added important caveats since my last post.
To repeat, you CAN alter the structure of linked tables in the BE using code in the FE.
I have used the idea for many years when running version updates.
Here are two recent examples:
Code:
If CheckTableExists("tblRoutePlanner") = True Then
Set db = OpenDatabase(GetDatafilesFolder & "\UKAddressFinderBE.accdb", False, False, "MS Access;PWD=" & STR_PASSWORD & "")
If CheckTableExists("tblRoutePlanner") = True Then
Set tdf = db.TableDefs("tblRoutePlanner") ' table already exists
'Add EMailSent boolean field to tblRoutePlanner
If CheckFieldExists("tblRoutePlanner", "EmailSent") = False Then
Set fld = tdf.CreateField("EmailSent", dbBoolean)
fld.DefaultValue = False
tdf.Fields.Append fld
Call SetPropertyDAO(fld, "DisplayControl", dbInteger, CInt(acCheckBox))
End If
End If
End If
The CheckFieldExists function ensures that the code doesn't error if run more than once
In the next example, the code also handles different backend databases depending on the licence type
Code:
If CheckTableExists("PostcodeDistricts") = True Then
'select correct datafile for this licence type
Select Case strLicenceType
Case "Tablet"
Set db = OpenDatabase(GetDatafilesFolder & "\TPostcodes.accdb", False, False, "MS Access;PWD=" & STR_PASSWORD & "")
Case Else
Set db = OpenDatabase(GetDatafilesFolder & "\Postcodes.accdb", False, False, "MS Access;PWD=" & STR_PASSWORD & "")
End Select
Set tdf = db.TableDefs("PostcodeDistricts") ' table already exists
'Add Sectors text field
If CheckFieldExists("PostcodeDistricts", "Sectors") = False Then
Set fld = tdf.CreateField("Sectors", dbText, 255)
tdf.Fields.Append fld
End If
'populate Sectors field (using a separate function)
AppendDistrictSectors
End If
I have other examples where fields are modified or deleted. Also examples where linked tables are created or deleted
However, in all such code, the BE must not be in use by others at the time.
In addition, any relationships affecting the linked table(s) need to be broken and later re-made.
IIRC, that can also be done using code from the FE.. but I would need to check that as its been a long time since I've needed to do it.
As mentioned by Pat, I also do this using SQL scripts for SQL backends where the process is easier still.
You can use SSMS to create the script for you
In both cases (Access or SQL BEs), the new FE with the links to updated tables will then need to be distributed to all users
