New to ADO 1

[vmon]

Just starting to use ADO and I cannot find a way to delete * from tblName. I used to use Docmd.RunSql strSql How is this done with ADO.

thanks,
vmon

 

[jmeckley]

To execute a SQL command using ADO you need a connection string and the SQL Command.

I create a public function that I call whenever I execute a statement against the database.

Public Function cnnCWO() As ADODB.Connection
On Error GoTo Err_cnnCWO
        
    Set cnnCWO = New ADODB.Connection
    With cnnCWO
        .ConnectionString = "ODBC;Server=ServerName;DSN=NameofDSN;Database=NameofDatabase"
        .ConnectionTimeout = 30
        .CommandTimeout = 30
        .CursorLocation = adUseClient
        Call .Errors.Clear
        Call .Open
    End With
    
Exit_Function:
    Exit Function
Err_cnnCWO:
    MsgBox "An Error occured while connecting to the database.  Contact the DBA for assistance."
    Resume Exit_Function
End Function

Once you have a connection you can then execute commands against the database. The follow procedure inserts a record into a table after error checking

Private Sub cmdAddMemo_Click()
On Error GoTo cmdAddMemoErr
    Dim InsMemo As ADODB.Command
    Dim SqlStr As String
    Dim Memo As Object
    
    Set Memo = Me.txtMemo
    
        SqlStr = "exec sp_AddMemo_i '" & Memo.Value & "', '" & User & "'"
        Set InsMemo = New ADODB.Command
        InsMemo.ActiveConnection = cnnCWO
        InsMemo.CommandText = SqlStr
        InsMemo.Execute
        Set InsMemo = Nothing

Exit_Sub:
    Exit Sub
cmdAddMemoErr:
    MsgBox Err.Description
    Resume Exit_Sub
End Sub

Here is the execution of the code.
1. define my sql statement
2. create a new instance of a adodb command
3. define the active connection. This opens the connection to the database. I call the function cnnCWO so I do not have to define the connection every time I execute a statement.
4. I define the command text (sql state, or stored procedure) which I defined in step 2.
5. Execute the command.
6. remove the instance of the command from memory.

The ActiveConnection text in the connection function will be different for you depending on the type of database you are connecting and if it is password protected or not. This is the syntax to connect to SQL Server 2000. The connection string will be different if you are connecting to a different DB.

This procedure will work for Inserts, Updates and Deletes because they do not return record sets. The only thing that would need to change is the sql statement.

Select statments are slightly different. If you want that code just let me know. This example does not include error checking.

I hope this isn't to overwhelming. post back if you have questions.

Jason Meckley
Database Analyst
WITF