Run-time error '-2147217900(80040e14)'

Question

eshajoshi 0 Newbie Poster

11 Years Ago

Hi can andbody help me? Below is my code.

I get this message:
Run-time error '-2147217900(80040e14)':
Table 'PreampAutoDetect' already exists.

The error points to the line below with the stars around it.
stringSQL4 = ...

Please help me ...

Private Sub Command4_Click()
    ' Copy Tables from List1 to Database(s) in List2

    Dim db As DAO.Database
    Dim db2 As DAO.Database
    Dim tdf As DAO.TableDef
    Dim tdf2 As DAO.TableDef
    Dim rs As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Dim cn As ADODB.Connection                          ' Connection to the source database
    Dim Num_Of_Fields As Integer
    Dim stringSQL As String
    Dim stringSQL2 As String
    Dim stringSQL3 As String
    Dim stringSQL4 As String

    ' Open database with the standard tables
    Set db = OpenDatabase(Text1.text)

    ' Opening the selected database(s) in List3
    For Counter = 0 To List3.ListCount - 1
        Text3.text = List3.List(Counter)
        Set db2 = OpenDatabase(Text3.text)

        ' Iterating through the tables in List1
        For i = 0 To db.TableDefs.Count - 1
            Set tdf = db.TableDefs(i)

            ' Iterating through the tables in the database(s) in List2
            For j = 0 To db2.TableDefs.Count - 1
                Set tdf2 = db2.TableDefs(j)

                ' Filter all system tables
                If ((tdf2.Attributes And &H2) = 0 And (tdf2.Attributes And &H80000000) = 0) Then
                    ' Delete the tables from the database(s) in List2 that match the table names in List1
                    If (tdf2.Name = tdf.Name) Then
                        ' SQL query to delete the tables
                        stringSQL = "DROP TABLE " & tdf2.Name
                        db2.Execute stringSQL
                    End If
                End If
            Next
        Next
    Next Counter

    ' Open connection to the source database
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Text1.text & ";Persist Security Info=False"
    cn.Open

    ' Iterates through the tables in List1
    For i = 0 To db.TableDefs.Count - 1
        If ((db.TableDefs(i).Attributes And &H2) = 0 And (db.TableDefs(i).Attributes And &H80000000) = 0) Then
            Set tdf = db.TableDefs(i)
            Set rs = db.OpenRecordset(tdf.Name)
            Num_Of_Fields = rs.Fields.Count                                 ' Calculates number of fields
            Text4.text = tdf.Name                                           ' Stores the names of the tables in List1
            Text5.text = tdf.Name

            For j = 0 To List3.ListCount - 1
                Text3.text = List3.List(j)
                Set db2 = OpenDatabase(Text3.text)

                ' SQL query to create tables in DBs in List2 with names of tables in List1
                stringSQL2 = "CREATE TABLE " & Text4.text
                db2.Execute stringSQL2

                ' Adds the correct number of fields
                For x = 0 To Num_Of_Fields - 1
                    ' SQL query to add the fields from the tables in List1 to the tables in the DBs in List2
                    stringSQL3 = "ALTER TABLE " & Text4.text & " ADD COLUMN " & rs.Fields(x).Name & " TEXT(10); "
                    db2.Execute stringSQL3
                Next

                ' Filter all the system tables
                ' SQL query to copy all data from the tables in List1 to the DBs in List2
                For k = 0 To List2.ListCount - 1
                    For x = 0 To Num_Of_Fields - 1
                        If ((tdf.Attributes And &H2) = 0 And (tdf.Attributes And &H80000000) = 0) Then
                            **** stringSQL4 = "SELECT * INTO " & Text5.text & " FROM " & Text5.text & " IN '" & Text3.text & "';" ****
                            cn.Execute stringSQL4
                        End If
                    Next
                Next
            Next
        End If
    Next

    ' Close the connection and databases
    cn.Close
    Set cn = Nothing

    db.Close
    db2.Close

Thank you.

visual-basic

2 Contributors
9 Replies
2K Views
5 Days Discussion Span
Latest Post 11 Years Ago Latest Post by ss125

ss125 18 Posting Whiz in Training

11 Years Ago

stringSQL4 = "SELECT * INTO " & Text5.text & " FROM " & Text5.text & " IN '" & Text3.text & "';" ****

Explanation

The select * into table1 from table2 where 1=1 creates table1 and inserts the values of table2 in them. So, if the table is already created that statement would give an error.

