Copy table from one database and put in another

[CJN]

Hi All,

If I have a database in Access (say myDB1.mdb) which has a table called tempResults and is filled with data, how can I place a copy of this table at run time in another database (Access) chosen by the user (at run time) (this could be a new db or one with tables already in it) and rename the table to whatever the user wants to call it?

EG.

DATABASE
- myDB1
_______________
Tables
- mytable1
- myTable2
- tempResults

Run-time -
User wants tempResults table saved to their database.
They say where their db is located eg. c:\progs\USERDB.mdb
They say what they want to call the tempResults table
eg. UsersResults
The program takes tempResults table, renames it UsersResults, and saves it in c:\progs\USERDB.mdb

(the tempResults table is still in its original state in myDB1 as well)


Hope you guys understand and can help

Cheers

[Sathyaish]

Have a look at this.

http://board.quicktechusa.com/cgi-bi...c;f=2;t=000719

[CJN]

Cheers for your help but this would mean I have to have a table ready in the destination database, I do not have this because I don't know what the destination db will be until runtime. I just wanna put my table in the current database into a destination database and rename the table, it seems like it should be such a simple thing. Any further help gratefully appriciated

[Sathyaish]

To create a new table in the destination database, you can use ADOX. Here is some sample code from my current project.

Public Sub CreateBlankRepository(ByVal StrRepositoryPath As String)


Dim cn As Connection
Dim Cat As ADOX.Catalog
Dim objTable As ADOX.Table
Dim rs As Recordset


Set cn = New Connection
Set Cat = New ADOX.Catalog
Set objTable = New ADOX.Table

    If PMData.DatabaseType = pmdMSAccess Then
        cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & StrDatabasePath
    ElseIf PMData.DatabaseType = pmdMSSQLServer Then
        cn.Provider = "SQLOLEDB"
        cn.ConnectionString = "Data Source=" & PMData.ActiveDB & ";UID=" & PMData.ActiveUID & ";PWD=" & PMData.ActivePWD
    End If
  
    Set Cat.ActiveConnection = cn

    On Error Resume Next
    Cat.Tables.Delete "Dummy"
    Err.Clear
  
    On Error GoTo 0
    objTable.Name = "Dummy"
    objTable.Columns.Append "UserName", adVarWChar
    objTable.Columns.Append "MessageID", adVarWChar
    objTable.Columns.Append "MessageDate", adVarWChar
    objTable.Columns.Append "MessageTime", adVarWChar
    objTable.Columns.Append "MessageType", adVarWChar
    objTable.Columns.Append "MessageStatus", adVarWChar
    objTable.Columns.Append "DataBaseQueryString", adVarWChar

    objTable.Columns.Append "UserRights", adVarWChar
    objTable.Columns.Append "IsActive", adVarWChar
    objTable.Columns.Append "FirstName", adVarWChar
    objTable.Columns.Append "MiddleName", adVarWChar
    objTable.Columns.Append "LastName", adVarWChar
    objTable.Columns.Append "Password", adVarWChar
    objTable.Columns.Append "CreationDate", adVarWChar
    objTable.Columns.Append "CreationTime", adVarWChar
    objTable.Columns.Append "LastUpdatedDate", adVarWChar
    objTable.Columns.Append "LastUpdatedTime", adVarWChar
    objTable.Columns.Append "GroupID", adVarWChar
    objTable.Columns.Append "GroupName", adVarWChar
    objTable.Columns.Append "LevelID", adVarWChar
    objTable.Columns.Append "LevelName", adVarWChar

    Cat.Tables.Append objTable
  
    Set rs = New Recordset
    Call rs.Open("select * from " & objTable.Name, cn, adOpenStatic, adLockOptimistic)
    
    rs.AddNew
    rs("UserName") = "Dummy User"
    rs("MessageID") = "0"
    rs("MessageDate") = CStr(FormatDateTime(Date, vbShortDate))
    rs("MessageTime") = CStr(FormatDateTime(Time, vbShortTime))
    rs("MessageType") = "0"
    rs("MessageStatus") = "0"
    rs("DataBaseQueryString") = " "
    rs("UserRights") = " "
    rs("IsActive") = CStr(False)
    rs("FirstName") = " "
    rs("MiddleName") = " "
    rs("LastName") = " "
    rs("Password") = " "
    rs("CreationDate") = CStr(Empty)
    rs("CreationTime") = CStr(Empty)
    rs("LastUpdatedDate") = CStr(Empty)
    rs("LastUpdatedTime") = CStr(Empty)
    rs("GroupID") = " "
    rs("GroupName") = " "
    rs("LevelID") = " "
    rs("LevelName") = " "
    rs.Update
    
    rs.MoveFirst
    rs.Delete
    Call rs.Save(StrRepositoryPath, adPersistADTG)
    
    If rs.State <> adStateClosed Then rs.Close
    If Not rs Is Nothing Then Set rs = Nothing
    If Not objTable Is Nothing Then Set objTable = Nothing
    If Not Cat Is Nothing Then Set Cat = Nothing
    If cn.State <> adStateClosed Then cn.Close
    If Not cn Is Nothing Then Set cn = Nothing
    
  
