[help] SQL export data to access problems

declare
@source nvarchar(1000),
@tablename nvarchar(30)
set @source = 'opendatasource(''Microsoft.ACE.OLEDB.12.0'', ''Data Source=E:\database.mdb;User ID=Admin;Password='')...'

set @tablename ='dwfields'
exec ('delete from '+@source+@tablename)
exec ('insert into '+@source+@tablename+' select * from '+@tablename)

Database.mdb in the sql2008 where the machine, I only read permissions.

Before I was to come in the sql2008 data into database.mdb, now sql2008 dwfields table structure change. Derived data is wrong. How to solve the?

Started by Katherleen at February 01, 2016 - 4:43 PM

Because you have the best *, specify the column name, one one corresponding

Posted by Phoebe at February 12, 2016 - 5:33 PM

All the data I need sql2008 dwfields.
Now I want to put the database.mdb in table dwfields is empty, then the reconstruction of a dwfields, but I only read access to database.mdb, therefore hope to remove surface reconstruction for dwfields by using the SQL statement.
How to realize?

Posted by Katherleen at November 20, 2016 - 10:37 AM

Drop table @source+@tablename is not enough.

Posted by Katherleen at December 02, 2016 - 11:10 AM

Select * into from dwfields where 1=2 to generate a table structure.

Posted by Phoebe at December 13, 2016 - 12:01 PM

Directly using the SQL import and export tools,
The table structure design on both sides of the same, export after the completion of repair back
What problems you?

Posted by Frederica at December 16, 2016 - 1:01 PM

Import and export tool is not available, I didn't write permissions to the database.mdb database.mdb is not to proceed.

Posted by Katherleen at December 30, 2016 - 1:30 PM

If you have no higher authority
You can also delete read-only execution script created.

Posted by Bernard at January 01, 2017 - 1:42 PM

select * into opendatasource('Microsoft.ACE.OLEDB.12.0', 'Data Source=E:\database.mdb;User ID=Admin;Password=')...dwfields from dwfields where 1=2

No, not for the operation of database.mdb, suggesting that
There is a syntax error near the keyword'opendatasource'

Posted by Katherleen at January 01, 2017 - 4:14 PM

Read access SQL script can be used to realize the add and delete data, can not use the import and export tools.

Posted by Katherleen at January 04, 2017 - 2:23 PM

If the structure does not change frequently, it is corresponding to the column names specified

Posted by Phoebe at January 09, 2017 - 3:52 PM

The statement:

exec ('insert into '+@source+@tablename+' select * from '+@tablename)

Instead of
exec ('insert into '+@source+@tablename+'(field list) select field list from '+@tablename)

Posted by Nelson at January 13, 2017 - 3:04 PM