How to use Ms Access Append queries into Ms Sql Server
I have created Five action queries in Ms Access 2016 for inserting data that require further manipulation to give correct information , one of the INSERT QUERY see below:
INSERT INTO tblPosAccounts ( QtySold, UnitCost, CostOfSales, SoldID, POSDate, CosAcc, BSIDCos )
SELECT tblPosLineDetails.QtySold, tblPosLineDetails.UnitCost , tblPosLineDetails.CostOfSa les, tblPosLineDetails.ItemSold ID, tblPosLineDetails.POSDate, tblPosLineDetails.CosAcc, tblPosLineDetails.BSIDCos
FROM tblPosLineDetails;
The above query works very well if used against Ms Access 2016 table BUT fail to append in MS SQL Server 2016, kindly assist me on this all the links are just super no errors whatsoever. I do not understand this. The SQL Server has the same tables as MS Access.
See what you can do.
Regards
Chris
INSERT INTO tblPosAccounts ( QtySold, UnitCost, CostOfSales, SoldID, POSDate, CosAcc, BSIDCos )
SELECT tblPosLineDetails.QtySold,
FROM tblPosLineDetails;
The above query works very well if used against Ms Access 2016 table BUT fail to append in MS SQL Server 2016, kindly assist me on this all the links are just super no errors whatsoever. I do not understand this. The SQL Server has the same tables as MS Access.
See what you can do.
Regards
Chris
Does your SQL table carry a Primary Key?
ASKER
No it does not carry that !
Regards
Chris
Regards
Chris
You need a Primary Key in order for the ODBC driver to properly do the "communication"...make a test to see.
ASKER
I have tried that it does not work! see below sir:
INSERT INTO tblPosAccounts ( PosIDs, QtySold, UnitCost, CostOfSales, SoldID, POSDate, CosAcc, BSIDCos )
SELECT tblPosLineDetails.POSID, tblPosLineDetails.QtySold, tblPosLineDetails.UnitCost , tblPosLineDetails.CostOfSa les, tblPosLineDetails.ItemSold ID, tblPosLineDetails.POSDate, tblPosLineDetails.CosAcc, tblPosLineDetails.BSIDCos
FROM tblPosLineDetails;
INSERT INTO tblPosAccounts ( PosIDs, QtySold, UnitCost, CostOfSales, SoldID, POSDate, CosAcc, BSIDCos )
SELECT tblPosLineDetails.POSID, tblPosLineDetails.QtySold,
FROM tblPosLineDetails;
Since you have the exact table structure on SQL just execute the query directly on SSMS to see if you have another issue..like a restrain
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.