Relation in multiple backend (1 Viewer)

[amir0914]

Hello everyone,

For limitaion of access database which is 2 Gb, I splited backed database to two backend, but relationships between them has gone and Cascade option are disables.
I also know I can use SQLServer databse instead of Access database, but for some reasons I have to perform it by Access DB.

What's your idea ? Actually Cascade Delete and Cascade Update are very important in my program.

 

[theDBguy]

Referential integrity can only be imposed on tables in the same container. Otherwise, this option will not be available. If you must have RI enabled, then make sure the tables involved are stored in the same database.

 

[amir0914]

Referential integrity can only be imposed on tables in the same container. Otherwise, this option will not be available. If you must have RI enabled, then make sure the tables involved are stored in the same database.

Ok, you're right, RI can be done only in one database.
But I have to use tow backend databases and the child must be updated when parent table (Customer) is edited.
What should I do ?

 

[theDBguy]

Ok, you're right, RI can be done only in one database.
But I have to use tow backend databases and the child must be updated when parent table (Customer) is edited.
What should I do ?

As I tried to say earlier, keep the related tables within the same container/file. Surely, not all your tables are related to each other, are they? If table1 and table2 are related, keep them in DB1. If table3 and table4 are related, keep them in DB2. Something like that...

 

[Pat Hartman]

In a relational database, everything is probably related to something so having more than one BE will be a problem at some level.

If your tables are so large that you need to split the BE, you really need to move to SQL Server, sooner rather than later.

Failing that, you need to code the RI yourself and you'd better get it right or you will have orphaned data.

Although I use cascade delete, when you do not have that option, the safest method is to not allow deletes at all. And this is what I would do in your situation. Add a field to your parent table. I happen to prefer to use dates rather than flags because they provide additional information so call it DeleteDT. When the user asks to delete the record, use Now() to populate DeleteDT. Then, you need to change queries to ignore any record where DeleteDT is not null. You also need an option on your edit form to show deleted items so you can manage them if you need to.

If you have to code cascade delete yourself, you need to do it inside a transaction because you want ALL deletes to happen or NONE. I don't have any code but you should be able to find samples or perhaps someone has something they can code.

The psuedo code is:

Start Transaction
.. using a query that joins the parent record to the child, delete the child records where the parent = the PK that the user selected.
.. Delete the parent record = PK the user selected
End Transaction

If you have more than two levels, you need a query for each level and you need to run them bottom up. You need to include the entire path but delete from the bottom table in the path. So, four levels 1-2-3-4 == delete from 4, three levels 1-2-3 == delete from 3, two levels 1-2 == delete from 2. The final delete is from level 1.

 

[amir0914]

As I tried to say earlier, keep the related tables within the same container/file. Surely, not all your tables are related to each other, are they? If table1 and table2 are related, keep them in DB1. If table3 and table4 are related, keep them in DB2. Something like that...

All tables are related together, 1 to 2, 2 to 3-4-5, 4 to 6-7.

 

[theDBguy]

All tables are related together, 1 to 2, 2 to 3-4-5, 4 to 6-7.

And they all need RI enforced? 1 RI to 2, 2 RI to 3, 3 RI to 4, and so on?

If so, then I guess, if you can't decide which tables to split up, you're stuck.

 

[Pat Hartman]

The problem is not that one table is related to all tables, it is that all tables are related to at least one other table so it is impossible to break the chain. I almost never have orphan tables either. Every once in a while you get stuff like the "tally" table that just contains records with one numeric field that you use to create duplicate labels or reports but otherwise, no table in a relational database is an island unto itself.

 

[The_Doc_Man]

I'm going to agree with Pat. When you reach the point that you have to split the DB back end into two or more parts, you are reaching a point where a back-end upgrade is in order. I used secondary back-ends only for archiving of event logs that were not directly linked to anything else and so COULD be archived and the primary areas then would be erased. If you have live (and current) data that is so huge that you are blowing out the 2GB limit, it is time for SQL Server or MySQL or SOMETHING that has more room.

 

[amir0914]

Thank you all for your advice. I agree with Pat, mostly there is not any orphan table in a database. all tables are related to at least on table in my database and not to all other tables. Given that there is not any data in the database so as The-doc-man mentioned I should move the database to SQLServer although I don't know if it's possible to convert the whole of databse to SQL databse. I think I have to start creating all the database in SQLServer from scratch.

 

[Pat Hartman]

Converting the BE to SQL server is the easy part. The hard part will be making sure that the FE doesn't slow to a crawl.

You can take an hour to try it out. Convert the BE to SQL Server. Then just relink the FE to the server tables. I've attached a db that might be helpful. The Access upsizing wizard used to do this for you but the wizard has been deprecated and so you need to use the SSMA tool to do the conversion. When you link to the SQL Server tables, they link as dbo_yourtablename. You can rename them manually, or you can use the code in the sample I attached to rename them.

