Enforce Referential Integrity not available.

I have a db that will eventually be split.
tblChild has PK ChildID and is a linked table
tblNote has FK ChildID (long).
Relationship window will allow me to drop PK on FK and declares
a one-to-many relationship. The Referential Integrity check
boxes are unavailable. If I create the relationship anyway
both ends of the line have just a small blob rather than
a 1, many or arrow.

I am guessing there's nothing I can do about it and that it is
happening because the table on the one side is external. Right?

What happens when I split and the tables are in different
back ends? Am I in trouble?

-- 
Len
______________________________________________________
remove nothing for valid email address. 


0
Len
12/17/2009 8:41:14 AM
access.tablesdbdesign 510 articles. 0 followers. Follow

6 Replies
711 Views

Similar Articles

[PageSpeed] 59

That's correct. Access is unable to enforce RI across different data files.

When you split your database, the relationships exist in back end.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


".Len B" <gonehome@internode0.on0.net> wrote in message 
news:#Yy00SvfKHA.6096@TK2MSFTNGP02.phx.gbl...
> I have a db that will eventually be split.
> tblChild has PK ChildID and is a linked table
> tblNote has FK ChildID (long).
> Relationship window will allow me to drop PK on FK and declares
> a one-to-many relationship. The Referential Integrity check
> boxes are unavailable. If I create the relationship anyway
> both ends of the line have just a small blob rather than
> a 1, many or arrow.
>
> I am guessing there's nothing I can do about it and that it is
> happening because the table on the one side is external. Right?
>
> What happens when I split and the tables are in different
> back ends? Am I in trouble?
>
> -- 
> Len
> ______________________________________________________
> remove nothing for valid email address. 

0
Allen
12/17/2009 10:45:40 AM
So I won't be able to create this relationship then because
each table will be in a different, the existing one and the
one from the split.

I suppose that means that I'll need to handle RI by joins
in queries.

-- 
Len
______________________________________________________
remove nothing for valid email address.
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:eJQrTYwfKHA.1536@TK2MSFTNGP06.phx.gbl...
| That's correct. Access is unable to enforce RI across different data 
files.
|
| When you split your database, the relationships exist in back end.
|
| -- 
| Allen Browne - Microsoft MVP.  Perth, Western Australia
| Tips for Access users - http://allenbrowne.com/tips.html
| Reply to group, rather than allenbrowne at mvps dot org.
|
|
| ".Len B" <gonehome@internode0.on0.net> wrote in message
| news:#Yy00SvfKHA.6096@TK2MSFTNGP02.phx.gbl...
| > I have a db that will eventually be split.
| > tblChild has PK ChildID and is a linked table
| > tblNote has FK ChildID (long).
| > Relationship window will allow me to drop PK on FK and declares
| > a one-to-many relationship. The Referential Integrity check
| > boxes are unavailable. If I create the relationship anyway
| > both ends of the line have just a small blob rather than
| > a 1, many or arrow.
| >
| > I am guessing there's nothing I can do about it and that it is
| > happening because the table on the one side is external. Right?
| >
| > What happens when I split and the tables are in different
| > back ends? Am I in trouble?
| >
| > -- 
| > Len
| > ______________________________________________________
| > remove nothing for valid email address.
|



0
Len
12/17/2009 12:10:29 PM
You can certainly create the joins, but you have to manage the integrity of 
the data yourself: making sure you cannot delete/edit data in the primary 
table that's referenced by the secondary, or insert/edit data in the 
secondary that doesn't match the primary.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


