inerting records from local table to sql server?
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
Error says table BrandMaster_acc does not exist ?
Thanks
fordraiders
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
just try to rename it...if everything is correct it should work...
Remember because its an action query (PT)
Return Records = false
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.
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
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
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.
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial