Hi all, I've been trying to come up with a method of copying a set of
records from one table to a second table. I am using VB and ADO 2.5
and accessing Foxpro database tables. My connection string to the
source table is

makeCNNStr = "Provider=MSDASQL.1;Persist Security
Info=False;Mode=ReadWrite;Extended Properties=" & Chr(34) &
"DSN=Visual FoxPro Tables;UID=;SourceDB=" & cnnPath &
";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"
& Chr(34)

I open the connection and get a record set with "select * from
employee". The destination table is identical in structure to the
source in structure but does not have the same indexing (otherwise, I
would simply copy the table files).

I've been reading newsgroups on this problem and have seen the
suggestion to use Insert into employee select * from sourceTable. It
does not work with Foxpro tables. I get a syntax error no matter what
I've tried.

I've tried cloning. It does create a second recordset. I do not know
how to connect it to the destination data table (already created and
empty).

If anyone can help with the proper sequence of steps, it would be
hugely appreciated.

Mark Ryan
Safe Passage International

Hello Mark

Have you tried

SELECT SomeFieldA, SomeFieldB, etc INTO DestinationTable FROM SourceTable

Sukesh

";SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;D
eleted=Yes;"

Yes, I just tried this. The error message I get is:

-2147217900 Microsoft OLE DB Provider for ODBC Drivers [Microsoft][ODBC Visual FoxPro Driver]Syntax error.

Same deal as the Insert.

What I'm doing is I have 2 tables in the same folder. In Foxpro, the folder is the database and the tables are separate files. For ADO's purposes, the connection is to the folder. I specify the path to the folder in the connection string with variable cnnPath (see my previous posting). I then perform an .open with a string containing the sql string command.  This works, but the command does not. I don't know if it is not supported or I'm simply doing something wrong.

Is there another way of generating a clone fo the data and attaching it to another connection? If so, how do I specify the table to attach it too?

I can do a Set destRS = sourceRS.clone. But it exists as a unattached record set. Can I load the destRS with a SQL statement "select * from dest" and then attach the clone to it?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Mark

1) Use this connection string (ODBC) and see if the errors go away

Const FpCntString="DSN=Visual FoxPro Tables"   ' instead of MSADSQL

2) How do you execute the SQL statements with command object or connection
object or recordset object ?
    Better, you could post the code too to help find a solution.

Regards
Sukesh

Yes, I just tried this. The error message I get is:

-2147217900 Microsoft OLE DB Provider for ODBC Drivers [Microsoft][ODBC
Visual FoxPro Driver]Syntax error.

Same deal as the Insert.

What I'm doing is I have 2 tables in the same folder. In Foxpro, the folder
is the database and the tables are separate files. For ADO's purposes, the
connection is to the folder. I specify the path to the folder in the
connection string with variable cnnPath (see my previous posting). I then
perform an .open with a string containing the sql string command.  This
works, but the command does not. I don't know if it is not supported or I'm
simply doing something wrong.

Is there another way of generating a clone fo the data and attaching it to
another connection? If so, how do I specify the table to attach it too?

I can do a Set destRS = sourceRS.clone. But it exists as a unattached record
set. Can I load the destRS with a SQL statement "select * from dest" and
then attach the clone to it?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Many thanks to those who tried to help me with this problem. As far as I can tell from my research, the Foxpro driver or the connection I'm using does not allow the select and insert into commands. I have tried this from foxpro itself using sql and it does not work.

The solution that I adopted was to write a foxpro.dll that accepts a character string. This string contains one or more foxpro commands that are then executed. Thus, I can pass in "use employee; append from temp" and it does it and quickly too. I would have felt better if I could have done this in ADO since it would have been a more "correct" solution, but time constraints ya know...

I'll post the fox code below just in case it helps someone...

* save this code in a file vfpShell.prg (whatever) and create a project such as VFP.
* compile and build vfp.dll. Remember to include both vfp.dll and vfp.tlb in any
* installation. Otherwise it doesn't load the DLL correctly.
* To call, use for example: myo = createobject("vfp.vfpscript")
*                                                       commandStr = "use employee; copy to temp"
*                           myo.runscript commandStr
*
DEFINE CLASS vfpShell as CUSTOM OLEPUBLIC

        PROCEDURE runScript
                PARAMETERS commandStr

                * setup default runtime behaviors (whatever you want)
                set exclusive off
                set safety off
                set cpdialog off

                * convert all semicolons found to carriage returns
                * comandStr can be built as line1 + chr(13) + line2
                * or as line1;line2 (easier)
                local pos

                do while .t.
                        pos = at(";", commandStr)

                        if pos = 0
                                exit
                        endif

                        commandstr = stuff(commandstr, pos, 1, cr)
                enddo

                * process commandStr breaking it into separate commands
                local cr
                cr = chr(13)

                * trim and add cr to end if needed. Makes loop code easier to deal with
                local cStr
                commandStr = alltrim(commandStr)
                commandStr = commandStr + iif(right(commandStr, 1) = cr, "", cr)
                pos = at(cr,commandStr)

                if !empty(commandStr)

                        do while pos <> 0

                                * get command

                                do case

                                        case pos = 1
                                                cStr = ""

                                        otherwise
                                                cStr = left(commandStr, pos - 1)

                                endcase

                                * remove command from master string
                                commandStr = iif(pos = len(commandStr),"",substr(commandStr, pos + 1))

                                if !empty(cStr) then
                                        &cStr
                                endif

                                pos = at(cr, commandStr)
                        enddo

                endif

        ENDPROC

ENDDEFINE

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!