Executing Programmatically the Update Query in ODBC

[keirnus]

Hello,

It's been a long time since I posted here.

I bumped and actually kept bumping on this ODBC stuff.

I created a DSN and connected to an external mdb using ODBC.
What I did was SELECT the data and it is good.

Now, I want to UPDATE some data. After hours of searching,
I just can't make it work.

Before, my DB is within my Access application.
So I used this:

    Dim sSQLUpdate As String
 
    sSQLUpdate = _
                    "UPDATE TableName " & _
                    "SET ColumnStr = 'NewStr' " & _
                    "WHERE ColumnNum > 100"
 
    DoCmd.RunSQL sSQLUpdate

* This works fine.

When I started using external DB thru ODBC,
I was able to do SELECT.......but can't UPDATE!
(Haven't tried DELETE yet but I think I can't as well)

I badly need your intelligent advice.

 

[namliam]

Check that the user you use to connect to the odbc source has update / delete rights.

Also make sure your target table has primary key and stuff setup, some databases require that otherwize no updates can be done.

Also instead of ....

    sSQLUpdate = _
                    "UPDATE TableName " & _
                    "SET ColumnStr = 'NewStr' " & _
                    "WHERE ColumnNum > 100"

consider doing this:

    sSQLUpdate = ""
    sSQLUpdate = sSQLUpdate & " UPDATE TableName " 
    sSQLUpdate = sSQLUpdate & " SET ColumnStr = 'NewStr' " 
    sSQLUpdate = sSQLUpdate & " WHERE ColumnNum > 100"

Advantage is you dont run into problems when you have many lines, thus many _ ....
The contination (_) is limited in the number of sequential times you can use it.

 

[keirnus]

Check that the user you use to connect to the odbc source has update / delete rights.

Also make sure your target table has primary key and stuff setup, some databases require that otherwize no updates can be done.

Also instead of ....

    sSQLUpdate = _
                    "UPDATE TableName " & _
                    "SET ColumnStr = 'NewStr' " & _
                    "WHERE ColumnNum > 100"

consider doing this:

    sSQLUpdate = ""
    sSQLUpdate = sSQLUpdate & " UPDATE TableName " 
    sSQLUpdate = sSQLUpdate & " SET ColumnStr = 'NewStr' " 
    sSQLUpdate = sSQLUpdate & " WHERE ColumnNum > 100"

Advantage is you dont run into problems when you have many lines, thus many _ ....
The contination (_) is limited in the number of sequential times you can use it.

Thanks for the help, mailman.

I created a System DSN and giving All Permission to Everyone.
So the problem was not the access rights of the user.

The problem was that my "rs" was already closed
if I use UPDATE, DELETE and INSERT.
So, I should not close it programmatically.
I don't know the reason yet.
(Btw, there's no prob with SELECT)

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
 
    Dim sSQLUpdate As String
 
    sSQLUpdate = ""
    sSQLUpdate = sSQLUpdate & " UPDATE TableName " 
    sSQLUpdate = sSQLUpdate & " SET ColumnStr = 'NewStr' " 
    sSQLUpdate = sSQLUpdate & " WHERE ColumnNum > 100"    
 
    'Setting ADO ODBC Connection here
    Call SetADODBConn(cn, rs, sSQLUpdate) 
 
    'rs.Close '<<< THIS WAS MY PROBLEM 
    'commenting this line for UPDATE, DELETE and INSERT
    'uncommented for SELECT
 
    cn.Close
 
    Set rs = Nothing
    Set cn = Nothing

To anyone who can bring light to my wonder,
it would be helpful to answer and post it here.

On the other hand, I followed ur style when creating SQL Queries
to avoid long queries coz I experienced too long queries before.
Thanks for the tip, mailman. Adding reputation now.

 

[namliam]

Thanks for the help, mailman.

I created a System DSN and giving All Permission to Everyone.
So the problem was not the access rights of the user.

The DSN doesnt give any permissions, your database does in conjuction with the user/pw combination you use in the DSN.

The problem was that my "rs" was already closed
if I use UPDATE, DELETE and INSERT.
So, I should not close it programmatically.
I don't know the reason yet.
(Btw, there's no prob with SELECT)

you dont even cannot use update/delete/insert commands on a "rs", these commands go to the database and affect tables directly, not "rs"

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

I am totaly unknowledgable about ADO thus cannot really help in that regard. I am however sure that considering this code, you are not even filling rs... thus this cannot be right...

Again, update/delete/insert are executed on database/table level not on an existing rs...

 

[keirnus]

Actually, my configuration is done in SetADODBConn() Function
where the rs properties are filled.

Here are the contents:

Public Sub SetADODBConn(ByRef cn As ADODB.Connection, ByRef rs As ADODB.Recordset, ByVal sSQLStr As String)
   
    'Create a new ADO Connection object
    Set cn = New ADODB.Connection
    
    With cn
        .Provider = "MSDASQL"
        .Properties("Data Source").Value = "ODBC;DSN=DSN_Name;UID=sa;pwd="
        .Open
    End With
    
    'Create an instance of the ADO Recordset class, and
    'set its properties
    Set rs = New ADODB.Recordset
    
    If Trim(sSQLStr) <> "" Then
        With rs
            Set .ActiveConnection = cn
            .Source = Trim(sSQLStr)
            .LockType = adLockOptimistic
            .CursorType = adOpenKeyset
            .CursorLocation = adUseClient
            .Open
        End With
    End If
    
End Sub

 

[namliam]

Like I said....
I am totaly unknowledgable about ADO

How I would solve this is to make a "normal" sql pass-trough-action-query make some dummy sql in it...
Then in the code simply write the action SQL into the query and execute it.

Something like...
Currentdb.querydefs("odbcqueryname").sql = sSQLUpdate
Currendb.execute ("odbcqueryname")

 

[DCrake]

When you create the ADODB instance and establish the paramters etc. These should be left in an open instance thoughout the lifespan of the session

This should be done when the session is started.

using your SetADODBConn sub

Then create new ADODB.Recordsets

Open (cn, rs, sSQLUpdate) as a select type and enumerate through the recordset performing the conditional updates.

In completetion you can the use the rs.Close etc.

Do not have a correct code snippet on this machine otherwise could have provided an accruate syntax for you.

David