".Len B" <gonehome@internode0.on0.net> wrote in message 
news:u3JwwHxfKHA.2164@TK2MSFTNGP02.phx.gbl...
> So I won't be able to create this relationship then because
> each table will be in a different, the existing one and the
> one from the split.
>
> I suppose that means that I'll need to handle RI by joins
> in queries.
>
> -- 
> Len
> ______________________________________________________
> remove nothing for valid email address.
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
> news:eJQrTYwfKHA.1536@TK2MSFTNGP06.phx.gbl...
> | That's correct. Access is unable to enforce RI across different data
> files.
> |
> | When you split your database, the relationships exist in back end.
> |
> | -- 
> | Allen Browne - Microsoft MVP.  Perth, Western Australia
> | Tips for Access users - http://allenbrowne.com/tips.html
> | Reply to group, rather than allenbrowne at mvps dot org.
> |
> |
> | ".Len B" <gonehome@internode0.on0.net> wrote in message
> | news:#Yy00SvfKHA.6096@TK2MSFTNGP02.phx.gbl...
> | > I have a db that will eventually be split.
> | > tblChild has PK ChildID and is a linked table
> | > tblNote has FK ChildID (long).
> | > Relationship window will allow me to drop PK on FK and declares
> | > a one-to-many relationship. The Referential Integrity check
> | > boxes are unavailable. If I create the relationship anyway
> | > both ends of the line have just a small blob rather than
> | > a 1, many or arrow.
> | >
> | > I am guessing there's nothing I can do about it and that it is
> | > happening because the table on the one side is external. Right?
> | >
> | > What happens when I split and the tables are in different
> | > back ends? Am I in trouble?
> | >
> | > -- 
> | > Len
> | > ______________________________________________________
> | > remove nothing for valid email address.
> |
>
>
> 
0
Allen
12/17/2009 1:38:03 PM
".Len B" <gonehome@internode0.on0.net> wrote in
news:#Yy00SvfKHA.6096@TK2MSFTNGP02.phx.gbl: 

> What happens when I split and the tables are in different
> back ends? Am I in trouble?

Why would you do that? If the tables are sufficiently related that
it is necessary to enforce RI, then they belong in the same back-end
file. If you are splitting them up because your dataset is getting
too big, then you've outgrown Jet/ACE as a data store and should
instead use a more suitable back end, i.e., one that can handle more
than 2GBs of data. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
12/18/2009 12:24:43 AM
Hi David,
I am not splitting them up. They are already in two
different databases.

At the moment the 'new' database links to two tables in
a database created several years ago. The 'old' database
isn't split but it has been in the back of my mind on
and off that I should split it.

Earlier today I was thinking about splitting both
databases and combining the back ends. I came back here
to ask Allen if he foresaw any problems with doing that.
The 'old' database isn't large. The principal table has
almost 1,000 records. The 'new' is really an unknown
quantity but I expect it to outgrow the old in 12-18 months.

-- 
Len
______________________________________________________
remove nothing for valid email address.
"David W. Fenton" <XXXusenet@dfenton.com.invalid> wrote in message 
news:Xns9CE4C5787BB52f99a49ed1d0c49c5bbb2@74.209.136.91...
| ".Len B" <gonehome@internode0.on0.net> wrote in
| news:#Yy00SvfKHA.6096@TK2MSFTNGP02.phx.gbl:
|
| > What happens when I split and the tables are in different
| > back ends? Am I in trouble?
|
| Why would you do that? If the tables are sufficiently related that
| it is necessary to enforce RI, then they belong in the same back-end
| file. If you are splitting them up because your dataset is getting
| too big, then you've outgrown Jet/ACE as a data store and should
| instead use a more suitable back end, i.e., one that can handle more
| than 2GBs of data.
|
| -- 
| David W. Fenton                  http://www.dfenton.com/
| usenet at dfenton dot com    http://www.dfenton.com/DFA/



0
Len
12/18/2009 2:05:39 PM
".Len B" <gonehome@internode0.on0.net> wrote in
news:edf5xs#fKHA.1648@TK2MSFTNGP05.phx.gbl: 

