Using FoxPro 3.0 tables in VB using ADO 
Author Message
 Using FoxPro 3.0 tables in VB using ADO

I could use some help in deleting records from a FoxPro table using ADO in a
VB6 app.

I have a table with a field called ReqDate, a date field, (i.e. 1/1/98) that
I would like to delete all records for 1998.  I created a simple app to
test.  It fails on the SQL string.

Any suggestions would be appreciated.

Jeff
=================================================

Private Sub Command1_Click()
    Dim strConnectionString As String
    Dim strPath As String
    Dim rs As Recordset
    Dim adoConnect As Connection
    Dim strSQL As String
    Dim numRec As Integer

    Set adoConnect = New ADODB.Connection
    Set rs = New ADODB.Recordset

    strPath = "C:\My
Documents\McGilvra\dpsdata\1998data\98Year_0\Data1998_0"

    strConnectionString = "Driver={Microsoft Visual FoxPro Driver};" & _
             "SourceType=DBF;" & _
             "SourceDB=" & strPath & "\;" & _
             "Exclusive=No;"

    Set adoConnect = New ADODB.Connection
    adoConnect.Open strConnectionString

    strSQL = "Delete From CUSTREQ Where ([REQDATE] < #1/1/98# or [REQDATE] >
#12/31/98# )"

    adoConnect.Execute strSQL, numRec, adExecuteNoRecords

    MsgBox "Records Affected = " & numRec

End Sub



Fri, 06 Feb 2004 23:18:30 GMT  
 Using FoxPro 3.0 tables in VB using ADO

Quote:
> I have a table with a field called ReqDate, a date field, (i.e. 1/1/98)
that
> I would like to delete all records for 1998.  I created a simple app to
> test.  It fails on the SQL string.
>     strSQL = "Delete From CUSTREQ Where ([REQDATE] < #1/1/98# or [REQDATE]

> #12/31/98# )"

My first thought would be to try replacing the #'s with apostrophes.
Different SQL dialects want their dates different ways. You might also need
to drop the []'s.


Fri, 06 Feb 2004 23:59:48 GMT  
 Using FoxPro 3.0 tables in VB using ADO
It does not appear that FoxPro supports the # delimiter around dates; in
fact, it seems to require rather strict formatting. I played with your
query and got it to work as follows:

strSQL = "delete from tester where birthdate < {^1970/01/01}"

Both the curly braces, and the caret character, were required for the
deletion to work.

Hope this helps!

Steven Bras, MCSD
Microsoft Developer Support/Visual Basic WebData



Sat, 07 Feb 2004 05:47:22 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Link FoxPro Table in Access 2000 using ADO

2. SELECT INTO using ADO and Foxpro tables

3. Cannot create Visual FoxPro table using ADO

4. ADO connection using Foxpro table (DBF) - SQL

5. Using visual FoxPro 2.6 files with VB 3.0

6. Use Ado to open Foxpro 3.0 Dbf Table!!!

7. Using VB read FoxPro table

8. How to connect to FoxPro Free Tables using VB 6

9. How to connect to FoxPro Free Tables using VB 6

10. SETTING NULL OFF in FoxPro Using VB and ADO

11. Accessing a Visual Foxpro 3.0 Database using VB4 pro

12. Using Data from a Foxpro 3.0 DBC linked databases

 

 
Powered by phpBB® Forum Software