Relation in multiple backend

[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...

 

[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.

 

[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.

 

[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.

 

[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 ?

 

[amir0914]

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.

Great !!
What about the next question : "SSMA_TimeStamp",
Is the field really needed ?