> I am not splitting them up. They are already in two
> different databases.
> 
> At the moment the 'new' database links to two tables in
> a database created several years ago. The 'old' database
> isn't split but it has been in the back of my mind on
> and off that I should split it.
> 
> Earlier today I was thinking about splitting both
> databases and combining the back ends. I came back here
> to ask Allen if he foresaw any problems with doing that.
> The 'old' database isn't large. The principal table has
> almost 1,000 records. The 'new' is really an unknown
> quantity but I expect it to outgrow the old in 12-18 months.

If the tables have data that is related to each other, they belong
in the same back end. I don't think that's an issue on which there
can be any dispute. 

-- 
David W. Fenton                  http://www.dfenton.com/ 
usenet at dfenton dot com    http://www.dfenton.com/DFA/
0
David
12/18/2009 10:49:03 PM
Reply:

Similar Artilces:

project accounting integration with proposals
Has anyone done any developmenet work on providing a proposal or bidding module that can be integrated with great plains project accounting? I have a client that has a bidding program developed in Microsoft Access that he is now looking to recreate and integrate with his project accounting module. We are first looking to not recreate the wheel. Hi Bud, We are currently working with one of our prospect , a web based sales proposal for GP. If you are interested in learning more please drop a email. nj.tech@hotmail.com "Bud" wrote: > Has anyone done any developmenet work o...

Simply accounting integration
Has anyone come accross any 3rd party components that provide integeration from MS CRM to simply accounting? I am mostly interested in the invoicing side of it. Ie. create invoice in CRM fired of some info to simply accounting for creating the invoice there. Thanks for any leads. Rick. ...

Help with tracking user without enforcing security
I know that the best way to track who is in the db is to enforce security. However, I am in a situation where the client does not want the security to be enforced at least right now. since we will have to keep another file (.mdw) on the network. Is there a way to track few users (5 max) in access through code? I have a login form that has 2 text boxes, one for the user ID and the other for the password. I have a table called tblUserLog that every time a user logsin, a new record is created in that table keeping the login ID and date/time. The login form fires the code in the after updat...

Integration Manager
I am trying to create an SOP integration that contains multiple line items. I am using one text file that contains multiple line items, but is seperate by an invoice id. I am having problems with the integration manager selecting the line. I have read through the user guide and looked on the knowledge base already. Does anyone have any type of additional documentation or examples on creating an SOP integration? I have always started my integrations by using the examples that great plains provides. You should see a SOP integration with the integration containing two tab delimited t...

Enforce No of Characters using Jscript
Hi I need a solution to what may seem like a simple bit of Jscript. We would like to enforce that our staff insert a minimum number of characters in a certain field. I assume my script would look like: if (crmForm.all.new_description.DataValue >50) alert ('Please insert a sufficient description') The alert works well. I just need to know how to enforce the minimum number of 50 characters for the field. Guess there is a bit of Jscript for (Characters) that I am missing. Please assist Many Thanks Mark Use .length attribute to get the length of the text: if (crmForm.all.new_...

Integration to Baan
Hi, Has anybody tried integrating MS CRM with Baan? Is there integration kit available for the same? Regards Dear Shankar, My company has developed an integration with Baan IV through Biztalk 2004. If you want more information, please contact me on the following e-mail address. jean-paul.geelhoed@qnh.nl "Shankar" wrote: > Hi, > > Has anybody tried integrating MS CRM with Baan? Is there integration kit > available for the same? > > Regards > > > ...

Enforce format
How can I "enforce" a format in a cell in excel so that it allows number entry only, i.e. it does not allow letters or any other characters. Thanks in advance. Keith Data Validation; custom formula; If active cell is A1, use this: =NOT(ISERROR(1*A1)) "Elfey1001" <Elfey1001@discussions.microsoft.com> wrote in message news:D744EEFC-3BD0-43A7-B673-F16107BBFA03@microsoft.com... > How can I "enforce" a format in a cell in excel so that it allows number > entry only, i.e. it does not allow letters or any other characters. > Thanks in advance. > Kei...

Location of Integration Manager Database
Is there a problem with putting the IM.mdb file on a network server and letting multiple people access it? Everything I've read about IM seem to imply that the database should be put on the workstation. I realize there may be a path issue if drive mappings aren't consistant. Is that the only issue or are there others? Bob, You can put the IM database on a network and allow multiple users to access it. That is how i configure my integrations. It also hs the advantage of having one central place for all integrations and the usually server gets backed up, so you have it on backp if y...

Enforce Passwor Policy
Bonjour! I'm wondering when we create a user in GP, the 2 settings (Enforce Passowrd Policies, Enforce Password expires) that are in the Advanced Option, are related to Active Directory Group policy? Because in SQL we can't modify those settings. It is for our auditors to explain. Regards, Joel :) Hi Joe Yes, the configuration of the these two settings comes from the Goup policy domain setting. So if you your windows password expires every 90 days, your SQL acount password will as well. Just remember the two accounts are not tied to each other in anyway, so your w...

