Results 1 to 5 of 5

Thread: How to validate an INSERT, after Executing

  1. #1

    Thread Starter
    Member
    Join Date
    Dec 2015
    Posts
    38

    How to validate an INSERT, after Executing

    Hi,

    I have


    Code:
    Set cn = CreateObject( "ADODB.Connection" )
    cn.Open "Driver={SQLite3 ODBC Driver};Database=c:\XXXXXXXX\XXXXXX\XXL_XX\MYPRODDB.db;"
    , and it connects fine, and runs queries.


    The INSERT is executing fine as well, based on a query from my sql tool afterwards.

    Code:
    THISTABLE_ins = "INSERT INTO [THISTABLE] SELECT * FROM [THATTRANSACTIONTABLE] WHERE  [Filename] = " & "'" & THISFileName_s & "'" & " AND [status] = 'F'"
    cn.Execute( THISTABLE_ins )


    , but my question is, how can we validate that the INSERT works from the cn object, immediately after executing? Do we need to do a query, or is there some property of the cn variable that we can poll?

  2. #2
    Frenzied Member
    Join Date
    May 2014
    Location
    Central Europe
    Posts
    1,372

    Re: How to validate an INSERT, after Executing

    cn.execute can return the number of rows affected in a second parameter.
    see here: https://stackoverflow.com/questions/...ba-ado-execute

    This should be >0 in your case and you should be able to check for that.
    On the other hand, a failed .execute usually results in a RTE as well, so no error but rows affected = 0 would mean that the SQL Statement completed but no records were inserted.

  3. #3

    Thread Starter
    Member
    Join Date
    Dec 2015
    Posts
    38

    Re: How to validate an INSERT, after Executing

    Thanks digital,

    That's what I needed.

    Regards,

  4. #4
    Fanatic Member VBAhack's Avatar
    Join Date
    Dec 2004
    Location
    Sector 000
    Posts
    617

    Re: How to validate an INSERT, after Executing

    Quote Originally Posted by VBExplorer12 View Post
    Code:
    Set cn = CreateObject( "ADODB.Connection" )
    cn.Open "Driver={SQLite3 ODBC Driver};Database=c:\XXXXXXXX\XXXXXX\XXL_XX\MYPRODDB.db;"
    If you don't mind my asking, where did you get the SQLite3 ODBC Driver?

    I get the following runtime error:

    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
    I'm trying this with VBA/Excel.
    Last edited by VBAhack; Dec 14th, 2023 at 03:27 PM.

  5. #5

    Thread Starter
    Member
    Join Date
    Dec 2015
    Posts
    38

    Re: How to validate an INSERT, after Executing

    https://www.ch-werner.de/sqliteodbc/

    Quote Originally Posted by VBAhack View Post
    If you don't mind my asking, where did you get the SQLite3 ODBC Driver?

    I get the following runtime error:



    I'm trying this with VBA/Excel.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width