Avatar of Fordraiders
Fordraiders
Flag 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
Microsoft AccessVBA

Avatar of undefined
Last Comment
John Tsioumpris

8/22/2022 - Mon
John Tsioumpris

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

Fordraiders

ASKER
BrandMaster_acc  is the local table in the access database.
John Tsioumpris

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

Remember because its an action query (PT) 

Return Records = false

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Fordraiders

ASKER
ugg, "query must have at least one destination field ?
Fordraiders

ASKER
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 Tsioumpris

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
John Tsioumpris

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 Tsioumpris

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

Gustav Brock

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

Your help has saved me hundreds of hours of internet surfing.
fblack61
Fordraiders

ASKER
@gustav,
Thanks but that’s the same query I posted? Or am I missing something?
John Tsioumpris

It is supposed that the destination table ( the SQL table) is linked to Access
Gustav Brock

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.

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.