Access Data Projects

Up Introduction The Author Quality Specification Design Development Testing Deployment Eye Candy Client Server Thin Client Internationalisation Security Employment Real-Time Metrics Documentation Access Data Projects

Access Data Projects are the bees knees; the best thing since sliced bread.

OK, so it only supports Microsoft SQL Server - not Oracle

.... and doesn't allow attachment to local resources...

..... but it's stable, versatile and reduces development time for enterprise applications.

My wish list for this product is :

bullet

Oracle support

bullet

More than one database to be connected at a time

bullet

Linked tables and local queries.
 

Connection

The main ADP connection is for only one MS SQL server database. That's a shame. I wish that it would support multiple connections, and allow Oracle connectivity.

An ADP stores the database connection string, which makes it vulnerable to being interrogated.  To find out an ADP's connection, view Application.CurrentProject.BaseConnectionString
 

It is better to set the connection when the application opens, and clear it when it closes.

In your initialisation, try something like :

str_Connect = "PROVIDER=SQLOLEDB.1;PASSWORD=MyPassword;PERSIST SECURITY INFO=TRUE;USER ID=MyUID;INITIAL CATALOG=MyDatabase;DATA SOURCE=MyServer;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096"
Application.CurrentProject.CloseConnection
DoEvents
Application.CurrentProject.OpenConnection str_Connect
 

and when exiting perform : Application.CurrentProject.CloseConnection
 

This doesn't mean that you can't access other databases, including Oracle databases, as you can use ADO. However, ADO does not work correctly with continuous forms. Problems can occur with checkboxes, and records holding on to focus.

 

Permissions

As the ADP application you are building will probably not be required to change the structure of the database, it is better to set two sets of permissions into the database.

bullet

Developer : This allows the change of structure

bullet

Application : This allows the change of data

Tables, Queries and Database Diagrams are not really built by MS Access, as the ADP calls the relevant SQL Server tools. If the Application permissions apply, then Access will not be able to perform these tasks. So build Tables, Queries and Database directly with Microsoft Enterprise Manager, using the Developer permissions, and retain the Application permissions for the ADP.

 

 

Tables

 

 

 

Queries

The Query Builder is just the one that belongs to SQL Server. Adequate, but not as flexible as the Access Query-By-Example grid. Remember, if you are calling ADO, you can store the SQL in the code module.

You'll have to be more familiar with MS SQL Server syntax.

 

 

Relationships