Code Of The Week Issue #20 for Visual Basic

Visual Basic Code of the Week (COTW)
http://www.codeoftheweek.com
Issue #20
All content and source code is Copyright (c) 1998 by C&D Programming Corp. None of the source can be reprinted in any manner without express written permission of C&D Programming Corp.

Requirements for this Issue

The source code in this issue is designed for Visual Basic 5.0 and higher. With some modifications to the parameters it can be used in VB 3 and 4.

In this Issue

This issue shows how to attach an external table to an Access database. This is useful for situations where you need to access tables in other Access databases or other database formats. We will show an example of how to attach a dBASE III type table. Doing the attachment allows you to access data using a query across multiple databases and database types.

AttachTable

Function AttachTable(DestDB As String, SrcDB As String, sTable As String, Optional sDBType as String) As Integer

Parameters

sDestDB - The destination database. This is where the attachment will be created. Example: C:\DATA\SALES.MDB
sSrcDB - The source database name. This is the database that contains the table to be attached. If you are attaching an Access table, this will contain the complete path and filename of the Access database, such as C:\DATA\SALES.MDB. If you are attaching a dBASE type table (where there is one table per database) this will only contain the complete path, such as C:\DATA. You would then set the sTable parameter to be equal to the filename, such as SALES. It is assumed that dBASE tables have a DBF extension.
sTable - The table name to be attached. This will be the name of the table as it appears in sSrcDB. It will be just the table name for Access type databases and the filename for the dBASE type databases.
sDBType - The type of database you are attaching. This is optional if you are just attaching Access tables. If you want to attach other database types, you need to use the appropriate database name. For example, dBASE 3 is dBASE III. Review the Connect Property in the Visual Basic Help File. It contains the complete listing of the available database types. Some additional examples are: Fox Pro 2.6, dBASE IV and Excel 5.0.

Return Value

None unless an error occurs. If an error occurs it will be raised to the caller. Some situations that this subroutine will fail are:

If the database you are trying to attach is not available.
If you are not allowed to delete a table/attachment that has the same name of the one you are trying to add.

Sample Usage

The below example uses the AttachTable to attach an Access table and a dBASE table. It is assumed that the dBASE tables are located in the directory C:\DATA

    On Error Resume Next

    ' Attach the OLDSALES.DBF table in the C:\DATA directory to C:\DATA\SALES.MDB
    AttachTable "C:\DATA\SALES.MDB", "C:\DATA", "OLDSALES", "dBASE III"
    If Err <> 0 Then
        MsgBox "Error: " & Err.Description
    End If

    ' Attach the Invoices table to REPORT.MDB from SALES.MDB
    AttachTable "C:\DATA\REPORT.MDB", "C:\DATA\SALES.MDB", "Invoices"
    If Err <> 0 Then
        MsgBox "Error: " & Err.Description
    End If

    ' Attach the Customer table to REPORT.MDB from SALES.MDB
    AttachTable "C:\DATA\REPORT.MDB", "C:\DATA\SALES.MDB", "Customers"
    If Err <> 0 Then
        MsgBox "Error: " & Err.Description
    End If

Source Code

Just paste this code into any module (this is the desired way) or form. To do this, open up your project and insert a new Module. Change the name of the module to basDatabase and paste this code into the module.

'----------------------------------------------------------------------
'
'   Module Name:    basDatabase
'   Written By:     C&D Programming Corp.
'   Create Date:    1/7/98
'   Copyright:      Copyright 1997-98 by C&D Programming Corp.  Source
'                   code may not be reproduced except for use in a
'                   compiled executable.  All rights reserved.  If
'                   you would like to reprint any or all of this
'                   code please email us at info@codeoftheweek.com
'
'   Purpose:        Determine the decimal equivalent of a fraction
'                   or whole number.
'
'
'----------------------------------------------------------------------
'
'   sDBType can be any database type, such as dBASE III
'       if you want to attach an Access Table, just
'       leave sDBType blank.
'
Function AttachTable(DestDB As String, SrcDB As String, sTable As String, Optional sDBType As String) As Integer
    Dim dbJet As Database
    Dim tdfAttachedTable As New TableDef
    Dim X As Integer

    On Error GoTo Handler

    ' Open Database
    Set dbJet = OpenDatabase(DestDB)

    ' Make sure link does not already exist.  If it does,
    ' this routine will delete it.  Make sure that you
    ' are not attaching a table that already exists.
    For X = 0 To dbJet.TableDefs.Count - 1
        If dbJet.TableDefs(X).Name = sTable Then
            dbJet.TableDefs.Delete sTable
            Exit For
        End If
    Next

    ' Set connection information.
    tdfAttachedTable.SourceTableName = sTable
    tdfAttachedTable.Connect = sDBType & ";DATABASE=" & SrcDB
    tdfAttachedTable.Name = sTable

    ' Append TableDef object to create link.
    dbJet.TableDefs.Append tdfAttachedTable
    dbJet.Close
    Exit Function

Handler:
    Err.Raise Err.Number, "AttachTable", Err.Description
End Function

This document is available on the web

Paid subscribers can view this issue in HTML format. There is no additional source or information in the HTML formatted document. It just looks a little better. Just point your browser to http://www.codeoftheweek.com/membersonly/bi/0020.html

Summary

That concludes this issue of COTW. We hope you find the source code useful in your development.

The below describes the ways you can supply us some feedback about COTW. We would like to see our members help mold COTW into the best Visual Basic source code resource available. But to do that we need your feedback about what you like and what you do not like about COTW.

Future Issues

We are currently planning for the next several issues of Code of the Week. If you have any suggestions of source code that you would like to see covered, please jump

How to tell us what you think

If you have any suggestions for topics you would like to see covered or questions about this issue, please email them to info@codeoftheweek.com or use online feedback form at http://www.codeoftheweek.com/feedback.html.

If you have any source code you would like to submit for possible inclusion in COTW, please fill out our online submission form at http://www.codeoftheweek.com/submission.html.

Contact Information

C&D Programming Corp.
PO Box 20128
Floral Park, NY 11002-0128
Phone or Fax: (212) 504-7945
Email: info@codeoftheweek.com
Web: http://www.codeoftheweek.com

Subscription Update

Thank you for trying Code of the Week for Visual Basic.

Your free trial expires after you receive your fourth issue. If you want to continue to receive Code of the Week you can get 52 issues of COTW for only $19.95. This is a full year of Visual Basic source code and information to help with all your development. So don't wait, subscribe now! The quickest way to subscribe is to jump to our online order form at http://www.codeoftheweek.com/order.html