Search Tools Links Login

Database Table Copy


This code will copy all fields from all tables in a database and add them to another identical database.
My company has Rep submitting database reports all the time. I need to append the data from the reports database to a live database. At first I was running querys I had made in the database but our file structure changed and that was no long an option. This is about as simple as it gets but someone might find it useful.

Original Author: Shawn Jetton

Inputs

Call the function like this.
AddData "C:My DocumentsTestFrom.mdb", "C:My DocumentsTestTo.mdb"

Side Effects

None that I know of.

Code

Public Sub AddData(DataFrom As String, DataTo As String)
Dim dbFrom, dbTo As Database
Dim rsFrom, rsTo As Recordset
Set dbFrom = OpenDatabase(DataFrom)
Set dbTo = OpenDatabase(DataTo)
For n = 0 To dbFrom.TableDefs.Count - 1
    'This search out on table in your database
    If dbFrom.TableDefs(n).Attributes = 0 Then
      Set rsFrom = dbFrom.OpenRecordset(dbFrom.TableDefs(n).Name)
      Set rsTo = dbTo.OpenRecordset(dbTo.TableDefs(n).Name)
    End If
  
    'Loops through all fields in table and copies from dbFrom to dbTo.
    Do Until rsFrom.EOF
      rsTo.AddNew
      For i = 1 To rsTo.Fields.Count - 1
        If rsFrom.Fields(i) = "" Then GoTo hell
        rsTo.Fields(i) = rsFrom.Fields(i)
hell:
      Next i
      
      'This updates and moves to the next record in the from database
      rsTo.Update
      rsFrom.MoveNext
    Loop
Next n
dbFrom.Close
dbTo.Close
End Sub

About this post

Posted: 2002-06-01
By: ArchiveBot
Viewed: 74 times

Categories

Visual Basic 6

Attachments

No attachments for this post


Loading Comments ...

Comments

No comments have been added for this post.

You must be logged in to make a comment.