copying recordset from one database into a copy of the database

From: steven scaife (scaife_at_discussions.microsoft.com)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 02:41:03 -0800

I need to be able to copy the data from one recordset into a table on an
exact replica of the current database.

The replica holds the table structures and no data and I am required to
transfer all the data for a particular record into the replica which is
modified by the agents on their laptops in a product we have and then I have
to export the data back from the laptop to the server copy of the database.

The database is an access database and I am using VB6. I am able to
generate a recordset and then copy the recordset using some code i found, but
I am unsure of how I enter the contents into the replica.

If more information is required please let me know, thanks in advance

Here is my code

Sub OpenConn()
MDBPath = [path to db]
connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MDBPath &
";Persist Security Info=False"

conn.Open connstring
Set cmd.ActiveConnection = conn

End Sub

Public Function CreateRecordset(ByRef oRec As ADODB.Recordset, ByRef
oRecDest As ADODB.Recordset, Optional withAttributes As Boolean = True,
Optional withData As Boolean = False)
    'create an empty recordset using oRec as template
    'withAttributes - fill also attributes
    'withData - fill also data
    
    Dim oFld As ADODB.Field, i As Integer
    
    
    For Each oFld In oRec.Fields
        If withAttributes Then
            Call oRecDest.Fields.Append(oFld.Name, oFld.Type,
oFld.DefinedSize, oFld.Attributes)
        Else
            Call oRecDest.Fields.Append(oFld.Name, oFld.Type,
oFld.DefinedSize, adFldIsNullable)
        End If
    Next
    oRecDest.Open
    If withData Then
        
        oRec.MoveFirst
        Do While Not oRec.EOF
            oRecDest.AddNew
            For i = 0 To oRec.Fields.Count - 1
                oRecDest.Fields(i).Value = oRec.Fields(i).Value
            Next i
            oRec.MoveNext
        Loop
    End If
    oRecDest.Update
Exit_Function:
        'Err.Raise Err.Number, Err.Source, Err.Description
        
End Function

Private Sub cmdToAdvisor_Click()

Dim FilePathFrom, FilePathTo, SrcFile, DestFile, AppPath As String
Dim RecID, i As Integer

'get the record number of the case for later use
RecID = InputBox("Please enter the ID number of the record you wish to
export", "Record Export")

'set the app path of where the database is and where the copy is going to
AppPath = "C:\Documents and Settings\sscaife\Desktop\Homebuyer Conversion\"

FilePathFrom = "Transport.MDB" 'the read only database template
FilePathTo = Day(Date) & Month(Date) & Year(Date) & Hour(Time) &
Minute(Time) & Second(Time) & ".mdb" 'create a unique database name
'first we copy the template MDB

'now create the variables for the database copy
SrcFile = AppPath & FilePathFrom
DestFile = AppPath & FilePathTo
FileCopy SrcFile, DestFile 'copy the database

'sends the case to the advisor so he can modify it on his laptop

'our SQL string with the record id
strSQL = "SELECT * FROM Cases WHERE ID = " & RecID

cmd.CommandText = strSQL
cmd.CommandType = adCmdText
Set RS = cmd.Execute

i = 1

If Not RS.EOF Then

    'we need to add the data to another database so open up a new connection
    
connstringIns = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & AppPath &
FilePathTo & ";Persist Security Info=False"
connIns.Open connstringIns

Set cmdIns.ActiveConnection = connIns

CreateRecordset RS, RSIns, True, True

Else
    MsgBox "Sorry but the ID number you entered wasn't found!"
End If
        
RS.Close
Set RS = Nothing

End Sub

Relevant Pages