Then just try it. Most things will just work fine. You will have to change some of your DAO code to include additional argumenss (the changed code will still work in Access) and then you have to do something about forms that are bound directly to tables or to queries with no criteria so that you dramatically reduce the number of rows brought down from the server. When you are working with Jet/ACE, Access works fine if you are using form filters to reduce the data the user sees but SQL Server will be a dog so, you will need to add criteria to all your RecordSources and use the criteria to filter before the data is retrieved rather than local filters after the data is retrieved.

 

[JackWhite]

Thank you very much for this thread, I found answers to my questions, it was very helpful.

 

[GPGeorge]

Thank you very much for this thread, I found answers to my questions, it was very helpful.

You install the free version of SQL Server, which is called SQL Server Express, and give Pat's suggestion a try.

 

[amir0914]

Converting the BE to SQL server is the easy part. The hard part will be making sure that the FE doesn't slow to a crawl.

You can take an hour to try it out. Convert the BE to SQL Server. Then just relink the FE to the server tables. I've attached a db that might be helpful. The Access upsizing wizard used to do this for you but the wizard has been deprecated and so you need to use the SSMA tool to do the conversion. When you link to the SQL Server tables, they link as dbo_yourtablename. You can rename them manually, or you can use the code in the sample I attached to rename them.

Then just try it. Most things will just work fine. You will have to change some of your DAO code to include additional argumenss (the changed code will still work in Access) and then you have to do something about forms that are bound directly to tables or to queries with no criteria so that you dramatically reduce the number of rows brought down from the server. When you are working with Jet/ACE, Access works fine if you are using form filters to reduce the data the user sees but SQL Server will be a dog so, you will need to add criteria to all your RecordSources and use the criteria to filter before the data is retrieved rather than local filters after the data is retrieved.

Your help is greatly appreciated,
I have a problem with converting tables to SQL, SSMA gives error for a field of the table :
"Complex Text dataset is not supported"
The field type is ComboBox (multiple allowed), I think this type of field there is not in SQL.

My next question is :
Can SSMA move also relationships ? or I have to create diagrams in SQLServer Management manually ?

 

[GPGeorge]

Your help is greatly appreciated,
I have a problem with converting tables to SQL, SSMA gives error for a field of the table :
"Complex Text dataset is not supported"
The field type is ComboBox (multiple allowed), I think this type of field there is not in SQL.

My next question is :
Can SSMA move also relationships ? or I have to create diagrams in SQLServer Management manually ?

You have, unfortunately, adopted the "Multivalue Field" datatype for one or more fields in tables. You'll have to unwind that in order to migrate the tables to SQL Server. Access and SharePoint are, to my knowledge, the only two data storage applications which permit those things. Dataverse probably does also, I'm afraid.

Once you resolve the Lookup Fields and MVFs into properly designed tables with regular foreign keys and associated look up tables, you can designate the primary and foreign keys in your tables and enforce referential integrity on those relationships. Only when that's in place will you be able to smoothly migrate the data to SQL Server. SSMA will handle all of the relationships if you do have them designated in the Access tables.

Over the years, many of us have preached against Lookup fields of any kind in Access tables, including MVFs. They are the sweet siren song of the Access world that leads many an innocent new Access user onto the rocky shores of despair.

 

[The_Doc_Man]

They are the sweet siren song of the Access world that leads many an innocent new Access user onto the rocky shores of despair.

George, we didn't realize you were such a poet!

 

[GPGeorge]

George, we didn't realize you were such a poet!

In a long ago phase of my misspent youth.

 

[Pat Hartman]

Nothing is ever easy Removing the MVF could be quite a project depending on how you used it. I was actually at the meeting in Redmond in 2006 when the Access team demo'd this feature for the first time to a small group of Access developers and the crowd went wild - not with joy - when we found out that MS had hidden the works and so this "feature" could never be used the way the experts wanted it to be used and could never be converted to SQL Server or any other RDBMS.

 

[amir0914]

Thank you all, I have tow other problems :

Short Time fields in Access is created as Short Text in SQLServer, I'm using short time (for example 15:23) in my Access program, now I want to do the same in SQLServer, How do I that ?

A new field appeared in SQLServer tables after converting that is "SSMA_TimeStamp" , what is it ? is that important or I can delete it ?

 

[Pat Hartman]

Do not confuse display format from data type. When your tables were converted to SQL Server, the incorrect data type for dates was selected. Either change them manually or redo the conversion. The other alternative is to change the ODBC driver you are using to a newer one. The problem with this is that you have to install and use this driver on ALL user computers also so it is not easy to distribute unless you have an IT staff who can do it for you. When the old ODBC driver does not recognize a data type, it sometimes treats the data as text. This doesn't work because the dates won't sort or compare correctly when they are strings.