Connection String Problem

[WordVBAProgrammer]

The code below works on, say, 99 out of 100 machines, regardless of
the version of Word.

I suspect a DLL is missing on the target machine, or a flaw in my
code. We are not allowed to distribute DLL files or in any other way
change a user's machine.

The code below opens a text file (SBAOffices.txt) and parses through
to populate a combobox.

Dir checks to see if the file exists. It does. strDB is the Temp or
Templates directory.

The code fails on rs.Open. Record count is 0.

Any ideas or comments to help with the thought process would be
appreciated.

Thanks!

Dan

==========================
Count = 0

If Dir(strDestination, vbNormal) <> "" Then
'Get count of records in file
If strDB = "" Then
strDB = ActiveDocument.AttachedTemplate.Path
End If
Set rs = CreateObject("ador.recordset")
strConnect = _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DefaultDir=" & strDB & ";" & Chr(34)
rs.Open "Select * from " & strTable & " order by ST, Name",
strConnect, adOpenStatic
Count = rs.RecordCount

If Count = 0 Then
GoTo ErrorOfficesList
End If
ReDim OfficeList(Count, 7)

'14 columns in SBAOffices.txt
' Access and read Text file

Do Until rs.EOF
For Each fld In rs.Fields
If fld = Empty Then
Else

....
==========================

 

[Perry]

Usually, connecting to a textfile using an ADO recordset
coded like this works sufficiently.

It has a slight different approach using another
connectionstring setting, you may want to test.
The recordset is opened readonly in my example, in yr code it wasn't.
Note how the Listbox is populated as well ...

Krgrds,
Perry

Userform/Word VBA containing a listbox (Listbox1)

==begin code
Dim cn As New ADODB.Connection
Dim rs As New ADOR.Recordset

With cn
.Provider = "MSDASQL"
.ConnectionString = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
"DBQ=c:\temp"
.Open
End With
rs.Open "select * from book1.txt", cn, adOpenStatic, adLockReadOnly
Debug.Print rs.RecordCount

With rs
Me.ListBox1.ColumnCount = .Fields.Count
Me.ListBox1.Column = .GetRows(.RecordCount)
End With
rs.Close
cn.Close

==end code

 

[WordVBAProgrammer]

Perry,

Thanks for your quick response. I couldn't review our suggestions
until today.

I placed your code in my project and found that it worked once, and
then failed after, even after rebooting.

I had to set a reference to MS Active X Data Object (msado15.dll) and
MS Active X Data Object Recordset 2.7 Library (msador15.dll). This
led me to wonder if the user would have these on their machine. This
project is freely distributed to our customers nationwide.

I believe I got a result the first time through, i.e., rs.fields = 14,
which was correct.

After further modifying your code to use my variables things got
worse. The Debug.Print rs.RecordCount always came up 0 and a prompt
<illegal operation object closed [paraphrase]).

At this point I blatently copied your code, placing my text file in
C:\Temp and revising the select statement to 'from SBAOffices.txt'
with no luck.

The original programmer has a statement in 'GoTo ErrorOfficeList'
(when count = 0) which modifies the registry:

If Err = 3146 And blnFirst3146 = True Or Err = 3704 And blnFirst3146 =
True Then
If System.PrivateProfileString("",
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Text",
"Format") <> "Delimited(,)" Then
System.PrivateProfileString("",
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Text",
"Format") = "Delimited(,)"
End If
If System.PrivateProfileString("",
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text",
"Format") <> "Delimited(,)" Then
System.PrivateProfileString("",
"HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text",
"Format") = "Delimited(,)"
End If
Err = 0
blnFirst3146 = False
GoTo ConnectFile
End If

I'm not sure this has an effect, but it may (and my knowledge of the
registry is, obviously, not as extensive as his was).

Thanks for your help. I've put my code back to my original and it
works on my machine. Loved your Listbox code ... elegant. I've got
to try it when I have a wholesale dump into a listbox.

Dan