Adding a table using the APPEND method

[VBpunk]

Hey ya'll<br><br>I want to take a table from one database and<br>transfer it, data & structure into another database<br>in a new location.<br><br>Here's were I am so far<br><br><br>' Declair variables for the Database and Recordset.<br>Dim SOURCEdb As Database<br>Dim DESTINdb As Database<br>Dim SOURCErs As Recordset<br>Dim DESTINrs As Recordset<br><br>Private Sub cmdTableApp_Click()<br>&nbsp;&nbsp;&nbsp;&nbsp;Call CopyTable<br>End Sub<br><br>Private Sub Form_Load()<br>&nbsp;&nbsp;&nbsp;&nbsp;' Declare a strings for storing database locations<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim dbLoc1 As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim dbLoc2 As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;' Open dbTest to the location on the drive,<br>&nbsp;&nbsp;&nbsp;&nbsp;' where the database is.<br>&nbsp;&nbsp;&nbsp;&nbsp;dbLoc1 = &quot;c:\VBDocs\Projects\Copying Tables\DAO_source.mdb&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;dbLoc2 = &quot;s:\Public\DRamsahai\DAO_destin.mdb&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set SOURCEdb = OpenDatabase(dbLoc1)<br>&nbsp;&nbsp;&nbsp;&nbsp;Set DESTINdb = OpenDatabase(dbLoc2)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set SOURCErs = SOURCEdb.OpenRecordset(&quot;SOURCErs1&quot;, dbOpenTable)<br>&nbsp;&nbsp;&nbsp;&nbsp;SOURCErs.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;Call ShowFields<br>End Sub<br><br>Sub ShowFields()<br>&nbsp;&nbsp;&nbsp;&nbsp;txtID.Text = SOURCErs!ID<br>&nbsp;&nbsp;&nbsp;&nbsp;txtInfo.Text = SOURCErs!info<br>&nbsp;&nbsp;&nbsp;&nbsp;txtNumber.Text = SOURCErs!Number<br>End Sub<br><br>Sub CopyTable()<br>&nbsp;&nbsp;&nbsp;&nbsp;DESTINrs = SOURCErs.Clone<br>&nbsp;&nbsp;&nbsp;&nbsp;DESTINdb.TableDefs.Append(DESTINrs)<br>End Sub<br><br>Private Sub Form_Unload(Cancel As Integer)<br>&nbsp;&nbsp;&nbsp;&nbsp;DESTINrs.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;DESTINdb.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;SOURCErs.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;SOURCEdb.Close<br>End Sub<br><br><br>I know that the CopyTable function doesn't work<br>but there must be a way to add the DESTINrs recordset<br>to the DESTINdb database.&nbsp;&nbsp;Or maybe I'm doing this <br>completely wrong.&nbsp;&nbsp;Any advise would be helpful.<br><br>Thanks in advance =-) <p>Deryck Ramsahai<br><a href=mailtoRams.bna@bundy.com>DRams.bna@bundy.com</a><br><a href= > </a><br>It takes two to lie. One to lie and one to listen.

 

[VB400]

<br>For some reason, I have a feeling that there is a much easier way to do this, but this works.&nbsp;&nbsp;Any variable with the suffix 1 refers to source while suffix 2 refers to target.<br><br>Dim d1 As Database<br>Dim d2 As Database<br><br>Dim t1 As TableDef<br>Dim t2 As TableDef<br><br>Dim rs1 As Recordset<br>Dim rs2 As Recordset<br><br>Dim f As Field<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Set d1 = OpenDatabase(&quot;c:\source.mdb&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Set d2 = OpenDatabase(&quot;c:\target.mdb&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set t1 = d1.TableDefs(&quot;Table 1&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Set t2 = d2.CreateTableDef(&quot;Table 1&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;For Each f In t1.Fields<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;t2.Fields.Append t2.CreateField(f.Name, f.Type, f.Size)<br>&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;d2.TableDefs.Append t2<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>' So far we've duplicated the structure<br><br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs1 = t1.OpenRecordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Set t2 = d2.TableDefs(&quot;r Methods&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs2 = t2.OpenRecordset<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;With rs1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do While Not .EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs2.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;For Each f In .Fields<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs2.Fields(f.Name) = f<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rs2.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;rs1.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;rs2.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;d1.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;d2.Close<br><br>Hope this helps!<br><br>P.S.&nbsp;&nbsp;This does not duplicate the indexes<br>

 

[JimHorton]

Isn't there a 'Transfer Database' in the Docmd. object that does this in one line??<br>--Jim