SQL Connectivity with Linked MS Access DB
I am having an issue with SQL connectivity and was curious if you might have
some insight into this situation.
I have linked a MS Access DB to SQL server. (Why? Don’t ask)
I have a sql query that calls the a table in linked MS access DB.
At one specific client site, I am able to run this query successfully from
Query analyzer from an XP workstation. The SQL Query analyzer is logged
into SQL as a local user. I am not able to run this query successfully from
my VB application. My VB app is connecting to the SQL server using the standard
DSN from moxy (MoxyDSN32) and ADO. The MoxyDSN32 uses TCP/IP to connect to
the SQL Server.
What kind of difference might their be between the way SQL Query Analyzer
connects to SQL compared to a VB application. Am I correct in assuming that
SQL Query Analyzer uses its own library to connect to SQL. If that is the
case, can I adjust for that? I tried changing the DSN to named pipes, but
that did not help.
Here is the error msg…
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 '\\tweedy_files\advent\Moxy40\ISO\AllocLync_ISO.mdb'. It is
already opened exclusively by another user, or you need permission to view
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize
returned 0x80004005: ].
Some more tidbits of info…
The MS Access db is linked using the UNC. All users and machines have access
to the db.
On my office network, my VB application runs the query fine from my XP client.
My office machines are connected via a workgroup, the client's is connected
via a Domain.
Another difference is that my XP client can run the query thru query analyzer
when logged via WinNT Authentication. The query also fails when their XP
client is logged into the SQL server via WinNT Authentication. Though it
runs successfully when run dircectly on the server when logged into SQL Query
analyzer via WinNT Auth.
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center