connect excel and vfp

[yostyid]

I am having trouble connecting to my odbc foxpro database to create cursors or views. here is (basically) the code:

public cn as database
public strBOM as string
strBOM = "create view bomsql as SELECT * from inventor;"
set cn = opendatabase("db", dbDriverNoPrompt, False, "ODBC;DSN=db;&quot
on error resume next
cn.execute strBOM

If Err.Number <> 0 Then 'ERROR CHECKING
Msg = &quot;Error # &quot; & str(Err.Number) & &quot; was generated by &quot; _
& Err.Source & Chr(13) & Err.Description
response = MsgBox(Msg, vbOKOnly, &quot;Error BOM Table&quot;, , Err.Description)
If response = vbOK Then
End
End If
Exit Sub
End If
On Error Resume Next

..I have a watch on msg that returns..

&quot;Error # 3290 was generated by DAO.Database
Syntax error in CREATE TABLE statement.&quot;

..when I paste the query into vfp it runs fine, but for some reason it wont work from excel. Any ideas?

 

[alvechurchdata]

It's a while since I used DAO but I don't recollect using views. I always used recordsets as in :

dim rst as DAO.recordset

set rst = dbs.openrecordset(strBOM, dbOpenDynaSet)

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[yostyid]

the problem is that this isnt exactly the recordset that I want. I have made a few changes. This view was only a test to try and see if I can write to the vfp container. Essentially I have created a procedure in vfp. the procedure will create 5 cursors (rather than views) 4 of the cursors are used to fill the main(5th) cursor that will be queried for a recordset. The problem is that I cannot run the cn.execute successfully. Now with this code..

dim cn as Database
Set cn = OpenDatabase(&quot;db&quot;, dbDriverNoPrompt, True, &quot;ODBC;DSN=db;&quot
On Error Resume Next

cn.Execute &quot;do inv_update;&quot;

(procedure inv_update just runs the 5 select into cursor statments.)

..returns an error of..

&quot;Error # 3065 was generated by DAO.Database
Cannot execute a select query.&quot;

..so can the cn.execute tell from the vfp procedure call that it will run a 'select into cursor' query? Is there a better way to be able to generate these cursors than calling them in a procedure? And finally, would ado be a better/faster choice than dao?