Post reply

OPENROWSET to Access MDB

  • June 23, 2006 at 9:21 am

    #68718

    At one site: When using OpenRowset to a client MsAccess database from SqlServer 2000, the lock file ldb remains on the client. The access jet is 2000 (Microsoft.Jet.OLEDB.4.0).

    At a separate site(system) this does not happen. The ldb file gets deleted.

    SAMPLE in use:

    SELECT customerid

    FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 

       '\\Machine\C$\test\Northwind.mdb';'Admin';'', Orders)

    Any Thoughts,

    Thanks Bluecats

  • June 23, 2006 at 10:30 am

    #645613

    Could be an MDAC version issue - I'd check those versions. I'm assuming that the client versions of Access are the same patch level?

    It could also be a client security issue, where the account accessing the client has no rights to delete the ldb file. There needs to be full control to delete it.

    You might want to try creating a non-administrative share to access it with as well. C$ requires an administrator to access.

     Also, OPENROWSET implies a distributed transaction, so MS DTC is involved. You may want to investigate that.

    Some avenues to pursue, my money is on MDAC or security settings.

    Hope that helps a bit.

    J

  • June 26, 2006 at 9:37 am

    #645862

    With the access \\Computer\C$ there should be Both Share and NTFS permissions set appropriately. The difference between sites (assuming you mean Web Sites) may be that anynymous access allowed or not. If Anonymous Access is not allowed then the person or process has good network credentials, and with Anonymous it may not have valid network credentials, just IUSR_YourWebServermane local account by default, so no Share and NTFS permissions on your file and the folder this file resides (Test in your case)

    Regards,Yelena Varsha

  • June 27, 2006 at 5:32 am

    #646026

    Actually I didn't word my comment about the use of C$ properly. What I meant to say is - don't use it. That's a dangerous security habit.

  • June 27, 2006 at 10:06 am

    #646119

    James,

    same here: I did not word my comment about it too! Good thing you did it now.

    Bluecats,

    James is right. Do not use C$, create a share that will hold only your specific file and give both NTFS and Share access to the account that copies files.

     

    Regards,Yelena Varsha

  • July 5, 2006 at 7:06 am

    #647490

    Security was certainly the issue. All is well now. (Not using the C$ of course)

    Thanks for your help.

    Bluecats

     

Viewing 6 posts - 1 through 5 (of 5 total)

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