Link to home
Start Free TrialLog in
Avatar of Fordraiders
FordraidersFlag for United States of America

asked on

inerting records from local table to sql server?

pass through query not working in access
inerting records from local table to sql server?

INSERT INTO t_sst_BrandMasterTest ( Brand_id, Supplier_Number, Supplier_Name, Brand, Preferred_Supplier )
SELECT BrandMaster_acc.Brand_id, BrandMaster_acc.Supplier_Number, BrandMaster_acc.Supplier_Name, BrandMaster_acc.Brand, BrandMaster_acc.Preferred_Supplier
FROM BrandMaster_acc

Open in new window



Error says table  BrandMaster_acc  does not exist ?



Thanks
fordraiders
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Why it doesn't work...have you made a test..does  the table  BrandMaster_acc  actually exists...probably a small character like different language a space...it might seem correct while not

The simplest thing...copy-paste to a simpler name like t1 and retry

INSERT INTO t_sst_BrandMasterTest ( Brand_id, Supplier_Number, Supplier_Name, Brand, Preferred_Supplier ) 

SELECT t1.Brand_id, t1.Supplier_Number, t1.Supplier_Name, t1.Brand, t1.Preferred_Supplier FROM t1

Avatar of Fordraiders

ASKER

BrandMaster_acc  is the local table in the access database.

just try to rename it...if everything is correct it should work...

Remember because its an action query (PT) 

Return Records = false

ugg, "query must have at least one destination field ?
this makes sense:
A pass-through query CAN'T reference a local Access table. Pass-through
queries run on the server: they don't know anything about the MDB that
they're located in.

Yes...missed that...because you are talking directly to the server...

You can either link the SQL table or use a Recordset object to iterate the local table and create INSERTs statements to the SQL servers

Check this for the 2nd case : https://www.utteraccess.com/forum/index.php?showtopic=2032698

You should create a normal link to the table t_sst_BrandMasterTest via ODBC and then run a normal append query:


INSERT INTO NameOfYourLinkedTable-t_sst_BrandMasterTest
    ( Brand_id, Supplier_Number, Supplier_Name, Brand, Preferred_Supplier )
SELECT
    Brand_id, Supplier_Number, Supplier_Name, Brand, Preferred_Supplier
FROM BrandMaster_acc

Open in new window

@gustav,
Thanks but that’s the same query I posted? Or am I missing something?
It is supposed that the destination table ( the SQL table) is linked to Access

Yes. The table name must be the name of the table as linked in Access, which may be different from the table name in SQL Server.

And it must be created as a normal Select query, not a Pass-Through query.

ASKER CERTIFIED SOLUTION
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial