Fails to Inserts two columns but no error given

[Kalyan Ganesan]

There are two tables in MS Access that are identical,they are linked tables,but the structure is same even though their database is different,so i tried

INSERT INTO TableA
SELECT *
FROM TableB

but it inserts three columns and two columns data is not inserted and left blank,but it doesnt give any error so what should i do to make sure all the 5 columns are inserted

Thanks

 

[dhookom]

Are there any lookup fields? Are there any constraints with primary/foreign keys?

I never create a statement like this without specifying the field names.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Kalyan Ganesan]

Just because i didnt give the field names shouldn't stop it from inserting those rows into the table right?there are indexes but the point is it didnt enter a single row for those two columns,they are name columns by the way,like Last name and first name,they are completely blank

 

[dhookom]

It doesn't make sense to me that it doesn't work but you haven't provided actual fields or data for us to help troubleshoot. TableA and TableB don't tell us much.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[SkipVought]

@Kalyan,

Do you want a working solution, or do you want to be justified in your approch?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kalyan Ganesan]

Ok you guys dont know the database i use i dont understand what difference would it make to give the field names,.anyway here is the exact query as it is,this time i tried with the exact field names

INSERT INTO Reciever ( Reciever_Sys_Id, Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active )
SELECT Reciever_Sys_Id, Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active
FROM Recievers_Prod
WHERE Reciever_Lname is not null;

Also there is only one trigger,which uses a sequence to insert values into the Reciever_Sys_Id,like a auto increment on Reciever_Sys_Id..so i am still very frustrated as to why it would't enter the two columns Reciever_Lname and Reciever_Fname

 

[dhookom]

So are these linked from Access or a SQL Server or other? If the Reciever_Sys_Id is auto increment, why are you attempting to force a value into it?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Kalyan Ganesan]

Well i tried without forcing value into Reciever_Sys_Id this time i just thought id give all the column names(as per the suggestion of someone here) ..
One table is a Development table that is linked to Oracle and another(the source table)is a production table also linked...
If it can enter the values of three columns why not those two?linked tables shouldnt cause it right?

Thanks anyways for your effort.i really need to figure this out ..

 

[Andrzejek]

Try skipping Reciever_Sys_Id field in 2 places in your statement.

Since "one trigger,which uses a sequence to insert values into the Reciever_Sys_Id,like a auto increment on Reciever_Sys_Id"

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Kalyan Ganesan]

You mean write the query like this?

INSERT INTO Reciever ( Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active )
SELECT Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active
FROM Recievers_Prod
WHERE Reciever_Lname is not null;

 

[Kalyan Ganesan]

Tried this and still no luck

INSERT INTO Reciever ( Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active )
SELECT Reciever_ID, Reciever_LName, Reciever_Fname, Reciever_Active
FROM Recievers_Prod
WHERE Reciever_Lname is not null;

327 Rows added but not the Lname and Fname

 

[SkipVought]

Try skipping Reciever_Sys_Id

INSERT INTO Reciever ( Reciever_LName, Reciever_Fname, Reciever_Active )
SELECT Reciever_LName, Reciever_Fname, Reciever_Active
FROM Recievers_Prod
WHERE Reciever_Lname is not null;

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kalyan Ganesan]

But i need Reciever_ID, Reciever_Sys_ID is the autoincrement but not Reciever_ID..i cant skip that

 

[Andrzejek]

Weird...

How about:[tt]
... SELECT Reciever_ID, [blue]'Smith', 'John',[/blue] Reciever_Active
...[/tt]

Just hard-code the name and see if that will fly.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Kalyan Ganesan]

Tried SELECT Reciever_ID, 'Smith', 'John', Reciever_Active

still not inserting...this is ridiculous, i have never encountered anything like this..

I even tried copying the column from the source and highlighting and pasting in the table but it keeps giving the write conflict error

 

[Andrzejek]

So the only thing left is to compare how the fields [tt]Reciever_LName[/tt] and [tt]Reciever_Fname[/tt] are declared in both tables. And they cannot be declared the same, there must be a difference.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[Kalyan Ganesan]

They both are text fields..thats something i compared when the first time the inserts failed..

 

[Kalyan Ganesan]

Ok i tried manually entering data into the Name fields and its giving the Write Conflict error,

It says This Record has been changed by another user since you started editing it.If you save the record ,you will overwrite the changes the other user made

 

[SkipVought]

You have an existing Reciever_ID

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Kalyan Ganesan]

What do you mean?the table is empty when i try to do the insert?