The insert into table1 select * from table2 only inserts the values of table2 in table1.

Solution

"And I also noted that you are copying from same table the select * into allows two different tables."

Thats the reason for the error Table already exists

Hope this helps you...

Have a happy coding...:-D

ss125 18 Posting Whiz in Training

11 Years Ago

stringSQL4 = "INSERT INTO " & tdf2.Name & " SELECT * FROM " & tdf.Name & "';"

there is error in you sql string

"';"

the ' will not come.
sort that out and execute...

Have a hapie coding...:-D

ss125 18 Posting Whiz in Training

11 Years Ago

stringSQL4 = "INSERT INTO " & tdf2.Name & " SELECT " & tdf.Fields(z) & " FROM " & tdf.Name & "'"

replace the string by

stringSQL4 = "INSERT INTO " & tdf2.Name & " SELECT " & tdf.Fields(z) & " FROM " & tdf.Name & ""

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

eshajoshi 0 Newbie Poster · Answer 1 · 2013-09-06T16:43:21+00:00

Hi ss125,

Thank you for your help. After reading your comments, I understand what I was doing wrong. If you please take a look down below at my new code, I changed the part where I am trying to copy the data from a table into a different table but now I seem to be getting another error when establishing destination fields (starred).

Private Sub Command4_Click()
    ' Copy Tables from List1 to Database(s) in List2

    ' Open database with the standard tables
    Set db = OpenDatabase(Text1.text)

    ' Opening the selected database(s) in List3
    For Counter = 0 To List3.ListCount - 1
        Text3.text = List3.List(Counter)
        Set db2 = OpenDatabase(Text3.text)

        ' Iterating through the tables in List1
        For i = 0 To db.TableDefs.Count - 1
            Set tdf = db.TableDefs(i)

            ' Iterating through the tables in the database(s) in List2
            For j = 0 To db2.TableDefs.Count - 1
                Set tdf2 = db2.TableDefs(j)

                ' Filter all system tables
                If ((tdf2.Attributes And &H2) = 0 And (tdf2.Attributes And &H80000000) = 0) Then
                    ' Delete the tables from the database(s) in List2 that match the table names in List1
                    If (tdf2.Name = tdf.Name) Then
                        ' SQL query to delete the tables
                        stringSQL = "DROP TABLE " & tdf2.Name
                        db2.Execute stringSQL
                    End If
                End If
            Next
        Next
    Next Counter

    ' Open connection to the source database
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Text1.text & ";Persist Security Info=False"
    cn.Open

    ' Iterates through the tables in List1
    For i = 0 To db.TableDefs.Count - 1
        If ((db.TableDefs(i).Attributes And &H2) = 0 And (db.TableDefs(i).Attributes And &H80000000) = 0) Then
            Set tdf = db.TableDefs(i)
            Set rs = db.OpenRecordset(tdf.Name)
            Num_Of_Fields = rs.Fields.Count                                 ' Calculates number of fields
            Text4.text = tdf.Name                                           ' Stores the names of the tables in List1

            For j = 0 To List3.ListCount - 1
                Text3.text = List3.List(j)
                Set db2 = OpenDatabase(Text3.text)

                ' SQL query to create tables in DBs in List2 with names of tables in List1
                stringSQL2 = "CREATE TABLE " & Text4.text
                db2.Execute stringSQL2

                ' Adds the correct number of fields
                For x = 0 To Num_Of_Fields - 1
                    ' SQL query to add the fields from the tables in List1 to the tables in the DBs in List2
                    **** stringSQL3 = "ALTER TABLE " & Text4.text & " ADD COLUMN " & rs.Fields(x).Name & " TEXT(10); " ****
                    **** db2.Execute stringSQL3 ****
                Next

                ' Filter all the system tables
                ' SQL query to copy all data from the tables in List1 to the DBs in List2
                For k = 0 To List2.ListCount - 1
                    For x = 0 To Num_Of_Fields - 1
                        If ((tdf.Attributes And &H2) = 0 And (tdf.Attributes And &H80000000) = 0) Then
                            'stringSQL4 = "SELECT * INTO " & Text4.text & " FROM " & Text4.text & " IN '" & Text3.text & "';"
                            'stringSQL4 = "SELECT * FROM " & Text4.text & " IN '" & Text3.text & "';"
                            stringSQL4 = "INSERT INTO " & Text4.text & " SELECT * FROM " & tdf.Name & "';"
                            cn.Execute stringSQL4
                        End If
                    Next
                Next

            Next
        End If
    Next

    ' Close the connection and databases
    cn.Close
    Set cn = Nothing

    db.Close
    db2.Close

