A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

can't change the data type or field size



 
 
Thread Tools Display Modes
  #1  
Old January 10th, 2010, 02:12 AM posted to microsoft.public.access.tablesdbdesign
Steve Stad
external usenet poster
 
Posts: 89
Default can't change the data type or field size

I am creating/editing updating fields in table design. I am trying to change
the field size of the first field. But getting message.. 'You can't change
the data type or field size of this field, it is part of on or more
relationships. I deleted the relationship and still get the message. Can I
screw anything up if I delete any other relationships. Or does access
re-connect all relationships on demand.
Thanks for any replies.
  #2  
Old January 10th, 2010, 02:25 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default can't change the data type or field size

On Sat, 9 Jan 2010 17:12:01 -0800, Steve Stad
wrote:

I am creating/editing updating fields in table design. I am trying to change
the field size of the first field. But getting message.. 'You can't change
the data type or field size of this field, it is part of on or more
relationships. I deleted the relationship and still get the message. Can I
screw anything up if I delete any other relationships. Or does access
re-connect all relationships on demand.
Thanks for any replies.


You may have a hidden relationship; for example, Lookup fields create
relationships which may not show in the relationships window. Also, the
relationships window can be misleading: if you just delete a table icon, the
join line disappears but the relationship doesn't. You must select and delete
the *join line itself*.

Try the "Show All Relationships" icon on the relationships window, or call
this code from the Immediate window:

Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, rel.ForeignTable
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub
--

John W. Vinson [MVP]
  #3  
Old January 11th, 2010, 10:41 PM posted to microsoft.public.access.tablesdbdesign
Steve Stad
external usenet poster
 
Posts: 89
Default can't change the data type or field size

John - Thanks for quick reply. I tried this code in the immediate window.
....to 'call
this code from the Immediate window'?? do I compile??...step into??...run
macro??? sorry i am a newbie particualy w/code. What should I expect to
see? I opened the imediate window from blank form/design mode.

Private Sub Form_Load()
Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = MasterDB
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, rel.ForeignTable
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub


"John W. Vinson" wrote:

On Sat, 9 Jan 2010 17:12:01 -0800, Steve Stad
wrote:

I am creating/editing updating fields in table design. I am trying to change
the field size of the first field. But getting message.. 'You can't change
the data type or field size of this field, it is part of on or more
relationships. I deleted the relationship and still get the message. Can I
screw anything up if I delete any other relationships. Or does access
re-connect all relationships on demand.
Thanks for any replies.


You may have a hidden relationship; for example, Lookup fields create
relationships which may not show in the relationships window. Also, the
relationships window can be misleading: if you just delete a table icon, the
join line disappears but the relationship doesn't. You must select and delete
the *join line itself*.

Try the "Show All Relationships" icon on the relationships window, or call
this code from the Immediate window:

Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = CurrentDb
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, rel.ForeignTable
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub
--

John W. Vinson [MVP]
.

  #4  
Old January 12th, 2010, 01:59 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default can't change the data type or field size

On Mon, 11 Jan 2010 13:41:02 -0800, Steve Stad
wrote:

John - Thanks for quick reply. I tried this code in the immediate window.
...to 'call
this code from the Immediate window'?? do I compile??...step into??...run
macro??? sorry i am a newbie particualy w/code. What should I expect to
see? I opened the imediate window from blank form/design mode.


Sorry! Too brief there on my part.

Open the database. Find the Modules tab and create a new module. Copy and
paste


PUblic Sub ShowAllRelations()
Dim db As DAO.Database
Dim rel As Relation
Dim fld As Field
Set db = MasterDB
For Each rel In db.Relations
Debug.Print "Relation "; rel.Name, rel.Table, rel.ForeignTable
For Each fld In rel.Fields
Debug.Print fld.Name; " linked to "; fld.ForeignName
Next fld
Next rel
End Sub

into a new Module. Click Debug.Compile from the Menu; post back if you get any
errors.

Save the module using some name OTHER than ShowAllRelations - basRelations
maybe.

Then type Ctrl-G if the immediate window isn't visible. When it is, type

Call ShowAllRelations

into the immediate window.

You should see a scrollable list of all your table relationships.
--

John W. Vinson [MVP]
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:33 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.