Open text file requiring pwd (2003)

Topic

New Reply

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

Reply | Quote

Replies

I have no way to test this, but does something like the following work? (Note: edited to correct some typos)

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

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote