By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,504 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,504 IT Pros & Developers. It's quick & easy.

Setting up relationships between back-end tables and front-end queries

P: 62
Hello -

I have an Access 2010 database that has a front end and a back end. Both are located in a shared folder on our server.

I am trying to work through the process of publishing this database to SharePoint 2010.

When I run the Web Compatibility Checker, I get the following errors:

Relationships that are not associated with a valid lookup field are incompatible with the Web.

These errors ( 3 ) are all associated with one query called Q_COMPANYDATA that is based on ODBC-linked tables on our SQL server.

Element Type: Relationship
Element Name: Q_COMPANYDATA.COMP_ID<-->T_ACTIVITIES.COMP_ID
Control Type: Relationship
Control Name: Q_COMPANYDATA.COMP_ID<-->T_ACTIVITIES.COMP_ID

How do I set up a relationship in the back-end to a query that is in the front end?

I tried creating the relationships between the query and the linked tables in the front end.

I also tried moving the query to the back end and setting up the relationships there. Neither option worked.

Thanks for any tips!
Sandra
Jun 5 '12 #1
Share this Question
Share on Google+
11 Replies


zmbd
Expert Mod 5K+
P: 5,287
It is hard to tell what is going on with your database from the information provided.

Can your queries pull the data from the SQL server?

take a look at:http://bytes.com/topic/access/answer...ds-tables-evil

Here's a list of compat errors... it's MS... so the info might be correct but useless:
http://office.microsoft.com/en-us/ac...010379061.aspx
Look at: "ACCWeb105016"

- It appears that you will have to create a lookup field... I suspect in the query given that the tables are in the SQL server and SQL doesn't support lookup fields at the table level if I remember correctly.

-Z
Jun 6 '12 #2

P: 62
Hello Z -

I did look up the ACCWeb105016 error, but as you say, not very helpful.

Yes, the query does pull the data from the server.

I am also in the process of moving the entire database to an SQL server so it probably makes sense to deal with this after the migration anyway.

Thanks!
Jun 6 '12 #3

zmbd
Expert Mod 5K+
P: 5,287
Sandra,

When you open the query, do you have a dropdown (combobox) list in the table for the [Comp_ID] or just a plan old box? Just to make sure I'm clear... this is when you directly open the query... not a form. If you have the plan old standard data entry field box, then from the query design, right click the field, properties, there is a lookup tab, that is where you might try and set your lookup information.

-z
Jun 6 '12 #4

P: 62
Hello Z -

Comp_ID is being pulled as a data field (ie not an auto-number) from the SQL table.

The source tables are read-only ODBC-connected tables from our CRM database.

I have attached an image of the design veiw, and the SQL is below.

Hope this helps!

Expand|Select|Wrap|Line Numbers
  1. SELECT dbo_INT_AUX_LISTING.LISTING_ID AS Comp_ID, dbo_INT_AUX_LISTING.COMPANY_NM, dbo_INT_AUX_DIRECTORY.DIRECTORY_ID
  2. FROM dbo_INT_AUX_LISTING LEFT JOIN dbo_INT_AUX_DIRECTORY ON dbo_INT_AUX_LISTING.OWN_DIR_ID = dbo_INT_AUX_DIRECTORY.DIRECTORY_ID
  3. WHERE (((dbo_INT_AUX_LISTING.COMPANY_NM) Not Like "*deceased*" And (dbo_INT_AUX_LISTING.COMPANY_NM) Not Like "*RETIRED*") AND ((dbo_INT_AUX_LISTING.LISTING_TYP_IND)=1) AND ((dbo_INT_AUX_LISTING.OWN_DIR_ID)=-1));
  4.  
Attached Images
File Type: jpg 06-06-12 5-03-34 PM.jpg (73.9 KB, 336 views)
Jun 6 '12 #5

zmbd
Expert Mod 5K+
P: 5,287
Open the access front end.
Open the query in design mode.
Right-click the [Comp_ID] field (looks like the first one in the table)
In the popup-menu, select properties.
In the "properties sheet" there should be a tab titled "lookup" - left click this tab... what do you see here?

Here's what you're looking for...


-z
Attached Images
File Type: jpg prop.jpg (41.8 KB, 1011 views)
Jun 7 '12 #6

P: 62
Sorry for misunderstanding your original request.

Both the General and Lookup tabs are blank - ie there are no values in any of the properties.

Thanks again!
Jun 7 '12 #7

zmbd
Expert Mod 5K+
P: 5,287
on a shoe sting here...

That is what I thought.

OK, I'm working on the following assumptions (I know)...
There is either a form or a control on a form that is using this query "Q_COMPANYDATA.COMP_ID" as the data-source; is that correct?

-z
Jun 8 '12 #8

P: 62
Yes - I have several form controls that use Q_COMPANYDATA.COMP_ID" as the record source, as well as several DLookups such as:

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("COMPANY_NM","q_CompanyData","Comp_ID = " & [Company_ID])
  2.  
Thanks again for your patience!
Sandra
Jun 8 '12 #9

P: 62
Yes - I have several form controls that use Q_COMPANYDATA.COMP_ID" as the record source, as well as several DLookups such as:

Expand|Select|Wrap|Line Numbers
  1. =DLookUp("COMPANY_NM","q_CompanyData","Comp_ID = " & [Company_ID])
  2.  
Thanks again for your patience!
Sandra
Jun 8 '12 #10

zmbd
Expert Mod 5K+
P: 5,287
let me think about this for a tad... I think we'll need to add the lookup in that properties tab we looked at in post 6
-z
Jun 8 '12 #11

zmbd
Expert Mod 5K+
P: 5,287
Ok,
Jumpping back into the fire.
One thought is to create another table within the frontend that has the fields within this table linked thru the lookup propeties to your linked table... thus allowing the use of the wizard for ease of creation of the lookup and then subsequent use of these local frontend tables within your query.

The other option was to actually go into your linked tables within the frontend and do the lookup linking within the origninal tables.

While researching the nuances of these options and to see if we could work within the query, I ran accross the following website: http://rogersaccessblog.blogspot.com...-creating.html this person appears to have had some of the same issues as you're experiencing with your database. You may want to start at the begining of his log.

What I can not stress enough, before we do anything else to this project, is... MAKE A BACKUP!
>> Use this COPY as the experimental version to work with.
My Rule#1 is to NEVER, EVER, use the production or the only copy of my database or front-end to experiment with. It is easy to either copy from the Backup to the production db or to make the Working Copy the production db.
BACKUP, BACKUP, BACKUP!!!!

Ok off my soapbox... sorry. :)

So read thru his blog... that is what I'm doing here after a short nap and some family time... and chew on it for a little bit. IF you get any ideas from the blog and try them out PLEASE post your efforts and the results here...

Also, in the meantime, I really hope that someone like Rabbit or NeoPa has more experience and can offer a better solution!
-z
Jun 9 '12 #12

Post your reply

Sign in to post your reply or Sign up for a free account.