I can Insert table from Old_Table_Database to New_Table_Database
with this code.

Dim cmd5 As New OleDbCommand
        Dim cmd6 As New OleDbCommand
        cmd6 = New OleDbCommand("select * from  " & "Product ", OldConn)
        Dim dr6 As OleDbDataReader
        dr6 = cmd6.ExecuteReader
        For j As Integer = 0 To dr6.FieldCount - 1
            If dr6.HasRows Then
                While dr6.Read
                    cmd5 = NewConn.CreateCommand
                    cmd5.CommandText = "Insert into Product(P1,P2,P3,P4,P5,P6,P7,P8,P9,P10,P11,P12,P13,P14,P15) values " & _
        "('"  & dr6(1) & "','" & dr6(2) & "','" & dr6(3) & "','" & dr6(4) & "','" & dr6(5) & "','" & dr6(6) & "','" & dr6(7) &  "','" & dr6(8) &  "','" & dr6(9) & "','" & dr6(10) & "','" & dr6(11) & "','" & dr6(12) & "','" & dr6(13) & "','" & dr6(14) & "','" & dr6(15) &"')"
                    cmd5.ExecuteNonQuery()
                End While
            End If
        Next j

Then if I want copy 10 table this make me grazy
from cmd1 to cmd20
Can anyone help me to make my CopyTableToNewDatabase as simple as it can?
ex. like this

CopyTableToNewDatabase OldConn, "Product", newConn

tx

Why not use this >>>

"SELECT tbl_Inventory.* INTO tbl_Inventory IN 'C:\Documents and Settings\User\Desktop\dbName.accdb' FROM tbl_Inventory;'"

You could create a sub or function that receives the table names to copy all your tables to the new database.

Syntax error in INSERT INTO statement.

Could youpost your sql?

Change the file extension from .accdb to .mdb.

I just ran this and it worked with not problem.

Private Sub MakeTable(ByVal newDBPath As String)


    Dim sql As String = "SELECT Inventory_1.* INTO Inventory_1 IN " & newDBPath & " FROM Inventory_1;"
    dbConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                                 dbPath & ";") 'Connection to originating database

    Dim cmd As New OleDbCommand

    With cmd
      .Connection = dbConn
      .CommandType = CommandType.Text
      .CommandText = sql
    End With

    Try
      dbConn.Open()
      cmd.ExecuteNonQuery()
    Catch ex As Exception
      MsgBox(ex.ToString)
    Finally
      dbConn.Close()
    End Try

  End Sub
Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFIle & ";" & _
      "Persist Security Info=True;" & _
      "Jet OLEDB:Database Password=123456"
        Call ConnectAccess()
        Dim NewConn As New OleDbConnection(strConn)
        Dim OldConn As New OleDbConnection(str)
        OldConn.Open()
        NewConn.Open()
        Dim cmd0 = New OleDbCommand("INSERT * Product.* INTO [MS Access;DATABASE=" & sFIle & ";Jet OLEDB:Database Password=123456;].[Product] from [Product]", OldConn)
        Dim cmd1 = New OleDbCommand("INSERT * Customer.* INTO [MS Access;DATABASE=" & sFIle & ";Jet OLEDB:Database Password=123456;].[Customer] from [Customer]", OldConn)
        Dim cmd2 = New OleDbCommand("INSERT * Suplier.* INTO [MS Access;DATABASE=" & sFIle & ";Jet OLEDB:Database Password=123456;].[Suplier] from [Suplier]", OldConn)
        cmd0.ExecuteNonQuery()
        cmd1.ExecuteNonQuery()
        cmd2.ExecuteNonQuery()

Now is Ok
I copy all my old_Database to New_Database
then delete not use table
Now my problem solved
tx Phasma

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.