Insert into ... error msg

[Jack Hudson]

Trying to move data from Visual FoxPro free table to MSA2003 table using
following code snippet:

Set conCnxn = New ADODB.Connection
strCnxn = "Provider = 'VFPOLEDB.1';" & _
"Data Source = 'z:\';" 'z: is mapped VFP database

conCnxn.Open strCnxn

Set rstSales = New ADODB.Recordset
strSQLSales = "INSERT INTO tblSalesHdr " & _
"SELECT * INTO tblSalesHdr FROM saleshdr " & _
"WHERE saleshdr.shd_trnnum > 1000 and saleshdr.shd_trnnum <1050"
rstSales.Open strSQLSales, conCnxn, adOpenStatic, adLockReadOnly,
adCmdText

rstSales.Open errors with msg "... tblSalesHdr.dbf not found"

I've searched MSDN and zillions of googled pages without hint as to why.
MSDN intimates that the ADO statement will figure out that tblSalesHdr is in
the current db (not a ADP), but that doesn't seem to be the case.

Any thoughts?

Thanks, Jack

 

[Jack Hudson]

Oops. strSQLSales below should read:

strSQLSales = "INSERT INTO tblSalesHdr " & _
"SELECT * FROM saleshdr " & _
"WHERE saleshdr.shd_trnnum > 1000 and saleshdr.shd_trnnum
<1050"

Sorry.

Jack

 

[Franck]

INSERT INTO tblSalesHdr SELECT * INTO tblSalesHdr FROM saleshdr WHERE
saleshdr.shd_trnnum > 1000 and saleshdr.shd_trnnum<1050

i think what you want is put selected values from table 1 and put them
in table 2

so your request should be
SELECT * INTO tblSalesHdr FROM saleshdr WHERE shd_trnnum > 1000 AND
shd_trnnum<1050

You actually dont need a insert into, the select * into table 1 mean
you want copy to that table
after you put the source and in the end you can add the restriction
(where) of the source table

 

[Franck]

WEll i personnaly never query a FoxPro database before but i know that
tables are files, for your ado connection i cant help you but i may be
the second problem
try this site to verify if you put everything i actually use it alot to
remeber connection strings:

http://www.connectionstrings.com/
for fox pro it's that link
http://www.connectionstrings.com/?carrier=dbffoxpro

according to the web site it should be :
instead of this
"Provider = 'VFPOLEDB.1';Data Source = 'z:\';"
this
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\;Extended
Properties=dBASE IV;"
or
"Driver={Microsoft dBASE Driver (*.dbf)};DriverID=277;Dbq=z:\;"

 

[Jack Hudson]

Thanks but I've figured it out I think. After I get the thing completely
tested I'll reply with the results. It seems that using ADO with Access
ain't as simple as the press suggests.

Regards, Jack