access to sql & relationship diagram

[DevAccess]

Hello

I have upsize backend access db to sql server, now and then again linked back the tables as connection string to the front end access db

everything works ok and tables are now being linked very well.

Now question is that when I had access db as backend and had them linked in access db I used to have relationship diagram with them post upsizing and linking them back to front db had caused to lost of relationship between tables.

Is there anyway I can have them back ? or again I have to create in sql will automatically replicate to front end access db ?

Please advise

Thanks
Man

 

[bastanu]

The relationships have to be (re)created in the SQL server. You don't need them in the front end, the back-end is where they belong and where they serve their purpose.

Cheers,
Vlad

 

[DevAccess]

The relationships have to be (re)created in the SQL server. You don't need them in the front end, the back-end is where they belong and where they serve their purpose.

Cheers,
Vlad

I have used SQL import export tool to upsize which has resulted to lost of referential integrity. Is there anyway I can have them them retain while using SQL export import tool ? I used identity enabled while doing this process

I am using enterprise SQL server 2014.

 

[bastanu]

Not really, you need to recreate it using constraints in SQL server:
https://social.msdn.microsoft.com/F...orrect-sequence-?forum=sqlintegrationservices

 

[DevAccess]

Not really, you need to recreate it using constraints in SQL server:
https://social.msdn.microsoft.com/F...orrect-sequence-?forum=sqlintegrationservices

You mean if I imported 120 tables and each of them have referential integrity then I need to recreate them manually post import to sql ?

 

[bastanu]

I believe so

 

[DevAccess]

I believe so

I dont believe so.

 

[isladogs]

Having tested this the other day I can confirm that
1. The SQL Server Import and Export Wizard incudes an option to set identity insert but even with this checked, primary key field details are not transferred. This is using version 2014
Yet I'm sure I've done this without problem in the past.

2. Using the upsizing wizard in A2010 does successfully transfer the PK field details. Unfortunately, that feature was discontinued in A2013

 

[bastanu]

Here is some relevant info:
https://support.office.com/en-us/ar...g-wizard-5d74c0df-c8cd-4867-8d07-e6e759d72924

You can try the DRI mentioned there but I had no luck with that.

 

[DevAccess]

Having tested this the other day I can confirm that
1. The SQL Server Import and Export Wizard incudes an option to set identity insert but even with this checked, primary key field details are not transferred. This is using version 2014
Yet I'm sure I've done this without problem in the past.

2. Using the upsizing wizard in A2010 does successfully transfer the PK field details. Unfortunately, that feature was discontinued in A2013

Thanks does this also for foreign key as well ? or any other constrain as well ?

 

[isladogs]

Thanks does this also for foreign key as well ? or any other constrain as well ?

AFAIK everything is upsized except for unsupported data types such as attachment and multivalue fields. However I’m away from my pc so can’t check for a few hours. Let me know if you find a solution from the sql server end.

 

[DevAccess]

AFAIK everything is upsized except for unsupported data types such as attachment and multivalue fields. However I’m away from my pc so can’t check for a few hours. Let me know if you find a solution from the sql server end.

The uprising from access 2010 to SQL seems working (can see foreign keys and all in SQL )but I don't see relationship diagram when link back to access front end db.

 

[isladogs]

The uprising from access 2010 to SQL seems working (can see foreign keys and all in SQL )but I don't see relationship diagram when link back to access front end db.

In my experience, you won't see the BE relationships in Access with any SQL Server BE DB no matter how its created
Have you checked whether the relationships & referential integrity were both transferred to SQL during upsizing?

 

[Pat Hartman]

In all cases, regardless of what tool I actually used, all relationships were also upsized.

The relationship diagram itself isn't transferred but all the relationships are. To get a new diagram in SQL Server, just open the diagramming tool - depending on how secure your SQL System is, you may not actually have permission to create a diagram so you might need to get your permissions changed to proceed. Once you can get to the diagramer, add all the tables. The relationships will be displayed. You can use the formatting tools to have SQL Server give you an initial layout. It may be strange but the tool makes every effort to avoid crossed lines. Once the layout is built, you can move stuff around however you want. You can turn on the pagination tool so you can see how the diagram will be printed. It will take some tweaking but ultimately, the tool is easier to control than the poor tool that Access used. One nice thing about this diagramming tool is that you can create several diagrams which might help you to better organize the schema for printing.

 

[DevAccess]

In all cases, regardless of what tool I actually used, all relationships were also upsized.

The relationship diagram itself isn't transferred but all the relationships are. To get a new diagram in SQL Server, just open the diagramming tool - depending on how secure your SQL System is, you may not actually have permission to create a diagram so you might need to get your permissions changed to proceed. Once you can get to the diagramer, add all the tables. The relationships will be displayed. You can use the formatting tools to have SQL Server give you an initial layout. It may be strange but the tool makes every effort to avoid crossed lines. Once the layout is built, you can move stuff around however you want. You can turn on the pagination tool so you can see how the diagram will be printed. It will take some tweaking but ultimately, the tool is easier to control than the poor tool that Access used. One nice thing about this diagramming tool is that you can create several diagrams which might help you to better organize the schema for printing.

Actually it does, but fails at lots of instance which shows by end of upsize report in ms access, there are lots of foreign key validation it fails because of size issues and all which works perfectly in ms access.

any idea?

 

[Pat Hartman]

It can't fix your coding problems. You must fix them. Access is much more forgiving of sloppy coding than SQL Server is. Access is a tool that is targeted at novices so MS allows lots of stuff I object to. SQL Server is a professional tool. It is not forgiving. It is the SQL Server way or the highway.