value you entered violates the settings

Tacti_Steve

New Member
Joined
Feb 15, 2019
Messages
6
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top