Import table error

[Rajtomar]

I am trying to split a replicated database by manually copying the
objects. but i am facing a problem with importing one table named
"CANDIDATES". When i select -import all, all tables gets imported
except the "CANDIDATES" table giving an error that it is not valid
datbase file.

After that i tried copying the table structure which happened.

Now "CANDIDATES" table needs to have a referential integirty
relaionship with "BOOKING" table through field name -
"CID" (autonumber) which is not happening even if i am mnually pasting
all the records in to the table. It says the "BOOKING" table have some
field whose reference is not there in the parent "CANDIDATES" table.
How this is happening when i m manualy pasting all the records.

May be the problem is because both the "CANDIDATES" table and the
"BOOKING" table has an auto number field and a date time stamp field.

Any ideas please..

 

[Clifford Bass]

Hi Rajtomar,

First, make sure you have a backup. Then, since you are trying to
split it, how about making a couple copies of the whole database (using
Windows Explorer). Name one something like "The Database-Data.mdb". Open it
up and delete out all but the tables. Compact and repair. Name the other
something like "The Database-Main.mdb". Open it up and delete out all of the
tables. Then create links to all of the tables in "The Database-Data.mdb".
Compact and repair. There is also the database splitting wizard that you
could try. I have not used it, so cannot comment on it.

Clifford Bass

 

[Rajtomar]

Hi Rajtomar,

     First, make sure you have a backup.  Then, since you are trying to
split it, how about making a couple copies of the whole database (using
Windows Explorer).  Name one something like "The Database-Data.mdb".  Open it
up and delete out all but the tables.  Compact and repair.  Name the other
something like "The Database-Main.mdb".  Open it up and delete out all of the
tables.  Then create links to all of the tables in "The Database-Data.mdb".  
Compact and repair.  There is also the database splitting wizard that you
could try.  I have not used it, so cannot comment on it.

             Clifford Bass









- Show quoted text -

Since this is a replicated database which cant be split by a wizard.
Moreover a replicated database contains hidden tables which stores
changes in the database(forms, report etc.) So i cant follow the
procedure as u explained as it will retain those hidden tables. So the
only method to be used is to import all but table in a new mdb file
and in another mdb file import only the visible tables (by
individually selcting each table. as select all option will also
select hidden tables). Then link tables. But in doing this i am facing
the error as explained in my first message.

 

[Clifford Bass]

Hi,

Not having worked with replicated databases, I was not aware of the
added issues. Here is a thought that may help. In the new database, create
a temporary copy of the CANDIDATES table structure and then change the
autonumber field to a long integer in the copy. Do your copying of the
CANDIDATES table into the temporary table. Then run an append query to
append the contents of the temporary table into the CANDIDATES table. It
should retain the current values and reset the next number to be the number
following the highest imported value. Now the values in the BOOKING table
should work. If you need the retain the numberings in the BOOKING table, use
the same process.

Let me know if that helps.

Clifford Bass

 

[Rajtomar]

Hi,

     Not having worked with replicated databases, I was not aware of the
added issues.  Here is a thought that may help.  In the new database,create
a temporary copy of the CANDIDATES table structure and then change the
autonumber field to a long integer in the copy.  Do your copying of the
CANDIDATES table into the temporary table.  Then run an append query to
append the contents of the temporary table into the CANDIDATES table.  It
should retain the current values and reset the next number to be the number
following the highest imported value.  Now the values in the BOOKING table
should work.  If you need the retain the numberings in the BOOKING table, use
the same process.

     Let me know if that helps.

              Clifford Bass





- Show quoted text -

No this thing doesn't work because once i set autonumber field to long
integer then afterwards i cant change it back to autonumber which is
required to be set auto number as it is related (one to many, with
referential integrity) with a field in BOOKING table.

The Main thing is that why i am not able to import the CANDIDATES
table correctly. whenever i import that table the source db gets
corrupted. Also whenever i tried compacting the source databases
corrupts. (one relationship gets deleted)

 

[Clifford Bass]

Hi Rajtomar,

You misunderstood me. The issue of autonumber to long interger back to
autonumber is indeed a block. That is why I said to import the data into a
TEMPORARY table that does NOT use autonumbering. THEN, use an append query
from the temporary table into the empty CANDIDATES table which DOES have
autonumbering. Doing it this way will retain the numbering of the original
table. Please try it.

Clifford Bass

 

[Rajtomar]

Hi Rajtomar,

     You misunderstood me.  The issue of autonumber to long interger back to
autonumber is indeed a block.  That is why I said to import the data into a
TEMPORARY table that does NOT use autonumbering.  THEN, use an append query
from the temporary table into the empty CANDIDATES table which DOES have
autonumbering.  Doing it this way will retain the numbering of the original
table.  Please try it.

                      Clifford Bass







- Show quoted text -

HI Clifford Bass,

It really worked well. Thanks a lot for giving it a thorough
understanding and replying precisely.

Anyways i would also like to tell you about the error which was
causing it. In the CANDIATES table there was a row where in every
field "#Error" was there and i was not able to delete or edit that
row. I even tried making a separate form for just that table. Whenever
i navigated to that record it gave a error unrecognised database. So i
had to manually copy all the rows in CANDIATE table except that row to
the temporary table as you suggested and finally appended all data to
the new table with autonumber field. It just worked. Now if you could
also throw some light on such error handling if one happens again in
future. That how to delete that #Error row. other than the way i did.