Help with relationships beetween FE and BE

[jaryszek]

Hi Experts,



i have huge problem and a little time to fine the solution.

I am trying to recreate split FE-BE split database on Access on FE Access and BE MS SQL Server.

In MS SQL Server i recreated RI from Access BE. And seems to be working. On FE on Access I can not see relationships but it is normal.

I will explain my problem on examples.
In attachment you can find 2 examples of my databases:
Working:
Simple split databases created on Access.


I have created here relationships, one query (NowaOby) and one form.


When you check form tbl_Korekty you can see that i can possibility to write new record within my NowaOby query.

And it is fine here. I need have this possibility in order to have working database model.

Not Working:
Instead of linking to MS SQL Server i want to demonstrate what is not working on linked Excel files

I created relationships (the same as i can create when MS SQL Server tables are linked):



Within my NowaOby query there is no possibility to add new records:




Of course here is an Excel linked tables and i can not add new records. But what if I have MS SQL Server. It should be possible somehow.

[jaryszek]

Relationships for working database look like:



Please help with topic,

How to recreate the behaviour for FE-BE Access into FE Access - BE MS SQL Server?

thank you in advance,
Best Wishes,
Jacek

[isladogs]

Using linked Excel files was a bad analogy - as you already know those cannot be updated in Access.
This is due to a copyright issue dating back about 25 years!

However, as long the SQL tables are updatable in SQL Server, they will also be updatable as linked tables in Access

The relationships aren't really relevant in this context

[Gicu]

Do your SQL tables have primary keys? If you open the tables in Access in design view do you see the same primary keys like in the Access version?

Cheers,
Vlad

[jaryszek]

Hi Guys!

Thank you very much!

ridders52,

This is due to a copyright issue dating back about 25 years!

wow microsoft is strange

However, as long the SQL tables are updatable in SQL Server, they will also be updatable as linked tables in Access

How can i recreate my query from sample database from Access in MS SQL Server? Create View and try to insert values into it?

The relationships aren't really relevant in this context

Question, what is very important here?

Vlad,

Do your SQL tables have primary keys? If you open the tables in Access in design view do you see the same primary keys like in the Access version?

Yes Vlad, the primary keys are the same. I will create new sample database in MS SQL Server and publish it here in order to replicate the bahavior.

You can also replicate the bahavior from here by your own. All what you have to do is create 2 tables in MS SQL Server: tbl_Daty and tbl_Korekty and link it with SQL Server.
Fields you can find in Excel files.

Thank you once again,
I really appreciate any help here,
Jacek

[isladogs]

wow microsoft is strange

True but in this case they were the subject of a lawsuit - its probably expired by now but the functionality has never been restored

Question, what is very important here?

The tables must be updatable in SQL Server - so they MUST have a PK field.
This also applies to SQL views in Access - IF they have a PK they can be updated in Access

I've looked at the Access database you supplied and added data to both tables then edited it.
Editing tbl_Daty depends on 2 required fields being present in tbl_Koretky
As long as tbl_Koretky is completed first then tbl_Daty there is no problem
I've not tried to do so in SQL Server but as long as the sequence is the same, it should be fine

[Gicu]

Jacek,

Can you update the tables in SQL server? Is the SQL user account used in your connection the db_owner or at least db_datawriter?
https://docs.microsoft.com/en-us/sql...se-level-roles

Cheers,
Vlad

[isladogs]

Jacek

Have a look at this link https://accessexperts.com/starthere/
It has loads of articles about using Access with SQL Server by Access MVP Juan Soto

[jaryszek]

Thank You Guys,

ridders52,
you have right that first you should fullfill tbl_Korekty and next tbl_Daty.

But when you are working on Access only your query is editable and you can input data ! You have cursor and can input data buty there will be warning about necessary fields.
Working on MS SQL Server has no possibility to do that.

This is strange! Very! So I have to rebuild my model and i think something is not ok with this.
It should work exactly in the same way as in Access.

Vlad,
yes i cna update tables and have specific access to ms sql server.

[jaryszek]

I think Guys that here is problem connected with seeing tables by Access.

When Access is based on Access split database there is no problem.
But when tables are linked to MS SQL Server there is very slighty difference.

I had to find workaround and implement it into my database.
Added one additional field done the job.

I added tbl_Daty primary key field into NowaOby query and Korekty_ID field and it is working now.
I think that Access has to see exactly what primary key is in specific table when you are linking tables from ms sql server.

Best Wishes,
Jacek

[Minty]

When you link a SQL table in Access , if Access can't determine a unique key field, it will prompt you to set one or a combination of fields to make a unique reference to the records.
Without this you won't be able to edit the records. This is intended behaviour.

[jaryszek]

Hi Minty,

thank you.
Here i Have Primary keys moved from MS SQL Server to FE Access.
But still Access has problem identify some of them within queries.

Jacek

[Minty]

If you delete the link to the SQL table and recreate it, ensuring the Primary key is identified that problem should disappear.
Sometimes the upsizing wizards don't necessarily do this correctly.

[jaryszek]

Thank you Minty,

problem is still there after recreating linked tables.

Jacek

[Minty]

That's a bit odd. Do you have the correct permissions set on the tables in SQL ?