Access 2007: Problems Importing Data Into a Split Database (.mdb format)

Question

Researched several of the questions which seemed to be close to my problem, but still no luck.  My problem is that I have a working database (mdb format) in Access 2007 and I want to split the database frontend-backend, but I have having problems with importing the same data into the exact tables as it should.  The error is:

"You cannot record your changes because a value you entered violates the settings defined for this table or list ... Correct the error and try again."

1)  My first attempt at splitting this database, I used the split wizard, and got the error when trying to import a text file using the same import specs as before the split.

2)  My second time I tried doing it manually, with the same results.

I am getting flustered with this, does anyone have a working solution?  Thank you in advance.

---

MJ_TNMC

Answer 1

Hello MJ

When I split a database I do it in a slightly unconventional manner.

1.Create a copy of the complete database  2. Strip all the tables and queries from one and all the forms etc from the other.  3. From the one with the forms left in it use the import data function (Access) and search for the copy with the data in it.  4. Create linked tables from the (now) backend to the (now) frontend.  You have recreated the original database but all the tables are in a backend.

I have never had any trouble doing it this way as the databases are, by definition, identical to each other.

Hope this helps

Geoff

Answer 2

Thanks for your response.

Let me re-phase my earlier comment about "having problems with importing the same data into the exact tables as it should..." 

I meant to say that what works when imported into a single mdb file (tables, forms, etc.) FAILS with the above error when the tables are split out to a separate "backend" mdb file.

With regards to your question about same data in front end and back end tables, no.  My intent was a true split, tables in backend and the rest (forms, queries, etc.) in front end.

I have a new requirement to report on two different facilities which share the some of the same input data source files.  I was trying to split the database so that I could load the input files once, filter them down into respective source tables, and then perform the respective processing on those adjusted source tables using the same existing process code but isolating by facility.

---

MJ_TNMC

Answer 3

Hi,

Are you trying to split a database that have linked tables? like a table that is link to a excel spreadsheet?

:)

---

I hope this helps, Bre-x just a regular user, not an expert

Answer 4

 but I have having problems with importing the same data into the exact tables as it should. 

That error message says the data is there now.  Did you try running an unmatch and match queries? 

Are you trying to have the same data in front end and back end tables?

---

Build a little, test a little.