VB-Access, the best way to drop an Access-table?

VB-Access, the best way to drop an Access-table?

Post by Matt Oko » Thu, 20 Nov 1997 04:00:00



I don't agree with your proposed solution.  You should issue an SQL
statement to delete all the records from the table.  For example,

    db.Execute("DELETE * from tblname")

to delete all the records from table 'tblname'.

Issue an append query to add the new records to 'tblname' from 'tblname1'

    db.Execute("INSERT INTO tblname SELECT * FROM tblname1")

Since your using VB4 the syntax will differ somewhat.  Check help.
And another tip ... don't use Macros ... use VBA code.  Study up on your SQL
code as well.


>I have an Access-database with 8 tables. In one of the tables I have
>600000 recordes (70 Mb).
>The database is approximently 80 Mb. What is the best way to delete all
>data and reloade new one?
>I want to do everything from Visual Basic 4.0. For the moment I use
>'appAccess.Run' and the Access
>procedure call an Access macro doing select all, delete all and transfer
>text, but I rather want
>to do it this way: copy the table-definitions/table-structure, then
>delete/drop all tables, after
>that I want to recreate the tables with the copied
>table-definitions/table-structure and load
>the new data. Is it possible to copy the
>table-definitions/table-structure and how do I do it?

>Tia
>Nikas Fjellman




 
 
 

VB-Access, the best way to drop an Access-table?

Post by Niklas Fjellma » Fri, 21 Nov 1997 04:00:00


Dear readers

I have an Access-database with 8 tables. In one of the tables I have
600000 recordes (70 Mb).
The database is approximently 80 Mb. What is the best way to delete all
data and reloade new one?
I want to do everything from Visual Basic 4.0. For the moment I use
'appAccess.Run' and the Access
procedure call an Access macro doing select all, delete all and transfer
text, but I rather want
to do it this way: copy the table-definitions/table-structure, then
delete/drop all tables, after
that I want to recreate the tables with the copied
table-definitions/table-structure and load
the new data. Is it possible to copy the
table-definitions/table-structure and how do I do it?

Tia
Nikas Fjellman




 
 
 

VB-Access, the best way to drop an Access-table?

Post by Maciej Wojtkowia » Tue, 25 Nov 1997 04:00:00


Hello,

    I had similar problem to your one. I suggest to:
    1. Don't use DELETE * statement against table with approx 600000
records. It will get a lot of time to finish this query
    2. You can hold empty database with same structure as normal - it will
act as template. Every time you need you just delete old one and copy
template & rename it to new working DB
    3. You can write procedure that scans structure of your db, drops tables
and creates them again. Below you can find procedure written by my colegue
that do similar thing (It was 2 years ago and you should check how it works
in new VB) :

Public Function CopyDBStructure(ByVal SourceName As String, ByVal DestName
As String, Optional ByVal INIScript)
Dim I As Integer, J As Integer, K As Integer
Dim sourceDB As Database, destDB As Database
On Error GoTo errCopyDBStructure
Set sourceDB = Workspaces(0).OpenDatabase(SourceName)
Set destDB = Workspaces(0).CreateDatabase(DestName, dbLangGeneral)
destDB.BeginTrans
For I = 0 To sourceDB.TableDefs.Count - 1
    If sourceDB.TableDefs(I).Attributes And dbSystemObject Then
        ' system object --> do nothing
    Else

        ' Preparing new TableDef
        Dim newTableDef As New TableDef
        newTableDef.Name = sourceDB.TableDefs(I).Name
        newTableDef.Attributes = sourceDB.TableDefs(I).Attributes
        For J = 0 To sourceDB.TableDefs(I).Fields.Count - 1
            ' Preparing new Fields
            Dim newField As New Field
            newField.Name = sourceDB.TableDefs(I).Fields(J).Name
            newField.Type = sourceDB.TableDefs(I).Fields(J).Type
            newField.Size = sourceDB.TableDefs(I).Fields(J).Size
            newField.Attributes = sourceDB.TableDefs(I).Fields(J).Attributes
            newField.DefaultValue =
