We help IT Professionals succeed at work.

inerting records from local table to sql server?

Fordraiders
Fordraiders asked
on
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
Comment
Watch Question

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

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

Author

Commented:
BrandMaster_acc  is the local table in the access database.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

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

Remember because its an action query (PT) 

Return Records = false

Author

Commented:
ugg, "query must have at least one destination field ?

Author

Commented:
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.
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

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

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

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

John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:

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

CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

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

Author

Commented:
@gustav,
Thanks but that’s the same query I posted? Or am I missing something?
John TsioumprisSoftware & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
It is supposed that the destination table ( the SQL table) is linked to Access
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:

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.

Software & Systems Engineer
CERTIFIED EXPERT
Distinguished Expert 2019
Commented:

Well there is another scenario that the use of "local" table works...it has being a long long time since i have used it but it works...just tested...

Besides the 2 above methods there is a 3rd method that uses the Linked server feature of MS SQL

So you establish a Linked server between your MsSQL server and your Access database..(do remember that SQL needs to have "access" to the Access database ...either local or remote path)

Then you change your query like that (supposing you have called your Linked server MYEXTERNALACCESS

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


Explore More ContentExplore courses, solutions, and other research materials related to this topic.