Results 1 to 7 of 7

Thread: DTS Connect to Access 97 secure database trouble...

  1. 10-07-2005, 04:01 PM #1
    Frank Johnson
    Frank Johnson is offline Registered User
    Join Date
    Sep 2005
    Posts
    13

    DTS Connect to Access 97 secure database trouble...

    Ok, I need to give some details. I have a secure Access Database using WorkGroup Security logon. It is an Access 97 database (yes I know it is "old").

    I tried to create a DTS package in SQL Server 2000 to connect to it, but I am getting the same error that I got before I "joined" the workgroup when attempting to access the DB. So I changed the Service logon identites to mimic <me> as the logon, and still I can't get it to connect. The user name and password I put in the connection are correct.

    It seems like nothing is working and I am all out of ideas. Does anyone have any recommendations? Has anyone done anything like this successfully?

    PS: I can manually logon to the database, and extract the data 1 table at a time, but I don't want to export 40 tables each time I have to load this data. Any help would be appreciated. Thanks in advance.

    Frank
    Reply With Quote Reply With Quote
  2. 10-10-2005, 09:47 AM #2
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    What does the package do? Is sql server in same workgroup?
    Reply With Quote Reply With Quote
  3. 10-10-2005, 12:17 PM #3
    Frank Johnson
    Frank Johnson is offline Registered User
    Join Date
    Sep 2005
    Posts
    13
    Looks like I stumped a few of you with this one. If I figure it out, I will post the answer.

    In the meantime, it looks like I will be manually exporting table... Not really, I already wrote a custom SQL statement in access that exports the data to files and built a DTS package to import the data... I have a good work around, but we all need to think outside of the box sometimes. It would have been nice to hear one of you recommend that. :-P (sarcasm)...

    Hope you all had a great weekend.


    Frank
    Last edited by Frank Johnson; 10-10-2005 at 02:08 PM.
    Reply With Quote Reply With Quote
  4. 10-10-2005, 02:09 PM #4
    Frank Johnson
    Frank Johnson is offline Registered User
    Join Date
    Sep 2005
    Posts
    13
    The package was going to be used to Export table data from Access to MS SQL server on a regular basis. We want to do some advanced reporting on the data, and Access just can't handle it.

    The workgroup security piece is for MS Access 97 only. SQL server can not be in the workgroup security piece as far as I know. This is all on a network domain and fully accessible if that was your thought.
    Reply With Quote Reply With Quote
  5. 10-10-2005, 03:47 PM #5
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Then you have to ensure that sql services account has permission to access that ms access db.
    Reply With Quote Reply With Quote
  6. 10-10-2005, 11:24 PM #6
    Frank Johnson
    Frank Johnson is offline Registered User
    Join Date
    Sep 2005
    Posts
    13
    I have changed the services to my name, and I even went so far as to make an account as the database owner name, and give it the same password, but none of that worked. Had the network Admin a little pissed at me for that, but he got over it. :-P
    Reply With Quote Reply With Quote
  7. 10-11-2005, 08:09 AM #7
    rmiao
    rmiao is offline Registered User
    Join Date
    Sep 2002
    Posts
    5,938
    Can you open that access db on sql server box directly?
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules