How to avoid the Runtime Error '2714', There is already an object named 'tblBrMiss' in the database ?

Posted on 2007-10-12
Last Modified: 2012-05-05
I am developing an Access application using an ADP file with Access as the front end and SQL Server as the back end database.

In the Access application I use VBA code. In the following IF statement, I perform 2 successive
SQL Statements. When the compiler executes the 2nd SQL statement:

I get a Runtime Error '2714'.
There is already an object named 'tblBrMiss' in the database ?
The first SQL statement is a SELECT INTO.
The second SQL statement is INSERT INTO    SELECT FROM.
If Me.Frame472.Value = 1 Then
      str_sql = "SELECT distinct Left(tblTSpringSpLNExp.[CPS Account Number],3) AS NameOfTheColumn INTO dbo.tblBrMiss from tblTSpringSpLNExp" & _
      " where Left(tblTSpringSpLNExp.[CPS Account Number],3) not in " & _
      "(SELECT branch from tblPSEmail) order by Left(tblTSpringSpLNExp.[CPS Account Number],3)"
      DoCmd.RunSQL (str_sql)

      str_slq = "INSERT INTO dbo.tblBrMiss SELECT distinct BRANCH from dbo.tblPSEmail where emailadd Is Null or costcenter is Null"
      DoCmd.RunSQL (str_sql)
End If
Question by:zimmer9
    LVL 17

    Accepted Solution

    You have misnamed the variable that holds your SQL command for the second statement...

    str_slq = "INSERT  

    Should be

    str_sql = "INSERT

    Author Comment

    Good catch.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Introduction In my previous article ( I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
    Viewers will learn how the fundamental information of how to create a table.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now