Copy from MS Access SourceDatabase to DestintionDatabase using ASP.Net - VB.Net

here is my code, i am still missing something to copy from SourceDatab开发者_StackOverflow社区ase to DestintionDatabase with the suffix "copied"

Dim myDB = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|SourceDatabase.mdb;Persist Security Info=True")
 Dim myDB2 = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|DestintionDatabase.mdb;Persist Security Info=True")

    myDB.Open()
    Dim mytables = myDB.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, New Object() {})

    Dim CurrentTable, CreateString, DropString As String


    For i = 1 To mytables.Rows.Count
        CurrentTable = mytables.Rows(i - 1).Item(2).ToString
        If Not CurrentTable.Contains("copied") Then
            CreateString = "SELECT * INTO [" + CurrentTable + "copied] FROM [" + CurrentTable + "]"
            DropString = "DROP TABLE [" + CurrentTable + "copied]"
            Dim myDBCommand2 = New OleDbCommand(CreateString, myDB)
            Dim myDBCommand1 = New OleDbCommand(DropString, myDB)
            Try
                myDBCommand1.ExecuteNonQuery()
            Catch ex As Exception

            End Try
            myDBCommand2.ExecuteNonQuery()
        End If
    Next i

    myDB.Close()

Obviously this is a code snippet from VB.Net 2010 - not only is it not a whole class, it isn't even a whole sub.

The reason that you don't have any replies yet, is that there are a lot of problems with your code snippet. You are essentially asking someone to write this module for you.

A few of the many problems:

• I don't recognize |DataDirectory| in the connection strings... Is that supposed to be something like C:\DataDirectory ?
• Where is your call to myDB2.Open() and myDB2.Close()?
• When you call GetOleDbSchemaTable, why are you asking for Primary_Keys instead of Tables?

The biggest problem is your technique of copying tables.

Insert * Into [TableNameCopied] From [TableName]

works fine if the new table TableNameCopied is supposed to be in the same database as existing table TableName. In your case, that isn't true.

If your code did work, this is what would happen for each table:

1. myDBCommand1 drops the table in Sourcedatabase.
2. myDBCommand2 tries to copy the just-copied database from the SourceDatabase to SourceDatabase.

Please notice that both command objects were created against MyDB, neither from MyDB2. Other than declaring MyDB2 to point to DestintionDatabase, you don't use it at all... you don't even open it or close it! But... There is no standard SQL syntax that says to read from a table in a completely different database.

(SQL Server has the concept of "linked servers", where you can specify four-part names like ServerName.DatabaseName.OwnerName.TableName, as in TestServer.Billing.Dbo.Customers. But this is not Standard SQL, and Access doesn't support it.)

There are at least two ways to do what you are trying to accomplish.

1. Write code that examines exactly what table CurrentTable looks like (number of columns, names of each column, etc.), and creates the same table in DestintionDatabase. Then, write a loop that reads all the data from SourceDatabase and writes it to DestintionDatabase. This is non-trivial!
2. Open DestintionDatabase in Microsoft Access, and click File / Get External Data / Import, select SourceDatabase, go to the "Tables" tab, click "Select All" then "OK". Then, change the name of each copied table to append "Copied" to the name.
   (I think it should be possible to capture this as a Macro or VBA, but I haven't tried it.)

Good luck, Mokokamello.