eshajoshi 0 Newbie Poster · Answer 2 · 2013-09-06T16:50:31+00:00

At the starred lines, the error I now get is:

"syntax error in field definition"

eshajoshi 0 Newbie Poster · Answer 3 · 2013-09-06T17:44:04+00:00

Hi ss125,

Please ignore my last two posts. I seem to have a different issue now that I am unable to save the name of the new table created.

With my code (below), my program crashes during runtime. Do you have any advice as to properly save the name of the table and copy the data onto this new table?

When I comment out the block of code that copies the data over, the program runs well in the sense that a new table is created in the database and the table has the correct fields.

Thanks for all your help!

 ' Open connection to the source database
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Text1.text & ";Persist Security Info=False"
    cn.Open

    ' Iterates through the tables in List1
    For i = 0 To db.TableDefs.Count - 1
        If ((db.TableDefs(i).Attributes And &H2) = 0 And (db.TableDefs(i).Attributes And &H80000000) = 0) Then
            Set tdf = db.TableDefs(i)
            Set rs = db.OpenRecordset(tdf.Name)
            Num_Of_Fields = rs.Fields.Count                                 ' Calculates number of fields
            'Text4.text = tdf.Name                                           ' Stores the names of the tables in List1
            tableName = tdf.Name

            For j = 0 To List3.ListCount - 1
                Text3.text = List3.List(j)
                Set db2 = OpenDatabase(Text3.text)

                ' SQL query to create tables in DBs in List2 with names of tables in List1
                stringSQL2 = "CREATE TABLE " & tableName
                db2.Execute stringSQL2

                Set tdf2 = db2.TableDefs(tableName)

                ' Adds the correct number of fields
                For x = 0 To Num_Of_Fields - 1
                    ' SQL query to add the fields from the tables in List1 to the tables in the DBs in List2
                    stringSQL3 = "ALTER TABLE " & tdf2.Name & " ADD COLUMN " & rs.Fields(x).Name & " TEXT(10); "
                    db2.Execute stringSQL3
                Next

                ' Filter all the system tables
                ' SQL query to copy all data from the tables in List1 to the DBs in List2
                For k = 0 To List2.ListCount - 1
                    For x = 0 To Num_Of_Fields - 1
                        If ((tdf.Attributes And &H2) = 0 And (tdf.Attributes And &H80000000) = 0) Then
                            stringSQL4 = "INSERT INTO " & tdf2.Name & " SELECT * FROM " & tdf.Name & "';"
                            cn.Execute stringSQL4
                        End If
                    Next
                Next

            Next
        End If
    Next

    ' Close the connection and databases
    cn.Close
    Set cn = Nothing

    db.Close
    db2.Close

eshajoshi 0 Newbie Poster · Answer 4 · 2013-09-09T16:25:02+00:00

Hi ss125,

Should the right approach be:

For y = 0 To db2.TableDefs.Count - 1

                    ' FIND THE INDEX OF THE NEW ADDED TABLE IN THE DESTINATION DATABASE
                    ' THAT INDEX WILL GO IN THE PARENTHESES BELOW
                    If (db2.TableDefs(y).Name = db.TableDefs(i).Name) Then

                        Set tdf2 = db2.TableDefs(y)

                        ' Filter all the system tables
                        ' SQL query to copy all data from the tables in List1 to the DBs in List2
                        For k = 0 To List2.ListCount - 1
                            For z = 0 To Num_Of_Fields - 1
                                If ((tdf.Attributes And &H2) = 0 And (tdf.Attributes And &H80000000) = 0) Then
                                    stringSQL4 = "INSERT INTO " & tdf2.Name & " SELECT " & tdf.Fields(z) & " FROM " & tdf.Name & "'"
                                    cn.Execute stringSQL4
                                End If
                            Next
                        Next
                    End If
                Next

I still get an error but am I headed in the right direction by splitting the SELECT and the FROM?

eshajoshi 0 Newbie Poster · Answer 5 · 2013-09-10T15:49:24+00:00

Is it possible that the line of code you posted has incorrect syntax?

I am using this line of code in my program and I am getting the error

"Invalid Operation".

ss125 18 Posting Whiz in Training · Answer 6 · 2013-09-11T03:30:46+00:00

yup sorry for that..

Semoi-colon missing

Sorry for that my friend...