Export data from SQL Server to MS Access

Question

I need to write a stored procedure which reads data from SQL Server and writes it into a MS Access 2003 table. I tried the following but it is not working:

Insert into OpenRowSet
('Microsoft.Jet.OLEDB.4.0',      
'D:\BEMF_LOAN_RECOVERY.mdb';  
''; -- User ID
'', -- Password
'Select * from access_tbl_name') 
    Select * 
    from sql_server_tbl_name

Error mesage

Msg 7308, Level 16, State 1, Line 2
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.

Do you realise that the D: drive in question will be the one on the SQL Server, not on your local PC, assuming that they're different? — Matt Gibson
– Matt Gibson, Commented Dec 2, 2015 at 9:10
@MattGibson both SQL SERVER and file.mdb are on same machine. — Khan
– Khan, Commented Dec 2, 2015 at 9:53
That's not an error message I've seen before. (Can you pull the data from the Access side rather than trying to push it in from SQL Server? Most times I've seen Access grabbing SQL Server data it's just been done inside Access through a simple linked table...) Also, although it's about Excel, this question may be relevant. — Matt Gibson
– Matt Gibson, Commented Dec 2, 2015 at 10:02
I'm just wondering if you really do need to do that. In Access itself, you can create a linked table to a SQL Server table or view, and that would be the normal way to pull data from SQL Server to Access, plus it means that your data will always be up-to-date. — Matt Gibson
– Matt Gibson, Commented Dec 2, 2015 at 10:27

momar · Accepted Answer · 2015-12-02 16:41:49Z

1

Are you sure that 'Ad Hoc Distributed Queries' is enabled on your SQL Server?

By default, SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. When this option is set to 1, SQL Server allows ad hoc access. When this option is not set or is set to 0, SQL Server does not allow ad hoc access.

https://msdn.microsoft.com/en-us/library/ms187569.aspx

answered Dec 2, 2015 at 16:41

momar

3641 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Export data from SQL Server to MS Access

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related