Impossible: VBS accessing DBF

[Mitch Theophila]

hi,

can anyone post an example of a live, working VBS script that directly
accesses a .DBF file, opens it (with or without indexes, either at this
point would make me very happy), pulls up a recordset, locates a record,
reads a value, (as a bonus replaces a few fields in that record and saves
the change,) and finally closes the .DBF ?

merely the explicit publication of an actual working connection string would
be greatly appreciated.

this would be an old-style .DBF, such as for dBase IV.

i have done a lot of reading thru countless sample apps, web pages and news
messages but there's no one example of this anywhere that shows all of this
together. at this time, i suspect that r/w access to a DBF simply isn't
doable from the VBS/MDAC platform. would appreciate confirmation or
counterexample of thesis.

thanks in advance, best regards, .mitch theophila

[Douglas Laudenschlager [MS]]

Well, this is VB rather than VBScript, but you should be able to adjust it
as necessary...

Option Explicit

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim strConn As String
Dim rs As ADODB.Recordset
Dim strQuery As String
Dim f As ADODB.Field
Set cn = New ADODB.Connection
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office10\1033;" & _
"Extended Properties=dBase IV;"
cn.Open strConn
Set rs = New ADODB.Recordset
strQuery = "SELECT * FROM EMPLOYEE.DBF"
rs.CursorLocation = adUseClient
rs.Open strQuery, cn, adOpenStatic, adLockOptimistic
For Each f In rs.Fields
Debug.Print f.Name
Next
With rs
Debug.Print .Fields("LAST_NAME").Value
Debug.Print .Fields("FIRST_NAME").Value
.Fields("LAST_NAME").Value = "Theophila"
.Fields("FIRST_NAME").Value = "Mitch"
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

This posting is provided “AS IS” with no warranties, and confers no rights.

[Mitch Theophila]

hello Douglas, and wow! great thanks for taking the shot. Sincerely
appreciate your efforts.

Douglas Laudenschlager [MS] <douglas...@microsoft.com> wrote in message
news:IFNRLYt...@cppssbbsa01.microsoft.com...

>
> Well, this is VB rather than VBScript, but you should be able to adjust it
> as necessary...

theoretically, the VBS syntax _is_ a mostly-contained subset of that of VB
.. but in practice, esp. when passing parameters, there is only a
superficial similarity between VBS and other VB's.

that connection string in particular seems to resemble others i've wrestled
with without success.

so ... if no one has a solution already in VBS, it still looks grim.

best regards .mitch

[Douglas Laudenschlager [MS]]

Here is a VBScript version, which works equally well. All I had to do was
change "New" to "CreateObject" and get rid of the Debug.Print statements:

Dim cn
Dim strConn
Dim rs
Dim strQuery
Dim f, strFields
Set cn = CreateObject("ADODB.Connection")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office10\1033;" & _
"Extended Properties=dBase IV;"
cn.Open strConn

Set rs = CreateObject("ADODB.Recordset")

strQuery = "SELECT * FROM EMPLOYEE.DBF"

rs.CursorLocation = 3
rs.Open strQuery, cn, 3, 3

For Each f In rs.Fields

strFields = strFields & f.Name & vbCrLf
Next
MsgBox strFields
With rs
MsgBox "First name: " & .Fields("LAST_NAME").Value
MsgBox "Last name: " & .Fields("FIRST_NAME").Value

.Fields("LAST_NAME").Value = "Theophila"
.Fields("FIRST_NAME").Value = "Mitch"
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

This posting is provided “AS IS” with no warranties, and confers no rights.

[Mitch Theophila]

Douglas, your efforts are greatly appreciated.

I had very good results adapting your VB code to VBS, getting everything to
work except for the update.

Your connection string in particular worked without modification.

Apparently the "impossible" just took a little longer. <g>

The update is still failing with "Operation must use an updateable query.",
error code 80004005. The available postings and other help resources all
talk about permissions, but this is a file on a local hard drive that's got
no restrictions attached to it at all.

If anyone's got any ideas on that, i'd be most interested to read about
them.

Thanks again Douglas, best regards .mitch theophila

Douglas Laudenschlager [MS] <douglas...@microsoft.com> wrote in message

news:PL4cvvD...@cppssbbsa01.microsoft.com...

[Douglas Laudenschlager [MS]]

You are now officially trapped in Jet/dBase Hell, presumably with a version
of the Jet supporting files (ISAM drivers) for dBase that don't support
updating without the Borland Database Engine installed.

Please see our Knowledge Base article Q230125
http://support.microsoft.com/support/kb/articles/q230/1/25.asp on this
problem.

[Mitch Theophila]

yes, and now this thread will appear with the other 2,232,888 messages that
contain the words "updateable query". <g>

thanks for the reference, it helps narrow it down. there once was a BDE
installed here. time to go hunting.

best regards .mitch

Douglas Laudenschlager [MS] <douglas...@microsoft.com> wrote in message

news:JTu1a#SeBH...@cppssbbsa01.microsoft.com...

[Mitch Theophila]

hi again,

that link was an effective "Get Out of Hell" card, i've downloaded JET 4.0
SP5 and we are updating now.

now, what was so hard about any of that? <ugly g>

for the record, here's your VBS example, as it finally worked.

Option Explicit
' Private Sub Command1_Click()

Dim cn
Dim strConn
Dim rs
Dim strQuery
Dim f

Set cn = CreateObject("ADODB.Connection")
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office10\1033;" & _
"Extended Properties=dBase IV;"
cn.Open strConn
Set rs = CreateObject("ADODB.Recordset")
strQuery = "SELECT * FROM EMPLOYEE.DBF"

rs.CursorLocation = 3 ' adUseClient
rs.Open strQuery, cn, 1, 3 ' adOpenDynamic, adLockOptimistic

For Each f In rs.Fields

msgbox f.Name
Next
With rs
msgbox .Fields("LAST_NAME").Value
msgbox .Fields("FIRST_NAME").Value

.Fields("LAST_NAME").Value = "Theophila"
.Fields("FIRST_NAME").Value = "Mitch"
.Update
End With
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing

' End Sub

the update was verified as having worked during the second script execution,
as it shows the changed values.

thanks greatly for all the help! best regards .mitch theophila

Douglas Laudenschlager [MS] <douglas...@microsoft.com> wrote in message

news:JTu1a#SeBH...@cppssbbsa01.microsoft.com...