Problem to load Sales Orders with Integration Manager
I need a hotfix to load the sales ordes with integration manager for GP ver. 8, but the page didn't show me the file; it only show me a hyperlink to open an incident. Could you help to get this hotfix, please. Thanks. Jose Fabre You need to open an incident with GP to receive the Hot Fix. they should not charge you for the incident. Follow the hyperlink and send a request to GP. -- www.fmtconsultants.com "Jose Fabre" wrote: > I need a hotfix to load the sales ordes with integration manager for GP ver. > 8, but the page didn't show me the file; it only sho...

Making Characters from Character Viewer More Easily Available
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I'm writing a manual where I need to often use both the Place of Interest Sign that denotes the Command key, as well as the Upwards White Arrow that typically means the Shift key, and I'm getting those from the Character Viewer, thanks to information from this forum. I'm wondering, however, if there is a way to make those characters available more readily, so that I might insert them with specially designated keystroke combinations, as you can with the symbols in Word? I'm using them enough now...

Integration Transactions Pls help
Can anyone let me know how much time does the integration manager takes to upload a 10,000 lines(SOP) into GP There are two methods of importing SOP transactions. One uses the SQL optimized adapter and one does not. The method used depends upon the data you would need to map. There are some fields and business logic not available with the SQL adapter. But, assuming you are using the SQL adapter, you should be able to import 10,000 transactions in about 60 minutes or less. If you are using the other one, start it and go home for the night. It will take hours. If you have some reso...

TFS / Source Control Integration with SSMS
Please forgive me if I am posting this to the wrong group. I'm in the process of building a new laptop running Windows 7 64-bit, and I have the SQL 2008 client tools with SP1 as well as Visual Studio 2008 Team Edition with SP installed1. We use TFS at work, so I am trying to determine what I need to install to do the following: 1. Use TFS (or is it Team Explorer?) within BIDs to connect to the server than contains all of our source control items. From what I recall I just need to install a plugin, but I can't for the life of me remember what media I did that from i...

Scheduling Integration manager with a parameter
Has anyone scheduled IM to run and passed in a variable to be used in the integration? Can it be done? I have an integration that prompts for a date which is used to filter the source data. I want to schedule it to run and pass in the value for that date in the command line. How would the command line be written? VGrinam Hello VGrinam, I have achieved the result you are after. In the batch file that would call IM, I firstly set an environment variable to the value to be used parameter. Within the Begin Integration VBScript of IM, I read the environment variable for the parameter n...

CRM integration with Dynamics SL
If you are interested in integrating Dynamics SL with Dynamics CRM, please contact Lynn at lshepherd@capturesolutions.com or 724-334-2780 x101. ...

Integration Released Later?
Is it true that integration 1.2 will be released later than CRM 1.2? What's the deal with that? For CRM Pro customers, it's pointless to get CRM 1.2 before the integration is released. And why can't MS get these 2 released together? This product has so far been a major disappointment to us. It's been very buggy, releases are delayed, support isn't always strong, migration is less than stellar, etc. -- Brandon IT Director Presentations Direct - Office Equipment & Supplies http://www.presentationsdirect.com Integration v1.2 hasn't been released yet - but In...

