saving part of database

What would be easiest way to save only part of database in different name? Databases type is Dbase IV.

If I have a recordset which has all the datas from database and then user gives some range of database and a new databases name.

Let's say that database has datas from 1/1/1998 to 19/8/1998 and then user wants to do new database which has only datas from 1/1/1998 to 31/1/1998 and give it a new name. Those databases isn't always similar.

Do I have to open a new recordset for that given range of dates and somehow try to save it or create new database and somehow try to copy all fields to it and then try copy datas to it??  
 
 
   
 
majalaAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
watyConnect With a Mentor Commented:
You could create a new table in the new database based on the definition of the source table :

See the Visdata Sample from VB, you will find useful code :

Function CopyStruct(vFromDB As Database, vToDB As Database, vFromName As String, vToName As String, bCreateIndex As Integer) As Integer
  On Error GoTo CSErr

  Dim i As Integer
  Dim tblTableDefObj As TableDef
  Dim fldFieldObj As Field
  Dim indIndexObj As Index
  Dim tdf As TableDef
  Dim fld As Field
  Dim idx As Index
 
  'search to see if table exists
NameSearch:
'  For Each tdf In vToDB.Tabledefs
  For i = 0 To vToDB.TableDefs.Count - 1
    Set tdf = vToDB.TableDefs(i)
    If UCase(tdf.Name) = UCase(vToName) Then
      If MsgBox(MSG3, 4) = vbYes Then
         vToDB.TableDefs.Delete tdf.Name
      Else
         vToName = InputBox(MSG4)
         If Len(vToName) = 0 Then
           Exit Function
         Else
           GoTo NameSearch
         End If
      End If
      Exit For
    End If
  Next
 
  Set tblTableDefObj = gdbCurrentDB.CreateTableDef()
   
  'strip off owner if needed
  tblTableDefObj.Name = StripOwner(vToName)

  'create the fields
'  For Each fld In vFromDB.Tabledefs(vFromName).Fields
  For i = 0 To vFromDB.TableDefs(vFromName).Fields.Count - 1
    Set fld = vFromDB.TableDefs(vFromName).Fields(i)
    Set fldFieldObj = vFromDB.TableDefs(vFromName).CreateField(fld.Name, fld.Type, fld.Size)
    tblTableDefObj.Fields.Append fldFieldObj
  Next

  'create the indexes
  If bCreateIndex <> False Then
'    For Each idx In vFromDB.Tabledefs(vFromName).Indexes
    For i = 0 To vFromDB.TableDefs(vFromName).Indexes.Count - 1
      Set idx = vFromDB.TableDefs(vFromName).Indexes(i)
      Set indIndexObj = vFromDB.TableDefs(vFromName).CreateIndex(idx.Name)
      With indIndexObj
        indIndexObj.Fields = idx.Fields
        indIndexObj.Unique = idx.Unique
        If gsDataType <> gsSQLDB Then
          indIndexObj.Primary = idx.Primary
        End If
      End With
      tblTableDefObj.Indexes.Append indIndexObj
    Next
  End If

  'append the new table
  vToDB.TableDefs.Append tblTableDefObj

  CopyStruct = True
  Exit Function

CSErr:
  ShowError
  CopyStruct = False
End Function


Function CopyData(rFromDB As Database, rToDB As Database, rFromName As String, rToName As String) As Integer
  On Error GoTo CopyErr

  Dim recRecordset1 As Recordset, recRecordset2 As Recordset
  Dim i As Integer
  Dim nRC As Integer
  Dim fld As Field

  'open both recordsets
  Set recRecordset1 = rFromDB.OpenRecordset(rFromName)
  Set recRecordset2 = rToDB.OpenRecordset(rToName)
  gwsMainWS.BeginTrans
  While recRecordset1.EOF = False
    recRecordset2.AddNew
    'this loop copies the data from each field to
    'the new table
'    For Each fld In recRecordset1.Fields
    For i = 0 To recRecordset1.Fields.Count - 1
      Set fld = recRecordset1.Fields(i)
      recRecordset2(fld.Name).Value = fld.Value
    Next
    recRecordset2.Update
    recRecordset1.MoveNext
    nRC = nRC + 1
    'this test will commit transactions every 1000 records
    If nRC = 1000 Then
      gwsMainWS.CommitTrans
      gwsMainWS.BeginTrans
      nRC = 0
    End If
  Wend
  gwsMainWS.CommitTrans

  CopyData = True
  Exit Function

CopyErr:
  gwsMainWS.Rollback
  ShowError
  CopyData = False
End Function

0
 
watyCommented:
You can create a new query to populate your new db

INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression


dbs.Execute "INSERT INTO Customers SELECT * FROM [New Customers];"
            
      
0
 
majalaAuthor Commented:
That database isn't always same kind of, so I don't know how many fields there is (does that your comment work anyway?).
I guess I have to create that new database before I can populate it?

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
majalaAuthor Commented:
This gives me an error, that "Sub of function not defined"
   tblTableDefObj.Name = StripOwner(vToName)
and this gives an error, that "Object required"
  Set tblTableDefObj = gdbCurrentDB.CreateTableDef()

0
 
majalaAuthor Commented:
This gives me an error, that "Sub or function not defined"
   tblTableDefObj.Name = StripOwner(vToName)
and this gives an error, that "Object required"
  Set tblTableDefObj = gdbCurrentDB.CreateTableDef()

0
 
watyCommented:
gdbCurrentDB should be your database.
 
NB : you should load the Visdata Sample, All is there.

here is the function StripOwner:

'------------------------------------------------------------
'strips the owner off of ODBC table names
'------------------------------------------------------------
Function StripOwner(rsTblName As String) As String

  If InStr(rsTblName, ".") > 0 Then
    rsTblName = Mid(rsTblName, InStr(rsTblName, ".") + 1, Len(rsTblName))
  End If
  StripOwner = rsTblName

End Function

0
 
majalaAuthor Commented:
There is in CopyStruct-function this function call and in my program there is users new databasename in variable vToName.

  tblTableDefObj.Name = StripOwner(vToName)

Why that StripOwner function returns string after dot? When user gives new databases name MyDB.dbf then result of StripOwner is dbf and result of hole CopyStruct is dbf.dbf

I changed StripOwner like this and now I think it works like it should (user gives new databases name "MyDB.dbf", result of StripOwner is "MyDB" and result of hole CopyStruct is
"MyDB.dbf")

   rsTblName = Mid(rsTblName, 0,InStr(rsTblName, ".") - 1)

Or is there something I don't get?

0
All Courses

From novice to tech pro — start learning today.