Results 1 to 12 of 12
  1. #1
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    TransferText (Access 2002)

    I have an Access database with tables linked to ODBC database tables. I am trying to automate a process to transfer information to text files that are used by other Access databases. I have code to TransferText which is built into a form open event. Because these ODBC linked tables require a password, I have to manually perform this step in order to type the password. Is there a way to add this password to the VBA code? If this could be done, I could schedule this task so that it could be performed during the night. This is the code I have so far:

    Private Sub Form_Open(Cancel As Integer)
    DoCmd.TransferText acExportDelim, "DedSpec", "qryDedEmps", "p:empsalpha.txt"
    DoCmd.TransferText acExportDelim, "RediiSpec", "qryRediiEmps", "s:appsrediiEmps.txt"
    End Sub
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: TransferText (Access 2002)

    If you are running in an integrated security mode with SQL Server, you shouldn't need to supply a password to link to the tables. However if you are using another product that requires passwords, then short of doing something with keystroke emulations (which I find quite distasteful and a last resort), then I think you are stuck with entering a password. Just out of curiosity, why do you export to a text file at all when you could simply make the tables read-only and work with the live data?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText (Access 2002)

    The text files that are created are then copied to each of our school servers to provide county wide employee data for an Access database they use for entering discipline and injury information.

    I am not sure what you mean by "integrated security mode". I asked the gentleman who set me up to be able to get to the data I needed and he was not able to give me an answer.

    I would be willing to try keystroke emulations as a last resort to enter the password if you could elaborate on the procedure to enter that into my code.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  4. #4
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText (Access 2002)

    If your back-end is SQL SERVER, you could create a job on the SQL SERVER machine to schedule a data transformation on a nightly basis.

  5. #5
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText (Access 2002)

    The backend is Oracle.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  6. #6
    2 Star Lounger
    Join Date
    Oct 2002
    Location
    Lakewood, New Jersey, USA
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText (Access 2002)

    Sorry, I'm out of my element, then. Although, I would bet that there is the equivalent to a DTS (Data Transformation Service) package on ORACLE, I would not be able to tell you anything about it. Good luck! <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: TransferText (Access 2002)

    Unfortunately, Oracle doesn't have an integrated security mode, though you can attach to the Oracle tables using an Oracle ODBC driver - I typically recommend the Microsoft driver as it has greater functional capability. Obviously there are a great many details to such a system, and I presume the individual school servers don't have LAN-like connectivity to the Oracle server, so you are probably pretty well stuck with a file transfer process. I don't know the Oracle admin and support tools, so I can't suggest the process for creating a text file from Oracle, but I rather imagine they have tools and scheduling facilities much like SQL Server, so the Oracle database administrator should be able to create a process that dumps the database to a text format suitable for you use. You could then use an import process scheduled in Access at a later time, and pick up the file and create Access tables.

    Just out of curiosity, how do you get all the data back into a single database? Your situation sounds much like the kind of scenario where we would use Access replication - are you doing it that way?
    Wendell

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: TransferText (Access 2002)

    Why couldn't you set up a new database that just gets the data from Oracle via ODBC then to create a text file from there. This could be done on a timed basis, say 7am each morning.
    When you link to the Oracle tables initially, simply save your password as part of this initial link. Every other time you need to access these tables the ODBC driver simply uses this password. You will probably need the DBA's permission to do this, but this would be one way to do it.
    I had to do this for a job for a Client a couple of years ago where the whole job had to run automatically, the database just ran by itself day after day.

  9. #9
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText (Access 2002)

    Wendell, I have a DOS batch file that uses the Windows scheduled task capability to copy the file to each school server. At the school level they have an Access database they use that links to the text file.

    Another batch file connects to each school server and copies their data to a central office file server, again using the scheduled task capability so that this is done each night. Centrally we have a database that compiles all their data so that we can work with it.

    This one aspect that involves this Oracle database is the only part of my entire process that isn't automated to be able to run during my absence.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText (Access 2002)

    Patt, when I initially linked to the Oracle tables I did not see any place in the process where I could save my password. I was using the Microsoft ODBC driver. Is there an Oracle driver that would permit saving my password?
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: TransferText (Access 2002)

    It's been a while since I have used the link to Oracle tables. From memory when you choose the table(s) to link to, it is a question on that selection window (on the botom right side of the window) that you tick.
    Maybe someone who is using the links to Oracle can be more specific.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: TransferText (Access 2002)

    I went back through the ODBC linking process and sure enough there was an option for each table I linked to save my password. I hadn't noticed it when I originally linked the tables. Once I relinked each of my tables and clicked that option for each one, the process now runs without manual intervention. I thank you from the bottom of my heart. Now I can take a few days of vacation when I need it without having to worry about my data.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

Posting Permissions

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