ACCESS '97: How To: Copy Table to new Database and Format column widths [RESOLVED]

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
Access '97

Well, Great Success! I can now retrieve my data from the corporate Oracle Server and put it into 4 tables in the local Current Database.

(1) How can I copy one of the tables (G_Table) into a clean access database (G_Data.mdb)? (it goes in folder "\\g0ghq\Work\DB\")

(2) How can I programmatically format the displayed column widths for the table? (4 columns)

Thank you for your gracious comments, suggestions, and assistance.

[RobDog888]

Its me agian. Lets see how bad I can screw this simple one up.

You can copy database objects with this...

Application.DoCmd.CopyObject "DestinationDatabase", "NewName", acTable, "Table1"

How do you mean format the columns? In Table view in Access?

[RobDog888]

Arrg! Started already. I see that your originating db is Oracle or already in Access?

[Webtest]

RobDog ... the GREAT Competition Deep Sea Fisherman!

Forget about Oracle ... I'm already done with that ... I've got that part working GREAT! (Well, it's slower than molasses, but it works) My tables that I'm working with are all now local tables in the Access '97 Master database.

(1) I saw the DoCmd.CopyObject function and played with it, but it requires a valid existing destination database. So I guess that is my real question ... the various steps reqired to create a NEW database out on a shared network drive in preparation for copying the table. The 'reference' book I have stinks and is nearly useless. I'm getting lost in ADODB, ADOX, Catalogs(?), etc. I just want to spin off a clean database with a copy of one of my tables that I create dynamically (programatically).

(2) Yes, I want to automatically set up the NEW (Slave) database Table (created programatically) so that when the user opens it for viewing and editing, the column widths are set reasonably. Any ideas on that?

Thanks for your gracious assistance. I really need to find a good comprehensive Access book.

[dannymking]

Mut have a reference to "Microsoft DAO 3.6 Object Library"

Dim Db As DAO.Database 'Easier with DAO although older
  Set Db = DbEngine.CreateDatabase("C:\MyDatabase.mdb",dbLangGeneral)
  Dim Td As DAO.TableDef
  Set Td = Db.CreateTableDef("MyTable")
  Dim Fd As DAO.Field
  Set Fd = Td.CreateField("MyField")
  Fd.Type = dbtext
  Fd.Size = 50
  Td.Fields.Append Fd
  Set Fd = Noting
  Db.TableDefs.Append Td
  Set Td = Nothing
  Db.Close
  Set Db = Nothing

[Webtest]

Thanks Danny ... I got it working from your example. Here is my code:

Sub Test_Transfer_DB()
    Dim myDB As DAO.Database  'Do NOT use 'New' - it is not valid???

    'Define and Create the new Database for the table
    Set myDB = DBEngine.CreateDatabase("C:\Test\MyDatabase.mdb", dbLangGeneral)
    'Copy the existing SRC table into the new database as DEST table
    DoCmd.CopyObject myDB.Name, "DEST_TEST_EXTRACT", acTable, "SRC_TEST_EXTRACT"
    'Clean House
    myDB.Close
    Set myDB = Nothing

End Sub

I tried to use "TransferDatabase" to create a Linked Table, but it keeps complaining that it can't find the SOURCE table (it's in the Current Database) ...

    'DoCmd.TransferDatabase acLink, "Microsoft Access", myDB.Name, acTable, "TEST_EXTRACT", "DEST_TEST_EXTRACT"

[dannymking]

Try updating the tabledefs by

Mydb.TableDefs.Refresh

Also it would be better to replace

myDB.Name from the TransferDatabase with the actual filepath originally used in the creation of the database..

I really need to find a good comprehensive Access book

[Webtest]

Here's the answer to the "Column Width" part of the question. You have to open the datasheet and then dynamically set the width. Find the "Resizing columns" section:
You Can Do That with Datasheets?

[dannymking]

When I refered to the Size it was actually the field size of the text field created in the table in my example and not the size of the column..