SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast RedFish's Avatar
    Join Date
    Nov 2001
    Location
    Pacific Northwest, USA
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MSAccess 97 - copy database, structure only

    Does anyone know an easy way to copy an MSAccess 97 database with just the structure, not the data? I want another Access db with all the tables, queries, forms, etc but I don't want the data. I looked through the help files and MS knowledge base, and am not finding the answer.

    I tried the TransferDatabase command as a macro, but that only works for individual objects within the database. Unless I'm doing something wrong with the TransferDatabase command.

    thx in advance,
    RedFish

  2. #2
    SitePoint Zealot
    Join Date
    Nov 2001
    Location
    RI, USA
    Posts
    140
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    do you mean..

    Do want to do this programmatically like through a macro.. or just need to do it once manually?

    If you don't mind the tedium of manual you can just open the destination datbase, select file import, select the source database, and then there should be options to just import the tables, not the data.
    in1.com | Bootply - Bootstrap playground

  3. #3
    SitePoint Member
    Join Date
    Nov 2001
    Location
    Southeast USA
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Copy Database, Structure Only

    If you know Visual Basic for Applications, create a module in your new application using the following code:

    Sub ClearAllTables()
    Dim DB As Database
    Dim intRecordCount As Integer
    Dim ctr As Integer

    Set DB = CurrentDb
    With DB
    intRecordCount = .TableDefs.Count - 1
    For ctr = 0 To intRecordCount
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DELETE * FROM " & .TableDefs(ctr).Name & ";"
    DoCmd.SetWarnings True
    Next ctr
    End With
    Set DB = Nothing

    End Sub

    ONLY USE THIS WITH A COPY!

    This code will delete all information in all tables. It will not affect system tables (I think!) because a user does not have rights to those tables.

    If you have certain tables that should not be included in the delete mode, use their names as part of an IF statement that skips around the name.

    Good Luck!

  4. #4
    SitePoint Enthusiast RedFish's Avatar
    Join Date
    Nov 2001
    Location
    Pacific Northwest, USA
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Re: do you mean..

    Originally posted by aspapp
    If you don't mind the tedium of manual you can just open the destination datbase, select file import, select the source database, and then there should be options to just import the tables, not the data.
    Sweet! That's exactly what I needed.

    I was trying to work it from the existing database. Didn't think to go through the import option from the blank/destination database.

    Thank you!

    RedFish

  5. #5
    SitePoint Addict kevin_tremblay's Avatar
    Join Date
    Jul 2001
    Location
    Boston Area (North shore)
    Posts
    233
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    import the db to the new access and then you can do a sql delete of all the data in the tables, then all you will have after that is an empty table with your structure.

    You can actually do the delete through a macro.
    Kevin Tremblay-- Sys Admin
    work: kevin_tremblay@hsgmed.com
    "The object of the journey is not to arrive."


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •