Databases - Remote Access

This page discusses how to access those resources (tables, forms, and reports) not located in the current database.

Paradox | MS Access | Delphi | Visual Basic | Java

Paradox

Paradox is a file based system - each directory is a database, tables, forms, and reports are simply files.

The best way to access a resource in a different database (directory) is to use an alias.

MS Access 97

Each MS Access database is stored as an mdb file.

In order to use a remote MS Access table, start to create a new table and select Link Table from the options list. Then simply navigate the the appropriate mdb file and select the tables you want to use.

For non-MSAccess databases, select Link Table from the options list. Then select ODBC Databases in the Files of Type filter box (it is the last option).

From MS Access, you are not allowed to link to an MS Access database using ODBC. From all other environments, you are allowed to use ODBC.

Linking Tables via Code

DoCmd.TransferDatabase [transfertype], _
   databasetype, databasename[, objecttype], _
   source, destination[, structureonly][, saveloginid]

transfertype	Export, Import, Link
databasetype	(no examples)
databasename	Fully qualified database name
objecttype	Table, Form, Report, etc. - only tables can be linked
source	Form or Report name
destination	Name of the resource in the destination database
structureonly	For tables only - true=just definition, no data
saveloginid

Linking Tables via Code (II)

The following code shows how to link to another MS Access database. The help files include the parameters typical for linking to other types of databases.

Dim tempDatabase  As Database   ' Could use CurrentDB instead
Dim tempTableDef  As TableDef
Dim tempRecordset As Recordset

Set tempTableDef = tempDatabase.CreateTableDef("AnyName")
tempTableDef.Connect = _
  ";DATABASE=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
tempTableDef.SourceTableName = "Employees"
tempDatabase.TableDefs.Append tempTableDef

Set tempRecordset = tempDatabase.OpenRecordset("AnyName")

OpenRecordset may also apply to tempTableDef
If true, then it would not be necessary to append the TableDef to a Database.

Forms and Reports

MS Access does not provide a way to directly use forms and reports in a separate database. Instead, you have to call a subroutine which then opens the desired resource.

The first step is to define a link. This is a little tricky because the required menu selection won't be avalable unless a module window is active. (In the help, linked mdb files are called libraries. Search the MS Access 97 help for library databases / Set a Reference... for more details.) Basically

Open a module window
From the menu, select Tools / References... / Browse
Select the applicable mdb file

In code, the References collection contains all currently set references.

Dim tempRef As Reference

  ' This line will add a new reference
  ' The word set is required, otherwise you get errors
set tempRef = References.AddFromFile ("databaseXX.mdb")

  ' Alternate syntax
References.AddFromFile ("databaseXX.mdb")

Once the link is defined, you can call any code stored in a module, but you can not directly open a form or call code attached to a form.

[Reference Name].MethodName (parameters)
[Reference Name].ModuleName.MethodName (parameters)

Reference Name is the string in the Tools / Options... / Advanced / Project Name property of the library file. By default, it is set to the name of the mdb file (without the extension). However, the developer may change it to anything.

The following code was tested. When called, the subroutine in the remote (library) mdb file opened the appropriate form and used the data in the library file.

 ' Attached to a button
[Inventory Control1].Module1.Open_Suppliers

 ' In Module1 of Inventory Control1.mdb
Sub Open_Suppliers()
  OpenForm "suppliers"
End Sub

Use square brackets if the reference contains spaces.

In order to see new subroutines in the library, I had to close the current database and re-open it.

Delphi 5.0

Link databases using ODBC.

Forms and reports are true exe files - just call them.

Visual Basic 6.0

Link databases using ODBC.

Forms and reports are true exe files - just call them.

Java

Author: Robert Clemenzi - clemenzi@cpcug.org