Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Open text file requiring pwd (2003)

    I'd like to process a series of text files that I can see on the network, but which are in workgroups that are not inside the domain that I am running my Access program from. I have the user names and passwords for these workgroups. How do I read these files into my Access application? I was hoping to find some arguements I could add to a FileSystemObject.OpenTextFile call, but I can't see any. Is there another FSO command that I can use, or something completely different? I vaguely remember that I am supposed to be able to use ADO to open a recordset on a text file. Would that allow me to include the user names and passwords?

    TIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open text file requiring pwd (2003)

    I have no way to test this, but does something like the following work? (Note: edited to correct some typos)
    <code>
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open ConnectionString:="Provider=Microsoft.Jet.OLEDB.4. 0;" & _
    "Data Source=serversharefolder;" & _
    "Extended Properties=""text;HDR=YES;FMT=Delimited""", _
    UserID:="me", Password:="secret"
    rst.Open Source:="SELECT * FROM TextFile.txt", _
    ActiveConnection:=cnn, CursorType:=adOpenStatic, _
    LockType:=adLockReadOnly, Options:=adCmdText
    </code>
    This code requires that you have a reference to the Microsoft ActiveX Data Objects m.n Library, where m.n is the version of your preference.

  3. #3
    3 Star Lounger
    Join Date
    Sep 2002
    Location
    London, England
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Open text file requiring pwd (2003)

    Hi Hans,

    I have tried your code (modified - see below) and get the following error message:
    Cannot start your application. The workgroup information file is missing or opened exclusively by another user.
    The code stops at the cnn.Open statement.
    Is there any chance that the problem is the provider? Should I be using a text file provider, rather than an Jet provider?

    Public Function GetFileRst(strFileName As String, strFilePath As String, _
    strLogon As String, strPwd As String)

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    Dim strCnn As String

    strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;"
    strCnn = strCnn & "Data Source=" & strFilePath & ";"
    strCnn = strCnn & "Extended Properties='text;HDR=YES;FMT=Delimited'"

    cnn.Open strCnn, UserID:=strLogon, Password:=strPwd

    rst.Open Source:="SELECT * FROM " & strFileName, _
    ActiveConnection:=cnn, CursorType:=adOpenStatic, _
    LockType:=adLockReadOnly, Options:=adCmdText

    Do While Not rst.EOF
    Debug.Print rst(0).Value
    Loop

    rst.Close
    Set rst = Nothing

    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Open text file requiring pwd (2003)

    There is no specific Text provider for OLE DB, you can use the Jet provider with "text" in the extended properties, or the OLE DB provider for ODBC with the ODBC text driver, but I'm afraid I don't know enough about them to offer further assitance. Hopefully someone else will be able to help you.

Posting Permissions

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