Adding/deleting/updating records using ADO

[hbrems]

Dear all,

my application consists of a back end & front end. From the front end I want to do some simple SQL operations on the back end.

First thing i would do is connect to the back end like this:

' connect to back end
cnnbe.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\Database BE.mdb;Jet OLEDB:Database Password=Porky;"

Then I'd like to pass a message like:

"Delete * from tblapples"

In the same way i'd like to update some records and insert new ones.

I do know the SQL statements for these commands, but I don't know how to pass them via ADO to a back end database.

Please note that the tables cannot be linked, that is a requirement.

Kind regards,
Hans B.

 

[pbaldy]

Try opening a recordset on that SQL statement (instead of a SELECT statement).

 

[georgedwilkinson]

One way of doing it is using the ADO Command object. It is quite flexible and can take a sql string as an argument.

Try:
Dim mycmd as ADO.Command

set mycmd as New ADODB.Command
... 'Do your other stuff here, like getting a connection.
mycmd.ActiveConnection = cnnbe 'From your post, above.

to create the object and connect to the db. Then:
mycmd.CommandText = HansMagicalSqlText1
mycmd.Execute

mycmd.CommandText = HansMagicalSqlText2
mycmd.Execute
...
Ad infinitum
...
mycmd.Close
Set mycmd = Nothing

There is so much more. Look at help on the Command object.

Also, you might want to check out the "ADODB.Recordset" object.