ADO-dBase IV Question

[Roger]

I've written a program in MS Access 2000 that grabs data from a
dBase IV table and then displays it on a form. It works fine in
a Windows 2000 environment, but I get a run-time error when I
run the very same program in a Windows XP Pro (SP2) environment.
All Office updates and XP updates are installed.

The code where the error is generated is as follows:

-----------------------------------

Dim pPath As New ADODB.Connection
Dim pTable As New ADODB.Recordset

pPath.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DATA\;Extended Properties=""DBASE IV;"";"

pTable.Open "Table1.DBF", pPath

-----------------------------------

The program bombs on the 4th statement (pTable.Open) when I try
to open Table1. The error that I get is:

Run-time error '-2147217900(80040e14)': Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I tried changing the Provider to "Driver={Microsoft dBASE Driver
(*.dbf)}; DriverID=277; Dbq=C:\DATA\;" and this time the error I
got was:

Run-time error '-2147217900(80040e14)': [Microsoft][ODBC dBase
Driver] Invalid SQL statement; expected 'DELETE', 'INSERT',
'PROCEDURE', 'SELECT', or 'UPDATE'.

I can't figure this error out keeping in mind that the program
works fine in Windows 2000 but not XP. I believe the program
ceased to work in XP after SP2 was installed - not 100% sure.
Any ideas?

Thanks,
Roger

 

[Eric Schittlipz]

I've written a program in MS Access 2000 that grabs data from a
dBase IV table and then displays it on a form. It works fine in
a Windows 2000 environment, but I get a run-time error when I
run the very same program in a Windows XP Pro (SP2) environment.
All Office updates and XP updates are installed.

The code where the error is generated is as follows:

-----------------------------------

Dim pPath As New ADODB.Connection
Dim pTable As New ADODB.Recordset

pPath.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DATA\;Extended Properties=""DBASE IV;"";"

pTable.Open "Table1.DBF", pPath

-----------------------------------

The program bombs on the 4th statement (pTable.Open) when I try
to open Table1. The error that I get is:

Run-time error '-2147217900(80040e14)': Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

I tried changing the Provider to "Driver={Microsoft dBASE Driver
(*.dbf)}; DriverID=277; Dbq=C:\DATA\;" and this time the error I
got was:

Run-time error '-2147217900(80040e14)': [Microsoft][ODBC dBase
Driver] Invalid SQL statement; expected 'DELETE', 'INSERT',
'PROCEDURE', 'SELECT', or 'UPDATE'.

I can't figure this error out keeping in mind that the program
works fine in Windows 2000 but not XP. I believe the program
ceased to work in XP after SP2 was installed - not 100% sure.
Any ideas?

Thanks,
Roger

You would get this error if the folder "C:\DATA\" did not contain
"Table1.DBF"
This is the first thing I would check.

The second thing to query is why you choose to use an ADODB recordset to
grab data and place it on a form. Could you not use a linked table so that
no code was necessary to display the information? Just bind the form to the
table.

Then there is the strange naming convention pPath is a connection and pTable
is a recordset???

In case you might like to see an example of someone else's coding and naming
conventions, here is an example sub:

Public Sub ShowSomeRecords()

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

Set cnn = New ADODB.Connection

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\MyStuff\ACT\Database\;" & _
"Extended Properties=dBASE IV;" & _
"User ID=Admin;Password="

cnn.Open

strSQL = "SELECT TOP 5 NAME FROM CONTACTS " & _
"WHERE NAME Is Not Null " & _
"ORDER BY NAME DESC"

Set rst = New ADODB.Recordset

rst.Open strSQL, cnn

While Not rst.EOF
MsgBox Nz(rst!Name, "")
rst.MoveNext
Wend

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
If rst.State > adStateClosed Then
rst.Close
End If
Set rst = Nothing
End If

If Not cnn Is Nothing Then
If cnn.State > adStateClosed Then
cnn.Close
End If
Set cnn = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub

 

[Roger]

Eric,

Thanks for helping out. I got a little farther, but no cigar
just yet.

In response to your comments, the data tables in the database
are in use by many users with a variety of different
applications. My program is seeking one record in the table,
processing the data extracted from that record, and then
emailing the results to various others. There's no need for my
continuing to access the tables once the email has gone out.
I've always built my programs with the thought of staying
connected only if you need to. In web-based applications using
ASP.NET, I have no choice. I'm sure some would argue with that
philosophy where non-web applications are concerned, but it's
the way I choose to do business.

