I got error like this Invalid object name 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ezacc\DATA\accountdata.mdb.ClientAc'.

Question

0.00/5 (No votes)

See more:

I got error like this

Invalid object name 'Provider=Microsoft.Jet.OLEDB.4.0;DataSource=c:\ezacc\DATA\accountdata.mdb.ClientAc'.

while executing this query in sql server

SQL

select * into [ACTYPE] from [Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\ezacc\DATA\accountdata.mdb].[ClientAc]

Would u pls resolve this error

I have tried still it is getting same error

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

Would u please elaborate ad-hoc queries in a stepwise

Posted 29-Jul-15 2:04am

TarunKumarSusarapu

Updated 29-Jul-15 2:53am

v4

Add a Solution

Comments

[no name] 29-Jul-15 8:13am

Fix your query and the problem will resolve itself.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2015-07-29T02:16:00

Solution 1

In order to query Access database from SQL Server you need to use OPENROWSET or similar function or define the Access db as a linked server.

Have a look at OPENROWSET (Transact-SQL)[^].

Also ensure that you have allowed distributed ad-hoc queries as explained in ad hoc distributed queries Server Configuration Option[^]

Posted 29-Jul-15 2:16am

Wendelius

Updated 29-Jul-15 2:18am

v2

Comments

TarunKumarSusarapu 29-Jul-15 8:52am

I have tried still it is getting same error

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

Would u please elaborate ad-hoc queries in a stepwise

Wendelius 29-Jul-15 9:14am

Your query is called a distributed ad-hoc query. Please go through the documentation links I provided.

Kornfeld Eliyahu Peter · Answer 2 · 2015-07-29T02:22:00

Solution 2

Your SQL totally wrong - syntactically...
To open an external source ad-hoc, you should use OPENROWSET in your SELECT query...

SQL

SELECT * INTO [ACTYPE] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\ezacc\DATA\accountdata.mdb',ClientAc)

For more details and options read here: https://msdn.microsoft.com/en-us/library/ms190312.aspx[^]

Posted 29-Jul-15 2:22am

Kornfeld Eliyahu Peter

Comments

TarunKumarSusarapu 29-Jul-15 8:47am

I got error like this

Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.