sourceDB.TableDefs(I).Fields(J).DefaultValue
            newField.Required = sourceDB.TableDefs(I).Fields(J).Required
            If newField.Type = dbText Or newField.Type = dbMemo Then
                newField.AllowZeroLength =
sourceDB.TableDefs(I).Fields(J).AllowZeroLength
            End If
            newField.ValidationRule =
sourceDB.TableDefs(I).Fields(J).ValidationRule
            newField.ValidationText =
sourceDB.TableDefs(I).Fields(J).ValidationText
            newTableDef.Fields.Append newField
            Set newField = Nothing
        Next J
        For J = 0 To sourceDB.TableDefs(I).Indexes.Count - 1
            If Not sourceDB.TableDefs(I).Indexes(J).Foreign Then
                ' Preparing new Indexes
                Dim newIndex As New Index
                newIndex.Name = sourceDB.TableDefs(I).Indexes(J).Name
                newIndex.Primary = sourceDB.TableDefs(I).Indexes(J).Primary
                newIndex.Required =
sourceDB.TableDefs(I).Indexes(J).Required
                newIndex.Unique = sourceDB.TableDefs(I).Indexes(J).Unique
                For K = 0 To sourceDB.TableDefs(I).Indexes(J).Fields.Count -
1
                    ' Preparing new Fields for Index
                    Dim newIndexField As New Field
                    newIndexField.Name =
sourceDB.TableDefs(I).Indexes(J).Fields(K).Name
                    newIndexField.Attributes =
sourceDB.TableDefs(I).Indexes(J).Fields(K).Attributes
                    newIndex.Fields.Append newIndexField
                    Set newIndexField = Nothing
                Next K
                newTableDef.Indexes.Append newIndex
                Set newIndex = Nothing
            End If
        Next J
        destDB.TableDefs.Append newTableDef
        Set newTableDef = Nothing
    End If
Next I
For I = 0 To sourceDB.Relations.Count - 1
    ' Preparing new Relations
    Dim newRelation As New Relation
    newRelation.Name = sourceDB.Relations(I).Name
    newRelation.Attributes = sourceDB.Relations(I).Attributes
    newRelation.Table = sourceDB.Relations(I).Table
    newRelation.ForeignTable = sourceDB.Relations(I).ForeignTable
    For J = 0 To sourceDB.Relations(I).Fields.Count - 1
        ' Preparing new Fields for Relation
        Dim newRelationField As New Field
        newRelationField.Name = sourceDB.Relations(I).Fields(J).Name
        newRelationField.ForeignName =
sourceDB.Relations(I).Fields(J).ForeignName
        newRelation.Fields.Append newRelationField
        Set newRelationField = Nothing
    Next J
    destDB.Relations.Append newRelation
    Set newRelation = Nothing
Next I
For I = 0 To sourceDB.QueryDefs.Count - 1
    Dim newQueryDef As New QueryDef
    newQueryDef.Name = sourceDB.QueryDefs(I).Name
    newQueryDef.SQL = sourceDB.QueryDefs(I).SQL
    destDB.QueryDefs.Append newQueryDef
    Set newQueryDef = Nothing
Next I
destDB.CommitTrans
sourceDB.Close
destDB.Close
CopyDBStructure = 0
Exit Function
errCopyDBStructure:
    CopyDBStructure = Err
    Exit Function
End Function

Maciej Wojtkowiak
DomData Sp. z o.o.
Poznan, Poland


>Dear readers

>I have an Access-database with 8 tables. In one of the tables I have
>600000 recordes (70 Mb).
>The database is approximently 80 Mb. What is the best way to delete all
>data and reloade new one?
>I want to do everything from Visual Basic 4.0. For the moment I use
>'appAccess.Run' and the Access
>procedure call an Access macro doing select all, delete all and transfer
>text, but I rather want
>to do it this way: copy the table-definitions/table-structure, then
>delete/drop all tables, after
>that I want to recreate the tables with the copied
>table-definitions/table-structure and load
>the new data. Is it possible to copy the
>table-definitions/table-structure and how do I do it?

>Tia
>Nikas Fjellman