Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

saving part of database

Posted on 1998-08-24
7
Medium Priority
?
133 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 300 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

721 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