[02/03] Clean up Access Database

[Xcoder]

I have an Access database which the user might want to clean up to start fresh from time to time, how can I accomplish this?

Im working with the Jet engine btw.

[RobDog888]

Do you mean like recreating just the database schema or a compact and repair?

[Xcoder]

Do you mean like recreating just the database schema or a compact and repair?

I guess it would be a clean recreation of the existing database. There is a consecutive number (key) which is incrementing with every interaction, and the user might want to restart that number back to 1.

[RobDog888]

Well if there are records still in the table then you culdnt reset it back to one so I take it would be a blank database with just the table definitions and queries ets. (schema).

You could use the TransferDatabase method to export a table to another access database.

'Using the Access Object Model
'Repeat for each objecvt to be replicated to the new blank db
'Change constants to .NET
Application.DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\RobDog888New.mdb", acTable, "Table1", "Table1", True

[Xcoder]

Well if there are records still in the table then you culdnt reset it back to one so I take it would be a blank database with just the table definitions and queries ets. (schema).

You could use the TransferDatabase method to export a table to another access database.

'Using the Access Object Model
'Repeat for each objecvt to be replicated to the new blank db
'Change constants to .NET
Application.DoCmd.TransferDatabase acExport, "Microsoft Access", "C:\RobDog888New.mdb", acTable, "Table1", "Table1", True

Thanks Rob, how do I import the Access Object Model?

Another question, would I need a clean version of the same file?

[RobDog888]

What this method does is to copy each object individually one at a time with just the table definition only or if you want you can copy the data too.
so if you have 5 tables then you would have to make 5 calls passing each table name separately.

There is another method call that can create the blank empty database file -

Application.NewCurrentDatabase "C:\RobDog888.mdb"

But then your current db will be the empty one so your TransferDatabase call would be turned into an Import call.

Add a reference or use Late Binding if the Access version is or may change.

[RobDog888]

Option Explicit On 
Option Strict On
'Add a reference to MS Access xx.0 Object Library
Imports Microsoft.Office.Interop
 
Public Class Form1
    Inherits System.Windows.Forms.Form
 
    Private moApp As Access.Application
 
    Private Sub frmClients_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        moApp = DirectCast(CreateObject("Access.Application"), Access.Application)
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        moApp.NewCurrentDatabase "C:\RobDog888New.mdb"
        moApp.DoCmd.TransferDatabase Access.AcDataTransferType.acImport, "Microsoft Access", "C:\RobDog888.mdb", Access.AcDataObjectType.acDataTable, "Table1", "Table1", True
        'More object calls etc.
        '...
    End Sub
 
End Class

[Xcoder]

Thanks Rob, I now have a problem with casting the Access.acDataObjectType as Access.acObjectType in the line:

moApp.DoCmd.TransferDatabase(Access.AcDataTransferType.acImport, "Microsoft Access", "C:\RobDog888.mdb", [B][COLOR=RED]Access.AcDataObjectType.acDataTable[/COLOR][/B], "Table1", "Table1", True)

What am I missing? Thanks again.

[RobDog888]

Not sure how that changed but it should be in my post as

Access.AcObjectType.acTable