End Sub

Do not worry, not all of it is relevant and creating a table is not so lengthy in terms of the code it requires. Only the following snippet is relevant to your case.

    On Error GoTo 0
    objTable.Name = "Dummy"
    objTable.Columns.Append "UserName", adVarWChar
    objTable.Columns.Append "MessageID", adVarWChar
    objTable.Columns.Append "MessageDate", adVarWChar
    objTable.Columns.Append "MessageTime", adVarWChar
    objTable.Columns.Append "MessageType", adVarWChar
    objTable.Columns.Append "MessageStatus", adVarWChar
    objTable.Columns.Append "DataBaseQueryString", adVarWChar

    objTable.Columns.Append "UserRights", adVarWChar
    objTable.Columns.Append "IsActive", adVarWChar
    objTable.Columns.Append "FirstName", adVarWChar
    objTable.Columns.Append "MiddleName", adVarWChar
    objTable.Columns.Append "LastName", adVarWChar
    objTable.Columns.Append "Password", adVarWChar
    objTable.Columns.Append "CreationDate", adVarWChar
    objTable.Columns.Append "CreationTime", adVarWChar
    objTable.Columns.Append "LastUpdatedDate", adVarWChar
    objTable.Columns.Append "LastUpdatedTime", adVarWChar
    objTable.Columns.Append "GroupID", adVarWChar
    objTable.Columns.Append "GroupName", adVarWChar
    objTable.Columns.Append "LevelID", adVarWChar
    objTable.Columns.Append "LevelName", adVarWChar

    Cat.Tables.Append objTable

Before this, you just create your connection object, a catalog object from the ADOX library's Catalog class and a Table object.

[Sathyaish]

In case you're wondering what this ADOX is, then read here:

http://www.codeguru.com/forum/showth...hreadid=252337

[Shaggy Hiker]

It seems to me that you could take the tabledef from the first database and append it to the tabledefs collection in the second database. Then alter the name of the tabledef in the second database to whatever you want the new name to be. This would move the table, but probably not the data in the table. The next step that occurs to me at first glance is to run a query to get all the data from table1 in database1, and add or append the recordset to table2 in database2. This should be relatively straightforward.

[CJN]

Why can't I just do something like a save file dialog? You can save txt files to places can't you save tables to database in a similar way?

[Shaggy Hiker]

I don't think so, but what I'm suggesting really isn't all that much harder. You would want to use the commondialogcontrol (or something like that) to determine which database to use, but once you have the database object set, it should be pretty easy to append a tabledef from one to another. Moving the records over shouldn't be more than a few lines either.

On the other hand, open up Access and see what it takes to move a table there. I don't think you can just drag and drop them, but maybe you can.

[MartinLiss]

Here is how I do it via DAO. In this case the target database is referenced via gdbTargetDB, but that could be added as a parameter of the sub.

