value you entered violates the settings

[Tacti_Steve]

Hello,

New poster here so I apologize if this specific question has been asked. I've searched all over this forum and Google and cannot find the solution. I have two databases where the only difference is which computer they're on. The db was built on comp1 and then copied to comp2. All linked tables were moved to onedrive and both db's reference the same files from onedrive.

The db is meant to pull inventory updates from various vendors download the files, find certain products, do some things and then create an output for our system to upload.

Everything works fine on comp1. Always has. On comp2 it regularly throws an error "you cannot record changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximum). correct the error and try again". If we close the db and reopen it (macro's run on open) it will eventually run without any errors. And the output is all correct.

It seems to be throwing this error on the first query it runs. This query looks at our inventory levels and imports them into a table in access. Again both db's reference the same source files. Everything between the two db's is identical. Please help this is very annoying.

 

[Joe4]

Welcome to the Board!

Is this a properly split Access database? When multiple users are using the same database, it should be "split", and each user is given their own copy of the front-end to use.
"Splitting" works better than copying it, especially if there are any native data tables in them.
See: https://support.office.com/en-us/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc

Also, do both users have the same version of Access? If not, what version does each have?

 

[Tacti_Steve]

Welcome to the Board!

Is this a properly split Access database? When multiple users are using the same database, it should be "split", and each user is given their own copy of the front-end to use.
"Splitting" works better than copying it, especially if there are any native data tables in them.
See: https://support.office.com/en-us/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc

Also, do both users have the same version of Access? If not, what version does each have?

It was not split. I will look into that. It was just copied. They are never used at the same time. Basically comp1 is a desktop and comp2 is a laptop that is used while traveling. They both have access 2013. I will look into the splitting of it thank you.

 

[Tacti_Steve]

It was not split. I will look into that. It was just copied. They are never used at the same time. Basically comp1 is a desktop and comp2 is a laptop that is used while traveling. They both have access 2013. I will look into the splitting of it thank you.

I split the database but the new copy does not have any of the linked tables, queries, macros or modules. Do I now have to copy everything from one to the other? How is that going to solve this problem?

 

[Joe4]

No, you should not need to copy anything over. If you split the datbase properly, this should already be done. A properly split database witll have two databases this result:
- Back-end database: this contains all the data tables
- Front-end database: this contains links to all the data tables, and has all other objects (Queries, Forms, Reports, Macros, Modules)

Did you use the Database Splitter functionality, or try to do it yourself manually?

 

[Tacti_Steve]

No, you should not need to copy anything over. If you split the datbase properly, this should already be done. A properly split database witll have two databases this result:
- Back-end database: this contains all the data tables
- Front-end database: this contains links to all the data tables, and has all other objects (Queries, Forms, Reports, Macros, Modules)

Did you use the Database Splitter functionality, or try to do it yourself manually?

I used the splitting wizard.

 

[Joe4]

Did you confirm that the back-end only has tables, and then the front-end has everything else, and links to those tables?
If you try using a copy of the front-end on the computer that was having issues, does it still have issues?

 

[Tacti_Steve]

Did you confirm that the back-end only has tables, and then the front-end has everything else, and links to those tables?
If you try using a copy of the front-end on the computer that was having issues, does it still have issues?

Yes the be only has tables. The original one I split it from looks unchanged and still gives the error.

 

[xenou]

I don't like to raise alarms unnecessarily but one drive (or in general any remote file systems) is not generally for MSAccess. Access is a file based system so there is no safety provided for lost packets or bad connections. It's a little complicated topic - for instance, certain VPNs and remote desktop apps are fine (they don't actually send data across the wire, just connect you to the machine where the data is stored).

You might want to decompose this problem so instead of running everything automatically when you open the database, you open the database then run the macro (preferably in steps, with good error checking for each step). The error message itself sounds like a custom error message so you could look into precisely what is happening when this error occurs.

But overall, you might also want to search for a more robust way of handling for remote operations.

I don't have any experience with MSAccess and one drive which is why I hesitate to weigh in, but it sounds a little iffy to me. Some similar advice:
https://answers.microsoft.com/en-us...onedrive/880e752f-9d66-41d9-838e-d80e45ba5a17
https://stackoverflow.com/questions/35562666/how-can-we-share-a-backend-ms-access-2016-on-one-drive

 

[Joe4]

I have to defer to xenou on this, as I do not use OneDrive at all. But I have heard of some issues with it and Access also, so I would wager to say that the problem probably has something to do with that.