How to copy a table using Visual Basic...

[shanzek]

I am attempting to provide an error prone user the ability to do an on-line backup of important tables prior to them screwing up an update. How can I copy an entire table from one table name to another either within the same database or to an external database, all within Visual Basic 6.0?
I attempted to do it with a simple loop, using DAO 3.51, essentially reading and writing records. But when it runs on their NOvell network, it will get a 'record lock' error, and kill the backup. It works flawlessly on a standalone system, but not on their network, even with a delay built in between each read and write. I know that is the low tech way to do it, but haven't been able to figure out how to copy the entire table.
Any help will be greatly appreciated.
Steve

[Brent Akester]

What you want to use is DAO's 'CopyFromRecordset' method, which basically dumps an entire recordset to a location of your choice. I have personally used this to dump from an Access db to an Excel spreadsheet. It goes something like this:

Set DB1 = opendatabase(ThisWorkbook.Path & "\STdb.mdb")

For x = 0 To UBound(arrSheets) - 1
Set RS1 = DB1.openrecordset(arrSheets(x), dbOpenSnapshot)
With Worksheets(arrSheets(x))
'.Range("A1").UsedRange.ClearContents
.Range("A4").CopyFromRecordset RS1
.Select
End With
RS1.Close
Next x
DB1.Close

This could be easily modified for Access...just do a 'SELECT * FROM' and grab all your tables.

You could also consider using ADO, which supports a similar function - 'GETROWS' (see your online help for more on this method). You could create a dynamic array, then use 'Getrows' to populate your array, THEN write the array to the new database.

Finally, check out this article on DAO/ADO and porting from DAO to ADO, on the MSDN site...it will probably help you some:
http://msdn.microsoft.com/library/techart/daotoado.htm

[MartinLiss]

Here is a routine I use in DAO 3.51 that you can check to see if you are doing something radically different.

Public Sub Backup()

    Dim nCtr As Integer
    Dim tbl As New TableDef
    Dim fld As Field
    Dim ind As Index
    Dim rsFrom As Recordset 'Dynaset
    Dim rsTo As Recordset 'Dynaset
    Dim rsBackupDate As Recordset
   
    On Error GoTo ErrorRoutine
    Screen.MousePointer = vbHourglass
    
    tbl.Name = "RunStats " & Now
    
    'Create the fields
    For nCtr = 0 To gdbRun.TableDefs("RunStats").Fields.Count - 1
        Set fld = New Field
        fld.Name = gdbRun.TableDefs("RunStats").Fields(nCtr).Name
        fld.Type = gdbRun.TableDefs("RunStats").Fields(nCtr).Type
        fld.Size = gdbRun.TableDefs("RunStats").Fields(nCtr).Size
        fld.Attributes = gdbRun.TableDefs("RunStats").Fields(nCtr).Attributes
        tbl.Fields.Append fld
    Next
    
    'Create the indexes
    For nCtr = 0 To gdbRun.TableDefs("RunStats").Indexes.Count - 1
        Set ind = New Index
        ind.Name = gdbRun.TableDefs("RunStats").Indexes(nCtr).Name
        ind.Fields = gdbRun.TableDefs("RunStats").Indexes(nCtr).Fields
        ind.Unique = gdbRun.TableDefs("RunStats").Indexes(nCtr).Unique
        ind.Primary = gdbRun.TableDefs("RunStats").Indexes(nCtr).Primary
        tbl.Indexes.Append ind
    Next
    
    'Append the new table
    gdbRun.TableDefs.Append tbl
    
    Set rsFrom = gdbRun.OpenRecordset("RunStats")
    Set rsTo = gdbRun.OpenRecordset(tbl.Name)
    While rsFrom.EOF = False
        rsTo.AddNew
        For nCtr = 0 To rsFrom.Fields.Count - 1
            rsTo(nCtr) = rsFrom(nCtr)
        Next
        rsTo.Update
        rsFrom.MoveNext
    Wend
    
    Set rsBackupDate = gdbRun.OpenRecordset("select * from BackupDate", dbOpenDynaset)
    With rsBackupDate
        .MoveFirst
        .Edit
        !LastBackupdate = Now
        .Update
    End With
    
    rsBackupDate.Close
    rsFrom.Close
    rsTo.Close
    Screen.MousePointer = vbNormal
    MsgBox "Table """ & tbl.Name & """ created."
    Exit Sub
    
ErrorRoutine:

  MsgBox Err.Description

End Sub

------------------
Marty