Cannot query Access database with OPENDATASOURCE

Question

Cannot query Access database with OPENDATASOURCE

Sql Junkie-204042

Hall of Fame

Points: 3874
More actions
June 8, 2005 at 11:10 am

#89525

I have to query an Access table from SQL. While running from QA,
SELECT top 10 a.*
FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','User ID=MyUser;Password=Mypassword;Data Source=C:\tables.mdb;Persist Security Info=False;Jet OLEDB:System database=C:\Windows\System.mdw')...TableName
I get this error
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.
[OLE/DB provider returned message: The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ].
Why does the error show the file name with empty strings ?
Any ideas ? Thanks.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Does the server have permission to access that folder?

Is the file opened by someone else (you or the server maybe). Check if msaccess is opened in the task manager... and kill it if it is.

Are you sure that the path is correct?

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 2

The Access file is not opened by any user. The path is correct and the server has access to the folder where the Access file resides.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Thaught of something else... Even when all the users are out, sometimes the .lock files is not deleted. Make sure it's gone and try again.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Have you checked with the task manager?

Sorry for being persistant, but sometimes the application is not visible... and I don't know any other way of getting that message .

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 5

There is a VB application that uses that Access database. I need to run the query even with application running.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Why can't you run that query from the application itself?

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 7

I forgot to explain. There is a VB 5 app that uses Access tables and we are in the process of migrating to VB.Net with SQL Server.

Some of the tables have moved to SQL. The query will run in VB5 needs to do a join between tables in Access and SQL and we are not using LinkedServer.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Hmm.. Seems like a linked server would be great in this situation.

Is there a reason why you can't move the whole access db in one shot (like the app needs to be running while we do this)?

Sql Junkie-204042 Hall of Fame Points: 3874 More actions · Answer 9

The VB.net app is being developed which reads SQL tables. So parts of application are in VB5 and VB.Net