Re: Update FoxPro 6 table from ado

Tech-Archive recommends: Fix windows errors by optimizing your registry

Hi James,

Be sure you have the latest ODBC drivers or OLE DB data provider,
downloadable from msdn.microsoft.com/vfoxpro/downloads/updates. The OLE DB
data provider is preferable since it works against all versions of FoxPro
tables. ODBC only works on tables in the VFP6 format and below.

Does your ODBC or OLE DB data source point to the \\shipping\c\cps directory
or to the DBC (database container) associated with cps.dbf? If so, you only
need "Update cps set..." Also, don't use "alias cps."

If this is an exact copy/paste then you're missing a comma between the value
stNew.Name and the next field to update.

FoxPro SQL statements are not terminated with a semicolon. Semi's are used
as line continuation characters.

In FoxPro, double and single quotes and square brackets are all used as
string delimiters. When assembling a SQL Pass-through string like this it
might be easier to work with the square brackets: ...sSql = sSql &
"cps.address = " & "[" & stNew.Address & "], "....



--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
cindy_winegarden@xxxxxxx www.cindywinegarden.com


"James Houston" <houstjd@xxxxxxxxx> wrote in message
news:u%230vlUGHGHA.3000@xxxxxxxxxxxxxxxxxxxxxxx
> Can someone post a working example of an update query run against a
> FoxPro 6 table? Or tell me what's wrong with my code listed below? I
> keep getting an error message telling me that there's an unrecognized
> phrase or keyword when I run this query. stNew is a user defined
> datatype.
>
> sSql = "Update \\shipping\c\cps\cps.dbf alias cps set cps.name = " &
> "'" & stNew.Name & "' "
> sSql = sSql & "cps.address = " & "'" & stNew.Address & "', "
> sSql = sSql & "cps.address_2 = " & "'" & stNew.Address2 & "', "
> sSql = sSql & "cps.city = " & "'" & Trim(stNew.City) & "', "
> sSql = sSql & "cps.state = " & "'" & stNew.State & "', "
> sSql = sSql & "cps.zip = " & "'" & stNew.Zip & "' " & vbCrLf
> sSql = sSql & "where cps.invoice = " & "'" & sInvoice & "';"


.

Relevant Pages

  • Re: FoxPro ODBC DSN Problem
    ... FoxPro tables is the same as any other ODBC or OLE DB compliant database. ... Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP ...
    (microsoft.public.data.odbc)
  • Re: import data from FoxPro database (ISIS)
    ... I have the ODBC driver on my computer. ... I'm a little confused about setting up the OLE DB stuff, ... you've got Visual FoxPro files. ... The DBC table is a "database container" ...
    (microsoft.public.access.externaldata)
  • Re: Installing foxpro oledb on SQL 2005 64 bit
    ... None of the ole providers on my 64 bit SQL 2005 seem to suggest that they are ... Is there a 64 bit OLE DB provider for ODBC? ... Over time new data features have been added to FoxPro tables. ...
    (microsoft.public.data.oledb)
  • Re: import data from FoxPro database (ISIS)
    ... you've got Visual FoxPro files. ... The DBC table is a "database container" ... that don't have the new features are still compatible with ODBC. ... A typical OLE DB connection string is ...
    (microsoft.public.access.externaldata)
  • Re: Update FoxPro 6 table from ado
    ... Visual FoxPro would not like that at all. ... >> Be sure you have the latest ODBC drivers or OLE DB data provider, ... ODBC only works on tables in the VFP6 format and below. ...
    (microsoft.public.vb.database.ado)