Using a Select Query to build a MDB table from a Fox table

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: RichG (anonymous_at_discussions.microsoft.com)
Date: 05/27/04 

Date: Thu, 27 May 2004 12:46:02 -0700

I posted this message a couple of days ago on the VB forum, but no one has responded. Maybe they don't know
how to deal with a version 7 or 8 DBF??????
My ADO knowledge is limited and I'm not sure how a DBF table responds compared to an MDB table.
I can do this easily in DAO using a Jet MDB.
I was able to open a DBF in ADO with the following results

'Open the connection..................................

            con.ConnectionString = _
                "Data Source=" & dbfPath & ";" & _
                "Provider=""VFPOLEDB.1"";"
    
            con.CursorLocation = adUseClient
            con.Mode = adModeRead
            con.Open

    
'Get the names of the dbf's one at a time...................

            dbfName = Dir(dbfPath & "\*.dbf")
            While Not Len(dbfName) = 0

'Strip off the .DBF from the name...........

                dbfName = Left(dbfName, InStr(dbfName, ".") - 1)

             
'Open the BDF in a recordset.....................
'This works..................I can read a field name and value

                Set rst = con.Execute("SELECT * From " & dbfName)
                If Not rst.BOF Then
                    

'I would like to replicate the dbf in an MDB with the following but I get a syntax error.

                    con.Execute ("SELECT " & dbfName & ".* INTO " & dbfName & " " & _
                        "IN 'C:\TEMP\Temp\AccuPrint\vfl.mdb' " & _
                        "From " & dbfName & "")

This is a common SQL syntax but it fails here
It is a runtime error.
The error says it comes from Microsoft OLE DB Provider for Visual FoxPro.
Error Number -214721900

Even if I substitute a literal name for the variable it doesn't want
to work. Is this not a valid statement in ADO?
Thanks
Rich

Relevant Pages

  • Copying VFP 8 tables to an Access MDB
    ... I was able to do so but my ADO knowledge is limited. ... 'I would like to replicate the dbf in an MDB with the following but I get a syntax error. ... This is a common SQL syntax but it fails in ADO. ...
    (microsoft.public.vb.database)
  • Re: Using a Select Query to build a MDB table from a Fox table
    ... This is my original query statement...... ... that ADO will not be able to create a table in an MDB????????? ... So that I can't create an MDB table directly from a DBF. ...
    (microsoft.public.fox.helpwanted)
  • Re: Suggestoins for Local DB to use with Shareware
    ... It was with D3 - I was using the BDE at the time. ... As for my own experience I begin to evalute ADO with Access ... MDB files in D4 just before D5 appeared with built in ADO support what has ... local database engines and I will continue to use it in the future. ...
    (borland.public.delphi.non-technical)
  • Re: Erfahrungen mit Access ADP Projects, Meinungen dazu?
    ... > da man auch in einer MDB mit ADO arbeiten kann. ... man den SQL-Server arbeiten lässt und mit einer geeigneten Technik ... >> Trotzdem versuche ich nicht primär mit per ODBC eingebundenen Tabellen ...
    (microsoft.public.de.access.clientserver)
  • Re: Recordset ? defaults mdb->DAO, adp->ADO
    ... > Why does the form recordset seem to default to DAO in an MDB and ADO ... Because in an MDB, Access uses DAO to manage the data, while in an ADP ...
    (microsoft.public.access.forms)