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. 
0
Utf
1/10/2010 1:12:01 AM
access.tablesdbdesign 510 articles. 0 followers. Follow

3 Replies
3138 Views

Similar Articles

[PageSpeed] 4

On Sat, 9 Jan 2010 17:12:01 -0800, Steve Stad
<SteveStad@discussions.microsoft.com> 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]
0
John
1/10/2010 1:25:42 AM
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
> <SteveStad@discussions.microsoft.com> 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]
> .
> 
0
Utf
1/11/2010 9:41:02 PM
On Mon, 11 Jan 2010 13:41:02 -0800, Steve Stad
<SteveStad@discussions.microsoft.com> 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]
0
John
1/12/2010 12:59:49 AM
Reply:

Similar Artilces:

I want to unlock my word doc to make changes its protected
I am writing a word document the other night. I went to carry on with it tonight but found it has protected the document and won't let me continue writing or editing Word 2007? Assuming that you have activated Office, it seems your trial version of the application has expired. Time to pay the piper! -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> ...

double clicking and draging a column in a chart to chg data
in Excel 2003, double clicking on a column in a chart and then dragging the column up or down would change the data in a table upon which the chart depended. How does one do this in excel 2007? Tom Hi, That feature has been removed in 2007, there is no way to do it. If this helps, click the Yes button. -- Thanks, Shane Devenshire "Tom of inns" wrote: > in Excel 2003, double clicking on a column in a chart and then dragging the > column up or down would change the data in a table upon which the chart > depended. > > How does one do this in excel 2007? &g...

Changing SQL Server for CRM 1.2
As my SQL server is currently on its last breath I am in the process of moving our CRM 1.2 databases to a new dedicated serever. I have detatched the databases from the old server, copied the files to the server and attached them. I have then gone into CRM deployment manager and changed to the SQL server to the new server. That all appears to work fine, no error messages. But when users try to access the CRM system they get an error and the CRM server event log fills with error messages. A few are listed below: dmLog: Failed to execute the SQL batch in the file sqlbatch.sql. MSCRM P...

transfer data from multiple columns to singlr column
I have data in form a d g b e h c f i (but larger scale) and I need it in a single column going a to z. Hi, highlight you data, copy, go to the column where you want to see the data, paste special, transpose "lc85" wrote: > I have data in form a d g > b e h > c f i (but larger scale) > and I need it in a single column going a to z. You up for using a macro? Sub ToOneColumn() 'dantuck Mar 7, 2007 &...

Territory Change
Our Sales VP has restructured all of the geographical territories that we had set up in Microsoft CRM. Our many thousands of Accounts are associated with territories. Obviously it is ludicrous to think that we would have to go one by one and change the territory on each account to the new territories. However, I have heard that there are strict rules for making changes directly to the backend SQL database. If we develop a SQL statement to change the territories assigned to each account to the new territories, are we going to be breaking anything? We don't want to screw up any de...

chart MAcro to change on activecell
Here is the macro below.. The only problem is that the columns change every month. so range (columns)keeps widening.... I have sorted it by selecting range upto column N. so it is provided for all 12 months. But now the "Grand total" column which is always the last column also gets included..(.which shouldnt be included in the range for the chart) Is there a way to modify this macro ? Sub updatechart1() Dim ThechartObj As ChartObject Dim Thechart As Chart Dim Userrow As Long Dim CatTitles As Range Dim SrcRange As Range Dim SourceData As Range If Sheets("summary").Ch...

Problems migrating BCM data into CRM SB edition
Hi There I am having a problem migrating data from Business Contacts Manager (BCM) into CRM 3.0 Small Business edition. I have downloaded the BCM data migration pack and have followed the data migration documentation to the letter. I even cleaned up the BCM database prior to copying the files, checking them for errors using the Manage Database option in the Business Tools menu. It gets so far through the migration process and then bombs out. Here is the final few entries from the log file: 28/10/2006 12:18:53------>Transitioning to next screen. From: ConfigurationSummary screen. To: ...