The naming conventions I use come from my earlier days when I
programmed heavily in Borland's dBase for Windows. I just can't
shake it even in the VB.NET and ASP.NET programs that I write.
But I'm trying!

The new problem is: "Index file not found"

Here's my revised code...
---------------------

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

Set cnn = New ADODB.Connection

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DATA\;" & _
"Extended Properties=dBASE IV;" & _
"User ID=Admin;Password="

strSQL = "SELECT * FROM TABLE1.DBF"

cnn.Open

Set rst = New ADODB.Recordset

rst.Open strSQL, cnn

--------------------

TABLE1.DBF has an index file - TABLE1.MDX. Both files are where
they're supposed to be (i.e., C:\DATA\). I tried adding a few
more parameters after the rst.Open statement:

rst.Open strSQL, cnn, adOpenForwardOnly, adLockOptimistic,
adCmdText

But that didn't help. I also tried strSQL = "SELECT * FROM
TABLE1.DBF ORDER BY" to avoid the need for indexing, but that
didn't help either. In fact, that resulted in a syntax error.

Am I missing a Reference? Do I have a syntax error? Am I
missing additional statements to process the index file?

Thanks much,

Roger

I've written a program in MS Access 2000 that grabs data from
a
dBase IV table and then displays it on a form. It works fine
in
a Windows 2000 environment, but I get a run-time error when I
run the very same program in a Windows XP Pro (SP2)
environment.
All Office updates and XP updates are installed.

The code where the error is generated is as follows:

-----------------------------------

Dim pPath As New ADODB.Connection
Dim pTable As New ADODB.Recordset

pPath.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=C:\DATA\;Extended Properties=""DBASE IV;"";"

pTable.Open "Table1.DBF", pPath

-----------------------------------

The program bombs on the 4th statement (pTable.Open) when I
try
to open Table1. The error that I get is:

Run-time error '-2147217900(80040e14)': Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or
'UPDATE'.

I tried changing the Provider to "Driver={Microsoft dBASE
Driver
(*.dbf)}; DriverID=277; Dbq=C:\DATA\;" and this time the error
I
got was:

Run-time error '-2147217900(80040e14)': [Microsoft][ODBC dBase
Driver] Invalid SQL statement; expected 'DELETE', 'INSERT',
'PROCEDURE', 'SELECT', or 'UPDATE'.

I can't figure this error out keeping in mind that the program
works fine in Windows 2000 but not XP. I believe the program
ceased to work in XP after SP2 was installed - not 100% sure.
Any ideas?

Thanks,
Roger

You would get this error if the folder "C:\DATA\" did not
contain
"Table1.DBF"
This is the first thing I would check.

The second thing to query is why you choose to use an ADODB
recordset to
grab data and place it on a form. Could you not use a linked
table so that
no code was necessary to display the information? Just bind the
form to the
table.

Then there is the strange naming convention pPath is a
connection and pTable
is a recordset???

In case you might like to see an example of someone else's
coding and naming
conventions, here is an example sub:

Public Sub ShowSomeRecords()

On Error GoTo Err_Handler

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim strSQL As String

Set cnn = New ADODB.Connection

cnn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" &
_
"Data
Source=C:\MyStuff\ACT\Database\;" & _
"Extended Properties=dBASE IV;" & _
"User ID=Admin;Password="

cnn.Open

strSQL = "SELECT TOP 5 NAME FROM CONTACTS " & _
"WHERE NAME Is Not Null " & _
"ORDER BY NAME DESC"

Set rst = New ADODB.Recordset

rst.Open strSQL, cnn

While Not rst.EOF
MsgBox Nz(rst!Name, "")
rst.MoveNext
Wend

Exit_Handler:

On Error Resume Next

If Not rst Is Nothing Then
If rst.State > adStateClosed Then
rst.Close
End If
Set rst = Nothing
End If

If Not cnn Is Nothing Then
If cnn.State > adStateClosed Then
cnn.Close
End If
Set cnn = Nothing
End If

Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " &
Err.Number
Resume Exit_Handler

End Sub