Solved

saving part of database

Posted on 1998-08-24
7
122 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

707 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

13 Experts available now in Live!

Get 1:1 Help Now