Solved

saving part of database

Posted on 1998-08-24
7
125 Views
Last Modified: 2010-04-30
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??  
 
 
   
 
0
Comment
Question by:majala
  • 4
  • 3
7 Comments
 
LVL 14

Expert Comment

by:waty
ID: 1430620
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
 

Author Comment

by:majala
ID: 1430621
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
 
LVL 14

Accepted Solution

by:
waty earned 100 total points
ID: 1430622
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:majala
ID: 1430623
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
 

Author Comment

by:majala
ID: 1430624
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
 
LVL 14

Expert Comment

by:waty
ID: 1430625
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
 

Author Comment

by:majala
ID: 1430626
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now