Attaching SQL tables questions?? 
Author Message
 Attaching SQL tables questions??

Quote:

>1)  When attaching SQL tables (i.e. Oracle) to an Access MDB in a multiuser
>environment should all the users connect to the one MDB or should each user
>have their own local copy of the MDB?

Local copies.

You may also wish to have a shared Access MDB, for any shared data
that doesn't reside on the server (chiefly configuration-type
information).

In the past I've even had a local copy of tables in a local MDB, still
within this general type of architecture. The reason was rather
unusual though; internationalisation of all my program text, loaded at
run-time from this table. For speed reasons I needed to open this
table as a table, rather than a dynaset, and so needed it locally.

Quote:
>2)  Where is page/record locking occurring in the above scenario, in the
>MDB file or at the server level?

If you tell Jet to use SQL passthrough, then the locking is done by
the server (usually page locking for most servers).

If you use attached tables from the server and have Jet handle
querying for you, then page locking will be done by Jet.

Quote:
>3)  When attaching a SQL table which is a better scenario: attaching tables
>in VB code (i.e. Form_Load) event, or pre-attach the SQL tables to the MDB
>within Access 2.0?

Do it with both (to a point).

My practice is to start VB programs of any complexity from a Sub Main,
rather than loading an initial form. I then open a single global
database object, referring to my local "desktop" MDB file. Normally
these attached tables will have been pre-attached to this desktop
database, but at installation time (or first run) and when the
database is opened at program start-up there is a piece of VB code
that prompts for a new path to the source and re-makes the
attachments.

Mail me if you're after a code fragment to do attaches from VB.

--

Global Cheese Online is a phenomenal cheese resource!
<A HREF=" http://www.*-*-*.com/ ~hope/"></A>



Thu, 30 Jul 1998 03:00:00 GMT  
 Attaching SQL tables questions??

Quote:

> 1)  When attaching SQL tables (i.e. Oracle) to an Access MDB in a multiuser
> environment should all the users connect to the one MDB or should each user
> have their own local copy of the MDB?

That depends. I favor having each user having his/her own copy of
the attachment MDB, in case the app wants to create temporary or
scratch tables. In this case, you might want to consider using
App.PrevInstance to prevent more than one instance of the app
from running simultaneously on the same machine to prevent
multiple instances from clobbering each other's scratch tables.
Otherwise, you'll need to investigate ways to generate unique
scratch table names at run-time.

Quote:
> 2)  Where is page/record locking occurring in the above scenario, in the
> MDB file or at the server level?

Oracle always handles the locking of Oracle tables, reagrdless of
whether attachments to those tables exist in an MDB or not.

Quote:
> 3)  When attaching a SQL table which is a better scenario: attaching tables
> in VB code (i.e. Form_Load) event, or pre-attach the SQL tables to the MDB
> within Access 2.0?

Pre-attaching makes loading the application much faster, but
you should provide a means to let the user reattach or at least
refresh the attachments, in case changes are made to the Oracle
database.

[chomp]

--

WARNING: I cannot be held responsible for the above        They're   coming  to
because  my cats have  apparently  learned to type.        take me away, ha ha!



Sat, 01 Aug 1998 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Attaching SQL tables questions??

2. Attached table and not attached table

3. Cannot delete rows in SQL attached table

4. Attaching to SQL server tables

5. Updating attached SQL Server Tables

6. Acc2000: Access tables attached to SQL-Server won't update

7. SQL Server with Access Attach table?

8. Open attached SQL Server table w/out prompting

9. Attach MS SQL tables via ODBC

10. Attached SQL Server Table

11. Attached SQL Server Table

12. Attached tables, identifying users and un-attaching

 

 
Powered by phpBB® Forum Software