ADO to Select INTO a VFP .dbf from an Access table

[David]

Can anyone help me with this?
I'm opening 2 ADO Connections.. one with the vfpoledb to open a visual foxpro
database container to expose a .dbf file the other using the access provider
to open an access database to expose a table. I want to use a Select...Into
string to insert all the the data from the access table into the fox pro
table..... What is my syntax...? Here is what I have:

cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB

cnn.Execute "Insert Into vfpfile Select * From accesstable;"
thanks in advance
D-

[Vikas]

Try this out:
"select * from accesstable insert into vfptable"

I haven't tried this out but it should work.

Bye





"David" <david@cdsconsulting.mailbox.as> wrote:
>
>Can anyone help me with this?
>I'm opening 2 ADO Connections.. one with the vfpoledb to open a visual foxpro
>database container to expose a .dbf file the other using the access provider
>to open an access database to expose a table. I want to use a Select...Into
>string to insert all the the data from the access table into the fox pro
>table..... What is my syntax...? Here is what I have:
>
> cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
> cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB
>
> cnn.Execute "Insert Into vfpfile Select * From accesstable;"
>thanks in advance
>D-

[David]

No, this doesn't work.... syntax error generated


"Vikas" <dvikas@sify.com> wrote:
>
>Try this out:
>"select * from accesstable insert into vfptable"
>
>I haven't tried this out but it should work.
>
>Bye
>
>
>
>
>
>"David" <david@cdsconsulting.mailbox.as> wrote:
>>
>>Can anyone help me with this?
>>I'm opening 2 ADO Connections.. one with the vfpoledb to open a visual
foxpro
>>database container to expose a .dbf file the other using the access provider
>>to open an access database to expose a table. I want to use a Select...Into
>>string to insert all the the data from the access table into the fox pro
>>table..... What is my syntax...? Here is what I have:
>>
>> cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
>> cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB
>>
>> cnn.Execute "Insert Into vfpfile Select * From accesstable;"
>>thanks in advance
>>D-
>

[David]

WIth a few changes this ALMOST works.... BUT all it does is create a new table
in my cnn2|rs2 called DavidPHCPROM.... it doesn't append the tblImport into
my cnn|rs table DavidPHCPROM that already exists....


Set cnn = New ADODB.Connection
Set cnn2 = New ADODB.Connection
Set rs = New ADODB.Recordset
Set rs2 = New ADODB.Recordset

strFox = dirDirectory.Path & "\" & dbc
strMDB = App.Path & "\" & mdb
strText = fle
strBilldate = dte

cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
rs.Open "DavidPHCPROM", cnn, adOpenDynamic, adLockOptimistic, adCmdText

cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB
rs2.Open "tblImport", cnn2, adOpenKeyset, adLockOptimistic, adCmdTable
cnn2.Execute "Update tblImport Set tblImport.BillDate = " & dte & ";"
cnn2.Execute "Select * Into [DavidPHCPROM] from tblImport;"


"Vikas" <dvikas@sify.com> wrote:
>
>Try this out:
>"select * from accesstable insert into vfptable"
>
>I haven't tried this out but it should work.
>
>Bye
>
>
>
>
>
>"David" <david@cdsconsulting.mailbox.as> wrote:
>>
>>Can anyone help me with this?
>>I'm opening 2 ADO Connections.. one with the vfpoledb to open a visual
foxpro
>>database container to expose a .dbf file the other using the access provider
>>to open an access database to expose a table. I want to use a Select...Into
>>string to insert all the the data from the access table into the fox pro
>>table..... What is my syntax...? Here is what I have:
>>
>> cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
>> cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB
>>
>> cnn.Execute "Insert Into vfpfile Select * From accesstable;"
>>thanks in advance
>>D-
>

[marc]

David

INSERT INTO [;DATABASE=database directory].table1 ( fields )
SELECT fields FROM table2;

You can also build the query in query designer, in Access 97, select append
query then right click where the tables are placed in the query design screen
and select properties. Under properties you will see source and destination
databases, just select the appropriate databases. I am sure it similiar
in 2000.

Marc

[David]

Thanks for your suggestion but I’m not using access to do this. I’m using
Visual Basic 6 to open 2 ADO connections:


cnn.Open "PROVIDER=vfpoledb;Data Source=" & strFox
cnn2.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & strMDB


then executing this query with the connection object:

cnn.Execute "Insert Into vfpfile Select * From accesstable;"

The connection objects don’t understand that the source data and the destination
table are in 2 different ADO connections….