Microsoft KB Archive/108147: Difference between revisions
m (Text replacement - """ to """) |
m (Text replacement - ">" to ">") |
||
(One intermediate revision by the same user not shown) | |||
Line 107: | Line 107: | ||
'Strip off owner if necessary: | 'Strip off owner if necessary: | ||
If InStr(to_nm, ".") | If InStr(to_nm, ".") <> 0 Then | ||
to_nm = Mid(to_nm, InStr(to_nm, ".") + 1, Len(to_nm)) | to_nm = Mid(to_nm, InStr(to_nm, ".") + 1, Len(to_nm)) | ||
End If | End If | ||
Line 123: | Line 123: | ||
'Create the indexes: | 'Create the indexes: | ||
If create_ind | If create_ind <> False Then | ||
For i = 0 To from_db.TableDefs(from_nm).Indexes.Count - 1 | For i = 0 To from_db.TableDefs(from_nm).Indexes.Count - 1 | ||
Set ind = New Index | Set ind = New Index | ||
Line 129: | Line 129: | ||
ind.Fields = from_db.TableDefs(from_nm).Indexes(i).Fields | ind.Fields = from_db.TableDefs(from_nm).Indexes(i).Fields | ||
ind.Unique = from_db.TableDefs(from_nm).Indexes(i).Unique | ind.Unique = from_db.TableDefs(from_nm).Indexes(i).Unique | ||
If gstDataType | If gstDataType <> "ODBC" Then | ||
ind.Primary = from_db.TableDefs(from_nm).Indexes(i).Primary | ind.Primary = from_db.TableDefs(from_nm).Indexes(i).Primary | ||
End If | End If |
Latest revision as of 17:44, 20 July 2020
Article ID: 108147
Article Last Modified on 1/8/2003
APPLIES TO
- Microsoft Visual Basic 3.0 Professional Edition
This article was previously published under Q108147
SUMMARY
Below is an example of how to copy a table from one database to another using the Professional Edition of Visual Basic version 3.0.
MORE INFORMATION
Sample Program
The following sample code contains two functions taken almost unchanged from the VISDATA sample project, from the code module VISDATA.BAS. The Command1_Click procedure shown below invokes these two functions, CopyStruct and CopyData. NOTE: The VISDATA.MAK project file is installed in the Visual Basic SAMPLES\VISDATA directory.
This example assumes that the databases have Microsoft Access format. The same techniques apply to the other supported database types.
- Start a new project in Visual Basic. Form1 is created by default.
Add a command button to Form1. Add the following code to the Command1 Click event:
Sub Command1_Click () Dim dbsource As database Dim dbdest As database ' The following hard-coded database names could be changed to ' selections from a text box, list box, or combo box to make the ' program more generic: Set dbsource = OpenDatabase("c:\vb3\biblio.mdb", True, True) Set dbdest = OpenDatabase("c:\vb3\test1.mdb", True, False) Print CopyStruct(dbsource, dbdest, "titles", "ctitles", True) Print CopyData(dbsource, dbdest, "titles", "ctitles") dbsource.Close dbdest.Close End Sub
Add the following code to the General Declarations section of Form1:
'Place the following Function statement on one, single line: Function CopyStruct (from_db As Database, to_db As Database, from_nm As String, to_nm As String, create_ind As Integer) As Integer On Error GoTo CSErr Dim i As Integer Dim tbl As New Tabledef 'table object Dim fld As Field 'field object Dim ind As Index 'index object 'Search to see if the table exists: namesearch: For i = 0 To to_db.TableDefs.Count - 1 If UCase(to_db.TableDefs(i).Name) = UCase(to_nm) Then If MsgBox(to_nm + " already exists, delete it?", 4) = YES Then to_db.TableDefs.Delete to_db.TableDefs(to_nm) Else to_nm = InputBox("Enter New Table Name:") If to_nm = "" Then Exit Function Else GoTo namesearch End If End If Exit For End If Next 'Strip off owner if necessary: If InStr(to_nm, ".") <> 0 Then to_nm = Mid(to_nm, InStr(to_nm, ".") + 1, Len(to_nm)) End If tbl.Name = to_nm 'Create the fields: For i = 0 To from_db.TableDefs(from_nm).Fields.Count - 1 Set fld = New Field fld.Name = from_db.TableDefs(from_nm).Fields(i).Name fld.Type = from_db.TableDefs(from_nm).Fields(i).Type fld.Size = from_db.TableDefs(from_nm).Fields(i).Size fld.Attributes = from_db.TableDefs(from_nm).Fields(i).Attributes tbl.Fields.Append fld Next 'Create the indexes: If create_ind <> False Then For i = 0 To from_db.TableDefs(from_nm).Indexes.Count - 1 Set ind = New Index ind.Name = from_db.TableDefs(from_nm).Indexes(i).Name ind.Fields = from_db.TableDefs(from_nm).Indexes(i).Fields ind.Unique = from_db.TableDefs(from_nm).Indexes(i).Unique If gstDataType <> "ODBC" Then ind.Primary = from_db.TableDefs(from_nm).Indexes(i).Primary End If tbl.Indexes.Append ind Next End If 'Append the new table: to_db.TableDefs.Append tbl CopyStruct = True GoTo CSEnd CSErr: CopyStruct = False Resume CSEnd CSEnd: End Function 'Place the following Function statement on one, single line: Function CopyData (from_db As Database, to_db As Database, from_nm As String, to_nm As String) As Integer On Error GoTo CopyErr Dim ds1 As Dynaset, ds2 As Dynaset Dim i As Integer Set ds1 = from_db.CreateDynaset(from_nm) Set ds2 = to_db.CreateDynaset(to_nm) While ds1.EOF = False ds2.AddNew For i = 0 To ds1.Fields.Count - 1 ds2(i) = ds1(i) Next ds2.Update ds1.MoveNext Wend CopyData = True GoTo CopyEnd CopyErr: CopyData = False Resume CopyEnd CopyEnd: End Function
- Start the program or press the F5 key.
- You can check to see if the table was copied correctly to the TEST1.MDB database by opening TEST1.MDB with Microsoft Access or with the Data Manager provided with Visual Basic. You can run the Data Manager program from the Window menu in Visual Basic or from the Windows File Manager run DATAMGR.EXE in the Visual Basic directory.
REFERENCE
The VISDATA.MAK file installed in the VB3\SAMPLES\VISDATA directory loads extensive examples of data access. The VISDATA sample program uses every data access function in Visual Basic. Refer to the VISDATA source code for examples that show how to use each data access function.
Additional query words: 3.00
Keywords: KB108147