SMTP Calendar Integration
Has anybody been able to integrate the MSCRM Calendar with the Groupwise Calendar via smtp connectors/web services integration? ...

Integrate Authorisation via WAN
Our Extranet Customer will that they can use Explorer View and other webdav functionality with the same Active Directory Account from their LAN. Did somebody have an idea how I can validate them via WAN on our FARM ? Thanks ...

enforcing macros
hello, wishing everyone a very prosperous and happy new year. i have created a workbook that has username validation and that is to be used over a network. all the background macros run and thus validate the username only when macros are enabled. if someone disables macros, they could easily tamper with the workbook. hence, i hid sheets in the workbook and protected the structure of the workbook with a password. in the workbook_open event i would provide the password and unhide the sheets (xlsheetvisible). again, in the workbook_beforeclose event i would use "xlveryhidden" to hid...

ISA Server Breaks CRM Integration
I recently updated our Small Business Server and now CRM integration isn't working. I assume it's because of the ISA Server 2004 update. What setting can I change in ISA to allow the "intuser" to connect to the CRM server? -- Brandon Presentations Direct - "Document Finishing Solutions" http://www.presentationsdirect.com Brandon, You've opened a real can of worms. ISA 2004 with SBS 2003 seems to be causing all kinds of problems with CRM 3.0, mine included. If all else is working well though otherwise, you may simply need to re-publish your CRM from...

Enforce leave a copy in server
As I know, use "Outlook POP3->Leave a copy on the server" , can save all emails on the server. But I Dont want to ask our users one by one to do such setting, I just want to do it in the Exchange Server (2003). Is it possible? Thanks It's not a server-side setting, rather a client option. The only way you could potentially manage this is if your users are all part of a domain and if the Office Admin template add-in for Group Policies allowed you to manage this. There are no settings on the POP3 virtual server that force it to tell the clients to leave a copy on the s...

CRM Integration With GP 8.0
Hi, I have a problem with CRM Integration with GP. I'm using GP 8.0 and CRM 1.2. When I'm trying to create a new order in CRM and submit the order, Integration Status Description shows this error message: "An error occurred while attempting to retrieve the note index from the company." But, the order created in GP has successfully integrated. Can somebody help me please...? Thanks. Hi Willy, Please ensure that GP company you are using has company address specified in it. Go to Tools->setup-> Company in GP 8.0. Thanks, Debarghya SDET CRM Integratio...

MFC code integrated with normal C,C++ code.
Hello Are there any real issues involved when I integrate my C code with MFC code. I am integrating my C,C++ file in the MFC generated project workspace. e.g. I have my normal C,C++ files as Implement.cpp, cli.c In Implementer.cpp I hav class myclas .. static myclass *getOb() Assuming Project name gui I have my MFC files as gui.cpp, guiDlg.cpp , stdafx.cpp. When I use myclass::getOb() in my guiDlg.cpp I get an error error LNK2001: unresolved external symbol public: static class myclass* __cdecl myclass::getObA(void)"... Can anybody plz tell me why am I getting this error ? B...

Integration Support for Developers
To all the integrators out there who are programming connectors (using the SDK or not) for CRM, what support tools are you using besides this newsgroup and the SDK itself? I feel there's a serious lack of support information out there. I'm wondering if there's anyone else who would like to see a CRM newsgroup for integrators - as this newgroups is almost entirely dedicated to general questions. Cheers, Anil I agree! This newsgroup and the SDK itself are also my only 2 sources of information. Cheers, Fred "Anil" <anil@doesntwork_anti_spam.com> wrote in mess...

No spell checking available
How can I get spell check on my machine. I have outlook express, version 6.0, Dell XP home edition. It has never been turned on since I got this computer. I am wondering how to activate it. This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for Intel PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x microsoft.public.windows.inetexplorer.ie55.outlookexpress for OE 5.5x microsoft.public.win...