We help IT Professionals succeed at work.

Access Web App Relationship with Table

88 Views
Last Modified: 2017-11-20
Hallo Stefan,
ich hoffe Du kannst mir helfen. Ich bin MVP für SfB, schreibe aber gerade mal an eine AWA.
Mir ist bei aufgefallen, dass eine Relationship zwischen zwei Tabellen trotz Lookup nicht gesetzt ist.
Kannst Du mir hier helfen?
Eigentlich wird bei einem Lookup einen 1:N Realtionship gesetzt, was auch bei allen anderen Lookups passiert ist.
Nur bei einer Tabellenbeziehung nicht.
Da ich breits Daten (naja jede Menge Daten) in der Tabelle habe, kann ich das Feld auch nit löschen und neu anlegen.



LG
Thomas
Comment
Watch Question

Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hallo Stefan,
Is this question only addressed to Stefan?
If not, you should wrote it in English. No many people here knows German.

Author

Commented:
Sure Vitor, if other want to help, any suggestion ist very much welcome.
I saw that a relationship between two tables is not set, even the lookup to the second table is correctly set.
this should be therefor a 1:n relationship. those relationship from lookups could only be verified when you create a query and add those tables.
I have seen that all other lookups created the correct relationship, just not between those tables.

I can delete the field anymore, since I have a huge amount of data imported already.
Any changes I made within the lookup, where the relationship is set doesn't change anything and are not bringing back the relationship.
I start a new query based on those both table, the query therefore can be saved, since Access identified a problem. and not reason has been shown so far.
In this new query, if a manually define the relationship it also doesn't accept the query, since the many lookup based relationship is missing.

Any idea how to solve this issue, without exporting all data, delete the field and recreate the lookup, than importing the data back?
Btw this would take a few hours to do so

Cheers
Thomas
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Is this a SQL Server database or Access database?
I can see that you posted the question in SQL Server area but your description seems that you're working with Access.
If you want to create the relationship in SQL Server and do not want to run T-SQL commands, you can use SSMS to create a new diagram and add the tables that you need for the relationship and then follow this article.

Author

Commented:
since there is no special forum for Access Web App, its more related to SQL.
That why I named Stefan as an Access MVP.
SharePoint uses SQL in O365 for SP Lists in the background.
But you have to use Access Desktop App configuring the SP Databases.
But again it is not native SQL

Cheers and thanks Vitor
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
I've added MS Access topic. It might help if some Access Expert have knowledge with Access Web App.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Crystal,
tnx, its an very amazing video. And btw next time I start the Access App from a desktop app.

But the solution isn't in there, I'm sorry.
The lookups are correct between those both tables, but the relation isn't set and even defining a new relation in a new query wont allow the query being saved. therefor the corrupted relation still stays.
And I don't see anyway how to fix it.

If you have further suggestion, Or if you want to have a look with me together into the database, you are welcome
Thomas
Remote Training and Programming
CERTIFIED EXPERT
Top Expert 2015
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Crystal,
I knew it :( So it will ending up in a weekend job. There are 3.500 rows in this table ...
Bad, that I haven't seen it earlier.

Cheers and tnx for your help
Thomas
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
you're welcome, Thomas. The good news is that you see it now -- and will be able to fix it :)

if you execute SQL to update using code, do NOT use dbFailOnError so the records that are ok will be updated, and records that are not will just be skipped. If you do it manually and Confirm* is true for queries, you should see how many records meet criteria [and how many records could not be changed].  You should write down this number [these numbers].

* File, Options, Client Settings - check box for: Record changes, Document deletions (no effect here but good to check), Action queries

while you are modifying structure, perhaps add tracking fields for when a record was added (dtmAdd) and when it was changed (dtmEdit) -- for both: DefaultValue =Now() -- and when you run update queries, also set dtmEdit =Now() so you can see what was just changed (normally I use this for storing when a record is changed by users via a form or view).  btw, 'dtm' is my notation for date/time; use 'dt' if only date is important (so Time component can be stripped for comparing).
Vitor MontalvãoIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Thomas, is this issue solved?
If affirmative please close this question. Otherwise let us know the current issue so we can try to help you out.
Cheers
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.