[AccessD] Trasnfer Table Between Databases

Marty:

That's a much more straightforward approach than I came up with, which I
post here anyway as someone might be able to use it.  The SendMessage sub
uses the Outlook object.  I would prefer to use SendObject but don't see a
way to attach a database file to the message.  Do you know if I can attach
an mdb to an email using SendObject?

Thanks and regards,

Rocky Smolin
Beach Access Software


Dim strDBFrom As String
Dim strDBTo As String
Dim tdf As TableDef
Dim objAccess As New Access.Application
Dim wrkjet As DAO.Workspace

Dim db As DAO.Database
Set db = CurrentDb

' Get the name and location of the back end
Set tdf = db.TableDefs("tblPatient")
strDBFrom = Right(tdf.Connect, Len(tdf.Connect) - InStr(1, tdf.Connect,
"="))
Set tdf = Nothing

strDBTo = Left(strDBFrom, Len(strDBFrom) - 4) & "_LT.mdb"

' Use TransferDatabase to trasnfer the tables to the target database
With objAccess
    .OpenCurrentDatabase (strDBFrom)
    .DoCmd.TransferDatabase acExport, "Microsoft Access", strDBTo, acTable,
"tblPatient", "tblPatient"
    .DoCmd.TransferDatabase acExport, "Microsoft Access", strDBTo, acTable,
"tblDiagnosticCode", "tblDiagnosticCode"
    .DoCmd.TransferDatabase acExport, "Microsoft Access", strDBTo, acTable,
"tblServiceCode", "tblServiceCode"
    .DoCmd.TransferDatabase acExport, "Microsoft Access", strDBTo, acTable,
"tblVisit", "tblVisit"
    .CloseCurrentDatabase
End With

Set objAccess = Nothing

' Empty the Visits table
Set wrkjet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkjet.OpenDatabase(strDBTo, True)
db.Execute "Delete * FROM tblVisit"
db.Close
Set db = Nothing

Call SendMessage("bchacc at san.rr.com", strDBTo)

----- Original Message ----- 
From: "MartyConnelly" <martyconnelly at shaw.ca>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Tuesday, September 16, 2003 9:47 PM
Subject: Re: [AccessD] Trasnfer Table Between Databases


> You can do this; from IN and INTO help files
>
> The following example makes a copy of the Employees table and places the
> new table in the assumed database Backup.mdb:
>
> SELECT Employees.* INTO Employees IN Backup.mdb FROM Employees;
>
> or
> Sub SelectIntoX()
>     Dim dbs As Database
>     Dim qdf As QueryDef
>     ' Modify this line to include the path to Northwind
>     ' on your computer.
>     Set dbs = OpenDatabase("Northwind.mdb")
>
>     ' Select all records in the Employees table
>     ' and copy them into a new table, Emp Backup.
>     dbs.Execute "SELECT Employees.* INTO " _
>         & "[Emp Backup] FROM Employees;"
>
>     ' Delete the table because this is a demonstration.
>     dbs.Execute "DROP TABLE [Emp Backup];"
>
>
>     dbs.Close
>
> End Sub
> Arthur Fuller wrote:
>
> > I know that you can do SELECTs from multiple MDBs in a single SELECT
> > statement. I never tried an append query across MDBs but I think it
> > would work.
> >
> > A.
> >
> >     -----Original Message-----
> >     From: accessd-bounces at databaseadvisors.com
> >     [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Rocky
> >     Smolin - Beach Access Software
> >     Sent: Tuesday, September 16, 2003 7:55 AM
> >     To: AccessD at databaseadvisors.com
> >     Subject: [AccessD] Trasnfer Table Between Databases
> >
> >     Dear List:
> >
> >     I have a FE/BE app where I need to transfer three of the BE tables
> >     to another database. Is there a better way to do this than
> >     creating a workspace for the target database, opening the
> >     target, opening the source and object tables and transferring the
> >     records one by one.  The table structure in the source and target
> >     databases is the same.  The number of records is small.  So time
> >     is not a consideration.
> >
> >     MTIA
> >
> >     Rocky Smolin
> >     Beach Access Software
> >
> >
> >------------------------------------------------------------------------
> >
> >_______________________________________________
> >AccessD mailing list
> >AccessD at databaseadvisors.com
> >http://databaseadvisors.com/mailman/listinfo/accessd
> >Website: http://www.databaseadvisors.com
> >
> >
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com