ADO Delete Database Records

    This may be an odd request....


    I'm creating an Excel UserForm where the user can view, edit, and delete records they've entered.


    The following code is for Deleting a selected record, and it IS WORKING. But it seems TOO SIMPLE and I want to be sure it's correct and not leaving any loose ends in the Database file that could cause corruption later on....



    Thanks in advance.... :)

    Re: Vba Ado Delete Database Records


    Yes, it's too simple ;)


    #1 You should make sure that there exist "correct" values in the cells. If the cells are locked or hidden then You should use constant text variables instead which will improve the performance.


    #2 You should add an appropiate error handling including user friendly error messages. The error handling should also include any ADO generated errors.


    #4 Make sure that the connection is closed properly including scenarios where any error may occur.


    #5 Your present code imply that the selected value does exist in the DB. If that is not the case the code should give the end users information about it.


    #6 You use early binding and therefore make sure that the targeting machines has the referring version or later of the ADO External Library Object. If not then use late binding.


    #7 Spend some time in the advanced section here and You will find solutions to most of the above inputs.


    Quote


    Thanks in advance....


    You're welcome :)

    Re: Vba Ado Delete Database Records


    Thanks for the suggestions. I have a couple responses & questions....


    Quote from XL-Dennis


    #1 You should make sure that there exist "correct" values in the cells. If the cells are locked or hidden then You should use constant text variables instead which will improve the performance.


    What do you mean I should use constant text variables in cells that are locked or hiddent to improve performance? Could you expand on that a bit? Improve performance in what sense or function?


    Quote from XL-Dennis


    #4 Make sure that the connection is closed properly including scenarios where any error may occur.


    By just including an error handler after the connection is opened to close the connection in case of any errors?


    Quote from XL-Dennis


    #5 Your present code imply that the selected value does exist in the DB. If that is not the case the code should give the end users information about it.


    The list that it's pulling the "dbID" value from displays contents of the Database, so I can't see where an invalid "dbID" value would come in. Unless you can think of possible scenarios?


    Quote from XL-Dennis


    #6 You use early binding and therefore make sure that the targeting machines has the referring version or later of the ADO External Library Object. If not then use late binding.


    This Excel Application will be used by less than 10 users in a very controlled computer environment. All the users have the same version of Excel, same computers, OS, etc., so Binding should be unnecessary, correct?



    Thanks again for your input! :)

    Re: Vba Ado Delete Database Records


    postman2000


    #1

    Code
    Dim dbPath, dbName As String 


'Path & FileName to the Database File
dbPath = M.Range("G2").Value 
dbName = M.Range("G3").Value


    (A small remark: The first declared variable is interpreted by Excel as a Variant variable and not as a String variable so in general we should avoid to try to implicit declare variable's datatypes in that way.)


    The keypoint here is that if these two values are fixed, i e never changed or rarely changed then it's preferrable to use constant variables like the following:


    Code
    Const stPath As String = "g:\......"
Const stName As String = "Test.mdb"


    Of course they could be only one as well. When the code is executed then Excel will not be forced to spend some time to first instantiate the variable(s) and then populate the variable(s) with values as this is now done in "one go".


    From a maintance point of view it's easier to change the values in code and we will also get a better structure as code is separated from the user interface (ie the worksheet interface).


    #4 Error handling
    I had connection pooling in mind when writing some of the comments and I believe it may be too complicated to cover in a thread like this one. Except for that, the number of users is small number so please disregard that part.


    If You include both an error handling and one "exit place" in the code Your solution will be more bullet proof:



    #5 No but since it was not explicit mentioned in Your first post I thought I should point it out :)


    #6

    Quote


    ...in a very controlled computer environment...


    That's very good :)
    I believe that many developers would appreciate that very much ;)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login