Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2005
    Posts
    3

    Question Unanswered: VBA in Access: Check for Existing Table and Field before Running Query

    Maybe this is not a great question, but it is sure giving me a lot of grief.

    I have created a form to run a few queries and modify tables so the user only has to click a button and wait for everything to finish up.

    In my code I need to check for the Existence of a table and if it exists then jump ahead in the code otherwise it needs to create the table before continuing. My code looks like this:

    'Check for existing table
    On Error Resume Next
    If CurrentDb.TableDefs("tblPatient_Info").Name > "" Then GoTo tblPatient_Info_Exists
    On Error GoTo Err_Command19_Click
    'the next query creates the table I need
    DoCmd.OpenQuery "ExcelStrip_Patient_Info"

    tblPatient_Info_Exists:
    <more code>

    The problem is that even if the table "tblPatient_Info" DOES NOT EXIST, the If Then GoTo statement still evaluates to TRUE and the jump to "tblPatient_Info_Exists" is made.

    If the table doesn't exist then the program should throw an error and then pickup on the next line (On Error Resume Next).

    This worked fine for a couple of days and then all of a sudden it stopped working. What could I have done. I didn't change the code at all, so I'm assuming I must have done something else, but I can't for the life me figure it out.

    Any help is greatly appreciated. Thanks.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Are you creating and wiping out the table every time you run the program!!?

    If not, why do you need to re-invent the wheel?

    Sam

  3. #3
    Join Date
    Nov 2005
    Posts
    3
    Quote Originally Posted by Sam Landy
    Are you creating and wiping out the table every time you run the program!!?

    If not, why do you need to re-invent the wheel?

    Sam
    The process that the program is using is hard to explain, but I'll try to give you a quick summary so you'll understand what I'm trying to do.

    There are two main buttons. The first button will prompt the user for a text file to import. The Second button, which is the one I'm having trouble with, will strip certain information from the imported data and will but this information in a new table. After the information is stripped out to this new table, a few queries will be run on it that will check for certain errors that are common in the imported data. The errors are complex and require that the user be able to see the information and manual correct it. If there is an error, then the procedure will stop and the user must correct the problem and then **click the 2nd button AGAIN**, only this time instead of stripping the information from the imported data again, it needs to run the "error checking" on the information that was stripped out after the 1st click of the button because the user is editing the information in that table, so it can't be recreated or the work spent fixing the errors will be erased and the errors will return.

    The requirement for this particular program requires that only the two buttons be used "for ease of use". Too many buttons confuse the user I guess, or I would just add another button to run the error-checking, but that is not what the boss wants.

    So I'm stuck trying to find a way to check if the table exists and procede through the code accordingly.

    Clear as mud now?

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You could use a recordset instead...

    The other alternative is looping through the AllTables collection and seeing if the name exists.

    There's an example in the help file if you search for "AllTables Collection"
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Quote Originally Posted by Teddy
    You could use a recordset instead...
    Or better yet, an array. Simply put all your table info into a two-dimentional array, wipe out the table information the way you do now, and do all your error checking and error correcting in the array. After all corrections are made, put the array information back into the table.

    The truth be told, there are both advantages and disadvantages to using either an array or a recordset. Take your pick.

    Sam

  6. #6
    Join Date
    Nov 2005
    Posts
    3
    Thanks for all the great ideas. I have found a simple way to correct the code I was using so that it produces the results I want.

    I'm not sure I follow what you mean about using a recordset, or array to keep up with table information. I simply was trying to find a way to check for the existence of a certain table and then act accordingly.

    I'm not very experienced in VBA so I may be going about things the wrong way, but at least things seem to be working now, so I'll probably just stick with my simplistic code.

    The reason my code was not working before was that I was using "On Error Resume Next" where the statement that through the error was in an If Then Goto statement, so the "Next" line after the error was thrown was the 'GoTo' statement that I wanted to execute if there were no errors. So whether there was an error or not it would take the "jump" regardless.

    I just pulled the statement where I expected the error out of the If Then Goto statement and things seem to work okay now.

    Thanks for the ideas.

  7. #7
    Join Date
    Jan 2008
    Posts
    1

    Smile VBA in Access: Check for Existing Table and Field before Running Query

    The following code should do it: Hope this helps.

    Dim db As Database, tdf As String
    Dim msg, Style, Title, Response, MyString As String

    Set db = CurrentDb

    tdf = "tblPatient_Info"

    'Check for existing table
    If tdf <> "" Then
    msg = "tblPatient_Info already Exists. Do you wish to replace it?" ' Define message.
    Style = vbYesNo + vbCritical + vbDefaultButton2 ' Define buttons.
    Title = "MsgBox Demonstration" ' Define title.
    Response = MsgBox(msg, Style, Title)
    If Response = vbYes Then ' User chose Yes.
    MyString = "Yes" ' Deletes the table specified.
    'the next query creates the table I need
    DoCmd.OpenQuery "ExcelStrip_Patient_Info"
    MyString = "No"
    End If
    GoTo LineEnd
    End If

    LineEnd:

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Being a year and a half ago, I'd say the problem is well and truly solved by now ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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