A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

copy table from closed database



 
 
Thread Tools Display Modes
  #1  
Old April 25th, 2004, 09:24 AM
Stuart
external usenet poster
 
Posts: n/a
Default copy table from closed database

I have a table named "Estimate Template" in a closed database
named "Newdb.mdb", which is located in
"C:\Temp\Automate Office".

I create a new database with the following code, which is running
from an Excel VBA module:

Dim appAccess As Access.Application 'dimmed at module level

Sub NewAccessDatabase()
Dim dbs As Object, tdf As Object, fld As Variant
Dim strDB As String
Const DB_Text As Long = 10
Const FldLen As Integer = 40

' Initialize string to database path.
strDB = "C:\Temp\Automate Office\Newdb2.mdb"

' Create new instance of Microsoft Access.
Set appAccess = CreateObject("Access.Application.9")

' Open database in Microsoft Access window.
appAccess.NewCurrentDatabase strDB

' Get Database object variable.
Set dbs = appAccess.CurrentDb

' Do Things

' Append Field and TableDef objects.
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
' Tidy up
Set appAccess = Nothing
End Sub

Q1: How can I copy the table "Estimate Template" from
Newdb.mdb to Newdb2.mdb and rename it (say)
"New Estimate Template".
(the table is pre-formatted with FieldNames, DataType
and FieldWidth etc, ready for spreadsheet data import).
I wish to keep this table as a Template for further use.

Q2: Do not understand the statement:
' Append Field and TableDef objects

Help would be much appreciated, please.

Regards.





---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.667 / Virus Database: 429 - Release Date: 23/04/2004


  #2  
Old April 25th, 2004, 12:13 PM
Marin Kostov
external usenet poster
 
Posts: n/a
Default copy table from closed database

I think this is little strange - the way you are referencing the objects.
My opinion: Use the object model, as written "by the book". What do I mean:
First, open the VBA Editor, and click Tools - References - Microsoft Data
Access Objects 3.60 Object Library. Now you will have all objects defined at
design time, and you will be able to use them and to code efficient.
Next, open the Online help of Access, and then Microsoft Data Access Objects
3.6 (DAO). Check CreateDatabase and CreateTableDef Methods. You can read
OpenDatabase too. There is sample code.
If you do not understand the concepts, you need to read some books about VB
and VBA. There are tutorials in the VBA help systems, in books, MSDN,
Internet...

--
Marin Kostov
Microsoft Office XP Master Instructor


  #3  
Old April 25th, 2004, 12:36 PM
Stuart
external usenet poster
 
Posts: n/a
Default copy table from closed database

Many thanks. Will follow your advice.

BTW, the code was pinched from Auto2000.chm
(a file extracted from Auto2000.exe) which is
MS Technical Support's guide to Automation in
Office2000.

Regards and thanks.

"Marin Kostov" wrote in message
...
I think this is little strange - the way you are referencing the objects.
My opinion: Use the object model, as written "by the book". What do I

mean:
First, open the VBA Editor, and click Tools - References - Microsoft Data
Access Objects 3.60 Object Library. Now you will have all objects defined

at
design time, and you will be able to use them and to code efficient.
Next, open the Online help of Access, and then Microsoft Data Access

Objects
3.6 (DAO). Check CreateDatabase and CreateTableDef Methods. You can read
OpenDatabase too. There is sample code.
If you do not understand the concepts, you need to read some books about

VB
and VBA. There are tutorials in the VBA help systems, in books, MSDN,
Internet...

--
Marin Kostov
Microsoft Office XP Master Instructor




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.667 / Virus Database: 429 - Release Date: 23/04/2004


  #4  
Old April 26th, 2004, 07:09 PM
dandgard
external usenet poster
 
Posts: n/a
Default copy table from closed database


I suggest you look at DoCmd.TransferDatabase. This should get you what
you want.

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"Path and Name of DB", acTable, "Name of Import table", _
"Name of table to put in"

Note that this will create an identical table to the one that is in the
first db and so you should not have a table created in the new db.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

  #5  
Old April 26th, 2004, 07:09 PM
dandgard
external usenet poster
 
Posts: n/a
Default copy table from closed database


I suggest you look at DoCmd.TransferDatabase. This should get you what
you want.

DoCmd.TransferDatabase acImport, "Microsoft Access", _
"Path and Name of DB", acTable, "Name of Import table", _
"Name of table to put in"

Note that this will create an identical table to the one that is in the
first db and so you should not have a table created in the new db.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:27 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.