How to Import Data to SQL Server from Access Database using TSQL Script (1 Viewer)

[happy1001]

I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link -

http://www.codeproject.com/Articles/21351/Import-Data-to-SQL-Server-from-Excel-or-Access-usi

Here is the code that I have tried -

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO

IF NOT EXISTS (SELECT 1 FROM sys.servers WHERE name = 'MSAccessConnect')
BEGIN
    EXEC sp_addlinkedserver 'MSAccessConnect',
       'Access 97',
       'Microsoft.ACE.OLEDB.12.0',
       'C:\SQL Project\TestDB1001.mdb' -- put here your datasource path
END
GO

SELECT * FROM OPENQUERY(MSAccessConnect, 'SELECT * FROM [Table1001]')  -- put table name here

go
sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
GO

The access database file path is - 'C:\SQL Project\TestDB1001.mdb'
The Table from which I want to import the data is - [Table1001]

but when I run this script, I get this error -
9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.

I am not being able to figure out, how to make it work.

Secondly I need to make 2 more changes to the code posted above.
1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')

Thanks

 

[Ranman256]

you would do it the other way.
export access data TO the SQL tables without any code.
use append query.

 

[Randy]

As noted above, MS Access natively connects with MS SQL server
go into your MS Access application
external data
ODBC database
click on "Link the data source by creating a linked table"
In the select data source dialogue box find your database
click OK
in the dialogue box find the SQL table you need to link
click ok
the SQL table will now appear as a linked table in your MS Access application
using the normal MS Access append query you can append data as needed.