Can't open 2005 data file after reinstalling Money 2005
I am experiencing a recurring problem. I have had to reinstall Windows XP and MS Money 2005. I am now unable to open my previously converted 2005 file or restore any backup version. I consistently get the following error message: "Money cannot locate filename or cannot open it, possibly because it is a read-only file, you do not have permission to change it, or your disk drive is write- protected. If you have chosen the correct file and it cannot be accessed, you will need to click OK and then Restore your most recent backup file." Any help or thoughts would be greatly ap...

Font Sizing
I am visually impaired. I would like to increase font size in the IN-box window where the From: Subject: and Received: columns are listed. I have been able to increase font size to read the body of the e-mail and can compose e-mail with larger font - just would like to increase font size for the incoming message headers. Please write to me at: johnw@winserv.com if you can help. thank you in advance. John Depending on your version of Outlook, View->Current View->Customize Current View->Other Settings. You can customize the Fonts from there. --� Milly Staples [MVP...

forward to: field
Hi Everyone, im looking for the AD attribute in which the 'forward to:' field of the delivery options is stored. Thank you in advance kind regards marc -- Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/ Its in the altRecipient attribute. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Marc Wenger" <haga(at).iesg--nspm-noa9h.gmx.ch> wrote in message news:opr64hs7z6fyi4rt@news.microsoft.com... > Hi Everyone, > > im looking for the AD attribute in which the 'forward to:' field of ...

Changing font in Money 2004 register?
Is there any way to change the font(s) used in MS Money 2004's registers? The default font is too small and difficult for me to read. Also, is there any way to change the color scheme to something more pleasing to my eye (like you can do in Quicken...) Thanks. Nope and Nope. See http://umpmfaq.info/faqdb.php?q=136. "Debbie R." <debbimsr@bellsouth.net> wrote in message news:f5ff01c43e15$e2ae3700$a401280a@phx.gbl... > Is there any way to change the font(s) used in MS Money > 2004's registers? The default font is too small and > difficult for me to read....

Global Column Row Preview Font Size
I know I can change the column, row and preview font size for the current email folder's view, but how do I do it for all of the email folders? I have loads of email addresses each with lots of folders. I don't want to have to do each one at a time. Surely there must be a default font setting (even if it's only in the registry)? Thanks in advance, Tim. I too would love an answer to this. Ian "Timie Milie" <tim_milstead@yahoo.co.uk> wrote in message news:45782ee8$0$27107$db0fefd9@news.zen.co.uk... >I know I can change the column, row and preview font ...

custom field for projects with tasks past due
I am wondering how/what formula would be needed for creating a custom field at the project level to show when a project has at least one task that is past due. I have a custom field at the task level (IIf([Finish]<[Current Date] And [% Complete]<100,1,2)) that will show when an individual task is overdue, but I want to add a custom field to the Project Center > Summary view to show when a project has any overdue tasks. The PM can then drill down from there into the project and see exactly what tasks are overdue. Once I figure out the formula for this I want to assign ...

changing values of one field based on another
How can I best change the values of one field in a table based on values of another field of the same table. We have an existing table of thousands of entries and I would like to use the following logic to populate a new boolean field. If field1 = "Done" Then BooleanFieldCompleted = True I have some Excel VBA experience but limited Access. I dont want to do this manually! Any assistance appreciated. In general, you'd use an Update query. However, in this case I don't see why you'd need such a field. Why not just create a query with a computed field that returns True...

Change the Exchange Virtual Directory to different website
I would like to remove the exchange virtual directory default website and move it another website which is currently redirecting to the website I want to delete. Meaning rather than logon to OWA as http://www.wheresmylunch.com/exchange (current default website) I want to move to http://www.getyourownsandwich,com/exchange. I am using Exchange 2000 server. Rube You would change the host header on the current website. -- Hope that helps, Dan Townsend This posting is provided "AS IS" with no warranties, and confers no rights. Please do not send email to this address, post a reply t...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Getting the BCC field by default.
I would like the BCC field to show by default but cannot find the setting for this in 2007. Anyone know where this is located? In a new message window, use the Options chunk to display the "Show BCC." --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted without reading. After furious head scratching, JC HARRIS asked: | I would like the BCC field to show by default but cannot find the | setting for this in 2007. Anyone know where this is located? Thanks Milly. I had...

Copying Data in a cell in one sheet to a cell in another sheet
I've run into a problem trying to copy data from a cell in one sheet to another. I have a spreadsheet called "rating" which contains a number of formula that calculates a final number. I also have a spreadsheet called "Final" that copies over the information from "Rating". In "Final", I'm trying to copy a number from "Rating" into a particular cell. I put in =Rating! G89, but it won't work. When I press enter, a window pops up "Update value:Rating". I press enter again and in the cell where I want the number ...

Customer check data
When customers pay by check RMS asks for specific information such as drivers license number, routing number, account number, address and phone number. Does anyone have a report and or a way to extract this info from the database for cases when the check is returned for NSF? Please advise, Scott We can write you this report. Contact me for detail on price . Afshin Alikhani - [ afshin@retailrealm.co.uk ] CEO - Retail Realm = = = = = = = = = = "Scott Santorio" <scott@tt-newyork.com> wrote in message news:e8ZKkR6$HHA.3716@TK2MSFTNGP03.phx.gbl... > When customers pa...

Money 2000 Account Balance Changed Inexplicably
Opening Money 2000, which I've used without problem since late 1999, I noticed that my checking account balance was overstated by almost $2000! I went to the register to see if there was a false transaction entered and it appears that this balance change goes back years with no obvious single entry being the culprit. When I run the "balance this account" function, it shows that the closing balance from my last statement, which was correct and rectified, is now also wrong and reflects the higher balance. What do I do now? The only thing that I can think of is to restore...

Macro to seperate data
Hi I seem to be struggling to find a macro that will work in previous threads. In sheet 1 is a list of data in columns A:N and the number of rows will vary. It is a list of sales with each sale record ocuppying one row. The salesperson's name is in column C and each salesperson will have multiple entries. What I am trying to do is create a seperate summary sheet in the workbook for each salesperson. Therefore sheets 2 to 20 are templates that already exist with a different salesperson's name entered into cell C3 on each of them. I am trying to find a macro that ...

Need HELP! for Linking data
Could someone please direct me to where I can learn how to link date in a work book. i.e., I have individual pages for each subject but I need the data that is entered in these individual pages to transfer to the Master page without having to manually in put it.........TNX Bubey, There are not too many bits about linking worksheets or workbooks that I can find. But have a look at the links below, in case they give you the information you need. I think it is frustratingly one of those things which is very easy when you know how, or if you can get someone to actually show you, but if you hav...

Changing language
I am running the Swedish version of Excel for XP and I need the US (or English) version. Is there a way to convert the language and all the settings associated with it? Using the swedish version is rather annoying since the formula names are translated to swedish. Thank you, Magnus ...

With and import tool can you change only item description?
Is there a way to change only the item description on a large quanity of items. What about the extended description? Thanks for your help. Use the MS SQL Data Import Tool by EMS. $65.00. The QSImport Tool available to download from Microsoft will probably work but is not supported by Microsoft. Kinnard L. Kohler Business Machines Systems 6101 South Shackleford Road Little Rock, AR 72204-8606 (T) 501-375-8380 (F) 501-375-0043 (Cell) 501-412-5686 Email: kinnard@removebmsar.com "Lisa" wrote: > Is there a way to change only the item description on a large quanity of >...

Macro
I need a macro that help me to transfer name and address information from an specific table in excel to a template in words on specific areas and then print the word document. The reason for this is that i need to create diferents letters to be sent to the customers from the excel table. Example of the table is: soc seg, customer name, child name, customer code, add 1 , add2, city, estate, zip code. all this information will be paste on word letter template on specific areas or fields. Any suggestion!!! -- nicoro Hi IMHO the best approach would be to set up a mail merge documen...