Link to home
Start Free TrialLog in
Avatar of Thomas Poett
Thomas Poett

asked on

Access Web App Relationship with Table

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
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

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

ASKER

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
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.
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
I've added MS Access topic. It might help if some Access Expert have knowledge with Access Web App.
SOLUTION
Avatar of crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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).
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