inerting records from local table to sql server?

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

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

Select allOpen 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.