473,324 Members | 2,473 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

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

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
11 4042
zmbd
5,501 Expert Mod 4TB
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
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
5,501 Expert Mod 4TB
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
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, 407 views)
Jun 6 '12 #5
zmbd
5,501 Expert Mod 4TB
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, 1219 views)
Jun 7 '12 #6
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
5,501 Expert Mod 4TB
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
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
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
5,501 Expert Mod 4TB
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
5,501 Expert Mod 4TB
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

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

Similar topics

0
by: Dave Roe | last post by:
This is more of a general SQL relationship question than a PostgreSQL-specific question, although I am using PostgreSQL to implement this. I have a number of data structures which I am calling...
2
by: John | last post by:
I'm trying to normalize my database the best I can this time around and I'm running into some trouble. I've been avoid normalizing for a long time now because while I know how to normalize, I'm...
2
by: Megan | last post by:
hello everybody, i know this is a very long post, but i wanted to provide as much detail as possible. quick overview- i want to create a couple of many to many relationships and am wondering...
49
by: Mal | last post by:
Hi, As I gain knowledge through a lot of trial, error, and usenet posts.. I have a potentially odd question. I am using a commercial access application. It is a front-end / back...
6
by: GaryDave | last post by:
My school registration database has not been quite right after a recent compact and repair (done while I was away). Though most of the many forms and subforms are working normally, one form in...
1
by: | last post by:
Hi, I am loading some crystal reports in a method similar to this (see below). And set the cursor to waiting while the reoprt is loaded, run and shown. BUT while the report is running the cursor...
2
by: junlia | last post by:
Hi All, I am working on a project that acts as a bridge. It does some checking with post xml data, and then redirects the request to an appropriate page. However, we find that depends on the...
45
by: salad | last post by:
I'm curious about your opinion on setting relationships. When I designed my first app in Access I'd go to Tools/Relationships and set the relationships. Over time I'd go into the window and see...
0
by: MLH | last post by:
I have a table, tblCorrespondence, that has been losing its KeyField setting. The field is named CorrespID and is an AutoNumber type field. This has happened in A97 on 3 occasions recently I...
4
by: MarwaAlSagheer | last post by:
private void ChangeButtonsBackgroundimage(string NamesOfImageList,string FolderName) { try { ArrayList Arr_Button = new ArrayList(); Arr_Button.Add(this.button1);...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.