8. (Advanced Programming In Access 2013) Creating Relationships In SQL Server

Creating Relationships In SQL Server video in the "Advanced Programming in Microsoft Access 2013" series hosted by Steve Bishop. In this free advanced video tutorial series Steve will be going over Microsoft SQL Server installation, Database Migration, creating a better User Interface, using external data sources, complex Visual Basic For Applications (VBA) concepts and distributing your application.

Click here for the full playlist of "Advanced Programming in Access 2013":

Click here for the Work Files of this series:

[jayantkumarparmar]

Dear sir, you are an very effective teacher. I was struggling to understand database but now you made it easy to understand. I like to have more resources to learn the database. I want to buy any material if you have produced to learn data management in detail. You have explained everything in simple to complex was and from creating database to client services. Thank you very much.

[nonsookafor]

Hi Steve...I wish you would post more as soon as possible. April is really a long way to go

[Awalreal]

Hi when are we expecting the next episodes of the series? can't wait for the rest of the series. The series are GREAT!

[ashishtrivedi]

Steve your are doing great and Thanks a ton for your tutorials but at this point if some newbie ask me if to go for Access or SQL, I will ask him to not try Access because its getting very painful process, I dont think microsoft ever considered users point of view while making all these..disappointed to put so many hours of work in access.

[enseig_rech]

Hi Steve. Thank you for all the videos. Let me submit you one question and one problem (I use ACCESS 2013) :
1- Question : my application with access is installed like this : backend file on a server and frontend file on all users PC. I want to use your method with SQL server and databse migration. Can you confirm me that i must install the SQL Server "Express edition" on my server not on a users PC ?
2- Problem : when i try to visualize the relationships between tables (like you on the actual video at time 20:26), the SQL Server management tell me that it is a problem with memory (the message in french is : TITLE : Microsoft SQL Server Management Studio :Tentative de lecture ou d'écriture de mémoire protégée. Cela indique souvent qu'une autre mémoire est endommagée. Can you tell me what can be the problem.
Thank you very much.

[saturninramirez]

THANK YOU VERY VERY VERY MUCH ! have you a patreon account or something to thank you by a way ?

[densiobaste]

table relationship is different from join type. they are not the same Steve. access is just making the table relationship as a pattern for joins. when we making a query in access.

[HarveyFrench]

This is a very useful introduction and very helpful.  If you have any useful links it would be great if you could share them at the end or in the youtube video notes.

It is clear that the Access 2010 upsizing wizard should be used if possible and the following links expand on the reasons you gave.  

Of particular note in the first link is how the wizard handles the following (as I think the method you present may not give the same results):  
- converting Access field names to legal SQL Server field names
- Validation rules (triggers are created),
- Default Values (er, it does something I don't understand yet.),
- the table properties "Description, Caption, Format, InputMask, and DecimalPlaces." which I think may "get lost" using the method you demonstrate.
- why it gives PK indexes a name beginning with (As Access chooses the index to use based on the alphabetical order of the index name - which I find hard to believe)

Both links are about olrder version of Access, so they may be dated, however, there doesn't seem to be more recent versions of these articles out there. The second link, in particular, gives some good info but gets pretty heavy, however, I found it useful to better understand issues that can arise.

Links:


I also read this about Connect strings, which intrigues me:
In a module, you can define a formatted connect string that specifies connection information. A connect string passes the connection information directly to the ODBC Driver Manager, and it helps simplify your application by removing the requirement that a system administrator or user first create a DSN before using the database.

Thanks for the massive and contribution you are making to my learning!

I hope this might be useful.

Harvey

[aboazoozxavi]

Hi, Steve, ,, in the beginning i want to thank you for all what you provided, , actually its a unique type of lessons
my question is : after making relationships in sql server and opening our access front database tools we will find our tables there .... do we need to do the relationship again .. thanks a lot

[nonsookafor]

hello, thanks for the videos..please is this the last video in the series? or are there more to come and when should we be expecting them... thank you

[harisrg]

In my Access Project, I never used the Relationship tool in Access to create relationships between the tables. I just had all my foreign keys named exactly the same as of the foreign table's column that the foreign key should point to. And this worked out pretty well without having to use the Access Relationship tool. Does the sql server work the same way or I would have to define the relationship explicitly?

[ivansavchenco]

Hello Steve, I am a "new guy" in database design but I have already built DB in Access with a huge functionality, difficult queries many-to-many relationships and deep data analysis for industrial needs of company I am currently working for. And each time when I build a query or code builds a dynamic query, I recheck and rebuild the relationships depending on my current-form-report-analysis needs. Sometimes the same joint may be right or left or inner depending on situation. So I suddenly realised that I just did not set all those relationships at all... Yes. Nothing. And from the technical point of view I do not understand necessity to build them at all. And the question... Maybe I am missing something here?
Many thanks in advance for answering.

[nonsookafor]

what about restoring relationship in access so that I can use my access as front end and my sql server as back end, , so how do i set the relationship in my access database as well

[nickpanko]

Steve, you might have received this question so apologize for the redundancy. SSMS does not allow you to edit Azure SQL databases. do you recommend another tool?

[markwilliams]

The Delete Rule cannot be set to Null for Privileges_ID. Is it because its data source is a list box? I'm able to change it in the Relationship window, but SQLServer won't save it.

[rathpanhasarun]

Hello Steve! If we have to create relationship in sql so that means Import data from access to sql server in your last video doesn't include relationship right?

[rathpanhasarun]

Excuse me! In the delete rule, I don't want to allow user to delete the record in case if the record is related to another record. How can I do that? In Access I just un-check Cascade Delete Related Records.

[adityait]

Hello Steve,
I am using Access 2013 and half of my team uses access 2010.
So Can I do the coding in 2013 and distribute the file to my teammates.
So does it work fine for people who are using Access 2010.

Please advise.
Thank You for all the Help!.

[boomwithpeter]

Utorrent Access 2010 and than when finished uninstall it