SQL Database Problem

[richy]

Connected to a database using the following code:

Set dbase = Workspaces(0).OpenDatabase(globaldir + "diary")

This connects fine. I can then select some values from this database and one of the tables by doing the following:

sqlstring = "SELECT * FROM tbldescrip"
Set rs = dbase.OpenRecordset(sqlstring, dbOpenDynaset)

and this works fine. The problem occurs when I try to use the insert into sql function.
I'm currently doing the following:

sqlstring = "INSERT INTO tbldescrip VALUES ('1st value', '2nd value', '3rd value', '4th value')"
Set rs = dbase.OpenRecordset(sqlstring, dbOpenDynaset)

and I'm told that it's an invalid operation. I've tested all my rights, and columns in my database, and they are fine.


Please help.

[RIVES]

Instead of using Recordset, use command

[richy]

there is no command from what i can see in the vb auto complete drop down list.

[bar]

Rives,
richy is using DAO there is no command object there.

richy,

I've never tried using it like that. Why don't you try the following after opening the recordset with either a SELECT statement of just the table name:

Recordset.AddNew
Recordset.Fields("Field1") = Value1
Recordset.Fields("Field2") = Value2 etc....

Recordset.Update

Hope this helps.

[richy]

the reason is that we are making it faster and running it completely through code rather than using the actual control. To do the code that you see above, you don't even use the data control.

[Gary Juleff]

Try using the execute method like so

dbase.execute sqlstring

[richy]

Cheers for everyones help.
I suddenly realised when reading Gary Juleffs post that you can't set a recordset to an insert statement, so in other words the line went from:

Set rs = dbase.OpenRecordset(sqlstring, dbOpenDynaset)

to

dbase.OpenRecordset(sqlstring, dbOpenDynaset)