Public Sub CopyTable(sSourceDB As String, from_nm As String, to_nm As String)
 
    Dim dbSource As Database
    Dim nCtr As Integer
    Dim tbl As New TableDef
    Dim fld As Field
    Dim ind As Index
    Dim ds1 As Dynaset
    Dim ds2 As Dynaset
   
    On Error GoTo ErrorRoutine
 
    Set dbSource = Workspaces(0).OpenDatabase(gsDBPath & sSourceDB)
 
    'Search to see if table exists
    For nCtr = 0 To gdbTargetDB.TableDefs.Count - 1
        If UCase(gdbTargetDB.TableDefs(nCtr).Name) = UCase(to_nm) Then
             gdbTargetDB.TableDefs.Delete gdbTargetDB.TableDefs(nCtr).Name
        Exit For
        End If
    Next
    
    'strip off owner if necessary
    If InStr(to_nm, ".") <> 0 Then
        to_nm = Mid(to_nm, InStr(to_nm, ".") + 1, Len(to_nm))
    End If
    tbl.Name = to_nm
    
    'Create the fields
    For nCtr = 0 To dbSource.TableDefs(from_nm).Fields.Count - 1
        Set fld = New Field
        fld.Name = dbSource.TableDefs(from_nm).Fields(nCtr).Name
        fld.Type = dbSource.TableDefs(from_nm).Fields(nCtr).Type
        fld.Size = dbSource.TableDefs(from_nm).Fields(nCtr).Size
        If dbSource.TableDefs(from_nm).Fields(nCtr).AllowZeroLength = True Then
            fld.AllowZeroLength = True
        End If
        
        fld.Attributes = dbSource.TableDefs(from_nm).Fields(nCtr).Attributes
        tbl.Fields.Append fld
    Next
    
    'Create the indexes
    For nCtr = 0 To dbSource.TableDefs(from_nm).Indexes.Count - 1
        Set ind = New Index
        ind.Name = dbSource.TableDefs(from_nm).Indexes(nCtr).Name
        ind.Fields = dbSource.TableDefs(from_nm).Indexes(nCtr).Fields
        ind.Unique = dbSource.TableDefs(from_nm).Indexes(nCtr).Unique
        ind.Primary = dbSource.TableDefs(from_nm).Indexes(nCtr).Primary
        tbl.Indexes.Append ind
    Next
    
    'Append the new table
    gdbTargetDB.TableDefs.Append tbl
    
    Set ds1 = dbSource.CreateDynaset(from_nm)
    Set ds2 = gdbTargetDB.CreateDynaset(to_nm)
    While ds1.EOF = False
        ds2.AddNew
        For nCtr = 0 To ds1.Fields.Count - 1
            ds2(nCtr) = ds1(nCtr)
        Next
        ds2.Update
        ds1.MoveNext
    Wend
    
ErrorRoutine:
 
    If Err.Number <> 0 Then
        PrintDetailRpt "CopyTable", FOUND_ERROR
    Else
        PrintDetailRpt "", OK
    End If
 
End Sub

[CJN]

Well hello again,

I've come to the conclusion that I have to face the fact I am going to have to try your suggestion (thanks for them all be the way, good work!), so I will start at the top and work my way down. So starting with Sathyaish, my version so far is:

Dim objConnection As OleDbConnection
    Dim strTableName As String 
'table name entered by user, strFileName is db name chosen by user using savefiledialog
 
    Private Sub SavetoNew()
        Dim strDBName As String
        Dim Cat As ADOX.Catalog
        Dim objTable As ADOX.Table
 
        If OleDbConnection1.State = ConnectionState.Open Then
            OleDbConnection1.Close()
        End If
 
        Cat = New ADOX.Catalog()
        objTable = New ADOX.Table()
        strFileName = txtDBfile.Text()
        strTableName = txtTableName.Text()
        objConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName & ";User ID=Admin; Password=;")
 
        objConnection.Open()
        Cat.ActiveConnection = objConnection
 
        objTable.Name = strTableName
        objTable.Columns.Append("TestResultID", ADOX.DataTypeEnum.adVarWChar)
        objTable.Columns.Append("TestUndertakenID", ADOX.DataTypeEnum.adVarWChar)
        objTable.Columns.Append("TestResultValue", ADOX.DataTypeEnum.adVarWChar)
        objTable.Columns.Append("TestPassed", ADOX.DataTypeEnum.adVarWChar)
        objTable.Columns.Append("TestDatTime", ADOX.DataTypeEnum.adVarWChar)
        objTable.Columns.Append("FailureReason", ADOX.DataTypeEnum.adVarWChar)
        Cat.Tables.Append(objTable)
 
        objConnection.Close()
 
    End Sub

It gets stuck here:

Cat = New ADOX.Catalog()

saying:

An unhandled exception of type 'System.IO.FileNotFoundException' occurred in bvwebmonitorUI.exe

Additional information: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

What does this mean, what can I do?

I looked for it on msdn but it doesn't really say anything other than its an error