Mendip Data Systems

Database applications for businesses and schools

LogoTransparent

Linked No Tables

Version 2.0   06/02/2019

Click any image to view a larger version ...

Screenshots

Code Samples for Businesses, Schools & Personal Use

This example app was originally created in response to a user question at Access World Forums.

The question referred to ways of preventing tables and queries from being copied to another application

 

Although there were many issues with the ideas behind original question, I came up with this ‘linked no tables’ DEMO application as a possible solution

 

This is a split database where the front end (FE) has no linked tables.

For the purposes of this DEMO the back end (BE) file needs to be saved in the same folder as the FE.

 

The BE contains 1 ‘deep hidden’ table (Table1) though it would work equally well with a standard table.

The BE file is encrypted with password 'isladogs' if you want to check it out.

 

The FE file has not been encrypted in this DEMO so you can view the code.

It has 2 forms and 1 report but, as stated above, no linked tables.

 

None of these 3 objects have a permanent record source

Instead the record source for each object is set automatically to the BE table when the object is opened. It is cleared again when the object is closed

 

The result is that it behaves exactly like any standard split database

However, there are no linked objects and therefore no connection strings visible in MSysObjects.

 

If the FE was converted to an ACCDE file, it would NOT be possible for end users to deduce the BE password and gain access to the BE tables (at least not without some VERY serious hacking!)

Return to Code Samples Page LinkNoTables1 LinkNoTables2 LinkNoTables3

FE with no linked tables

Form showing data from BE table

Form design with no record source

Backend with no data table visible

This code sets the record source for each form:

 

 Private Sub Form_Load()

 

     Dim rst As DAO.Recordset

     Me.RecordSource = "SELECT * FROM Table1 IN '' [MS Access;PWD=isladogs;DATABASE=" & CurrentProject.Path &

         "\BE.accdb];"

   

     Set rst = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset, dbSeeChanges)

   

     With rst

         .MoveLast

         .MoveFirst

     End With

   

     'get record count

     Me.txtTotal = Me.RecordsetClone.RecordCount

 

     'combo row source

     Me.cboLastName.RowSource = "SELECT DISTINCT Table1.LastName FROM Table1 IN '' [MS  Access

        ;PWD=isladogs;DATABASE=" & CurrentProject.Path & "\BE.accdb] ORDER BY Table1.LastName;"

   

     Me.cmdPrint.SetFocus

 End Sub

This code clears the record source after use:

 

 Private Sub cmdQuit_Click()

     Me.RecordSource = ""

     Application.Quit

 End Sub

Click to download :  LinkedNoTables      Approx 0.5 MB (zipped)