Best efficient desgin - MS Access FE and MS SQL BE

[isladogs]

I would like to add that my biggest table has 16 mln of rows and has 2 GB space on disc....

Jacek

Well I can't beat that but ...

I also use Access FE & SQL Server BE for several production databases.
The largest of these has a SQL file that after 10 years has reached about 1.6GB.
That's despite it having over 300 tables including one with 1.5 million records.
Mind you I do 'shrink' the datafile in SQL Server at routine intervals - similar to C&R in Access

Although I've been using SQL Server for over 10 years, I'm certainly no expert.
I'd say I just know enough to get by ...

I also use DSN less connections as I find this much easier to understand / work with

When I upsized to SQL server, I lost all table relationships - probably because I did it wrongly.

I have RI set up in SQL server but I do so using PK/FK keys. For example:



I thought triggers were just used to trigger events!

Other points:
1. Changing to SQL Server didn't make connections noticeably faster.
However it did make them MUCH more reliable.
Network drop outs became almost non-existent which was one of the main reasons for upsizing

2. Although I have RI set up in SQL Server, the table links aren't shown in Access. I didn't notice this for some time after upsizing & never bothered to re-create them (there are over 300 tables as I said...). This is a summary of the database statistics ...





3. Shared procedures in SQL Server can be used to do very clever things ... but I've only scratched the surface of their power

4. Make sure you use indexes on frequently searched fields to speed up working with SQL tables

5. Wherever possible, use SQL views rather than Access queries so that the processing is done at the server. This should dramatically speed up many tasks - especially when delaing with large tables

6. Making changes to the structure of a SQL BE file can easily be done using a script file.
I can supply my clients with the script files over the internet or by email for them to implement
Much easier than making equivalent changes in an Access BE

HTH

[jaryszek]

Hello ridders52,

thank you very much for your help!
I have couple of questions here:

Why to set up RI in SQL Server?
In Access FE when you do not have any RI you should set up here them.
My queries and forms often based on relationships and if access doesnt see it, i can:
1) set up them in Access FE
2) set up data sources directly in FE.

So using RI is in order to creating cascading changes within your database?
What about relationships in Access ? Are they still necessary?

Jacek

[isladogs]

In answer to some of your questions - I didn't understand one of them

Why to set up RI in SQL Server? That's where the tables are so it's the obvious place
If I choose the delete a record in SQL Server, RI kicks in at source
In Access FE when you do not have any RI you should set up here them. Yes you could do so in the FE instead or get the same outcome using code

My queries and forms often based on relationships and if access doesnt see it, i can:
1) set up them in Access FE Yes you can
2) set up data sources directly in FE. ???

So using RI is in order to creating cascading changes within your database? Yes - same concept as doing so in Access

What about relationships in Access ? Are they still necessary?
Opinions differ on that
I used to think they were essential ....but having 'lost' them by mistake after upsizing, I've done without for several years without apparent issues
If you create relationships in the BE these will be picked up in the FE & applied by default in the query designer (though they can be modified in that)
If you don't create relationships in the BE, you can still do so in the FE or the query designer
If done in the BE & the FE, the BE relationship will over-ride that in the FE next time you load the db

HTH

[jaryszek]

Thank you ridders52!

I understand all despite of this sentence:

If you create relationships in the BE these will be picked up in the FE & applied by default in the query designer (though they can be modified in that)

So if you have RI in MS SQL Server BE you will be seeing relationships in Access FE?

Best Wishes,
Jacek

[jaryszek]

One adittional note here.

When i created relationships on my FE in Access with linked tables to MS SQL Server there is no possibility to mark "enforce referencial integrity"
Why is that?

Jacek

[Minty]

Because Access can't enforce it for non Access tables.
SQL can be made to do it.

[jaryszek]

Aaa ok.

thank you Minty.

Via SQL you can add referential integrity on Access FE?
Do you have any examples ?

Best,
Jacek

[isladogs]

One adittional note here.

When i created relationships on my FE in Access with linked tables to MS SQL Server there is no possibility to mark "enforce referencial integrity"
Why is that?

Jacek

As Minty has already said, you can't. That's why I do RI in SQL Server as already explained.

If you create relationships in the BE these will be picked up in the FE & applied by default in the query designer (though they can be modified in that)

You can setup relationship diagrams in SQL Server as in the example below



However, in my experience these relationships are not shown in the Access FE relationships window.
By contrast, relationships from Access BE are carried across to the Access FE

HTH

[jaryszek]

Thank you Ridders52!

I will set up RI in SQL Server so. I think it is the best approach.

relationships from Access BE are carried across to the Access FE 

Yes, but when i have password on BE I can not see RI in FE on split database.

Best Wishes,
Jacek