Table field property change

[questionit]

If we have 2 tables, which are linked to each other

And i want to change property of a field in one of the tables i.e Max Character Length, etc
how would i do it - becasue Ms Access doesn't us doing it on a table which is linked to another one ?

Do we have to remove the link first or there is any other way at all

Thanks

[missinglinq]

What version of Access are you using? And what exactly do you mean by linked tables? I ask because people have different ideas about what this means. If you mean that the tables are related, I can't reproduce the problem, as stated, in Access 2000. If you're decreasing the field length, you'll get a warning telling you that data may be loss, which may cause validation rules to be violated, but you can go ahead and do it. If the field involved is part of that relationship, i.e. the Primary Key in one table and the Foreign Key in the other, then yes, you have to remove the relationship, change the field lengths in both tables so that they match, them create the relationship again.

Good Luck!

Linq ;0)>

[ADezii]

If we have 2 tables, which are linked to each other

And i want to change property of a field in one of the tables i.e Max Character Length, etc
how would i do it - becasue Ms Access doesn't us doing it on a table which is linked to another one ?

Do we have to remove the link first or there is any other way at all

Thanks

You cannot change the Property of a Field in a Linked Table from the Current Database, Access simply will not allow it. One Method I use, however, is Automation code to modify a Field(s) programmatically from the Current Database without Deleting the existing Link. The following code will change the Field [Topics] in C:\Test\Testlink.mdb and make it Text 50. Let me nkow how you make out:

Dim strDB As String, MySQL As String
 
Dim appAccess As Access.Application
 
'Path to External Database
Const strConPathToExtDB = "C:\Test\Testlink.mdb"
 
'Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")
 
'Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToExtDB
 
'tblTopics - Table containing the Field (Topic) to modify
'after executing the SQL Statement, [Topic] is now a TEXT (50) Data Type
MySQL = "ALTER TABLE tblTopics ALTER COLUMN Topic TEXT (50);"
appAccess.DoCmd.RunSQL MySQL
 
appAccess.CloseCurrentDatabase
Set appAccess = Nothing

[questionit]

HI

I am new in VB.

Can i write this code in the same .mdb file which is currently i have opened ?

Also, would that code go in a class module, etc and how will i run it?

Thanks

You cannot change the Property of a Field in a Linked Table from the Current Database, Access simply will not allow it. One Method I use, however, is Automation code to modify a Field(s) programmatically from the Current Database without Deleting the existing Link. The following code will change the Field [Topics] in C:\Test\Testlink.mdb and make it Text 50. Let me nkow how you make out:

Dim strDB As String, MySQL As String
 
Dim appAccess As Access.Application
 
'Path to External Database
Const strConPathToExtDB = "C:\Test\Testlink.mdb"
 
'Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application")
 
'Open database in Microsoft Access window.
appAccess.OpenCurrentDatabase strConPathToExtDB
 
'tblTopics - Table containing the Field (Topic) to modify
'after executing the SQL Statement, [Topic] is now a TEXT (50) Data Type
MySQL = "ALTER TABLE tblTopics ALTER COLUMN Topic TEXT (50);"
appAccess.DoCmd.RunSQL MySQL
 
appAccess.CloseCurrentDatabase
Set appAccess = Nothing

[ADezii]

HI

I am new in VB.

Can i write this code in the same .mdb file which is currently i have opened ?

Also, would that code go in a class module, etc and how will i run it?

Thanks

Can i write this code in the same .mdb file which is currently i have opened ?

You most certainly can.

Also, would that code go in a class module, etc and how will i run it?

Do not put the Code in a Traditional Class Module. The code can be placed in a Function or Sub-Routine Procedure, it can be executed from the Click() Event of a Command Button, AfterUpdate() Event of a Control, Open() Event of a Form, etc.

[questionit]

Hi

I've tried your code but got this run-time error: '3720' - Cannot change field 'Name'. It is part of one or more relationships

You most certainly can.

How did you run it successfully ?

Do not put the Code in a Traditional Class Module. The code can be placed in a Function or Sub-Routine Procedure, it can be executed from the Click() Event of a Command Button, AfterUpdate() Event of a Control, Open() Event of a Form, etc.

[ADezii]

Hi

I've tried your code but got this run-time error: '3720' - Cannot change field 'Name'. It is part of one or more relationships

The Field(s) I ran the code against were not part of any Relationship. As the Runtime Error indicates, I do not think that you can change certain characteristics of a Field if it is part of a Relationship. The Relationship would have to be Deleted, the Field characteristic would have to modified, and then the Relationship would have to be re-established. Further Errors may occur depending on what changes you made, and if they involved Fields on both sides of the Relationship. This can be done programmatically but it would be a challenge and not be without risk. You may find yourself in a situation where you cannot Re-link the Tables involved in the Relationship after making the modifications.