We help IT Professionals succeed at work.

which is the best approach for MS Access(only forms should be there) to SQL Server(Only tables)

Hi ,
   
1.i have 4 MS access MDB(Forms with Tables).
2.my requirement is need to Consolidate DB and keep all the Forms in Single MDB.Tables needs to migrate in SQL Server 2008.
3.currently i am using ADP Approach to Connect MSaccesss to sql server in this approach all tables which are there in SQL Server are listed in MS Access.i need to prevent this(None of the tables to be displayed in MS Access)
4.one more thing i need to know where to keep connection string in common place for through out this application

suggest which is the best approach to connect MS Access to sql server(Tables should not be listed in MS Access)
Comment
Watch Question

Database Developer
CERTIFIED EXPERT
Commented:
Oh I'd say that anyone telling you what the best approach is would be doing you a disservice.
All you'll get is opinions.
But let's start with a question...
Why do you want no local tables?  As a security precaution?  (That's not an unreasonable consideration.)
But, of course, as the developer it your role to keep users in the application (your forms UI) and not looking at the database window (or Nav Pane).
Startup options.  Locking out Shift override etc.  Making sure the user finds it prohibitively difficult to ever get to see those linked tables (and any UI component that you didn't create).

By effectively removing the possibilty of connected tables in an ADP or linked tables in an MDB (/ACCDB) you're making development more work.  If you're OK with that then that's OK, though if you still plan on using bound forms - I'd suggest having those linked tables while developing though - giving you more convenient working environment.  However you choose to deploy.

Without linked tables, you choices are pretty much unbound forms (recordsets and update statements), bound forms to local tables (filled and updated from the server tables through code), forms bound to externally sourced queries or bound recordsets (recordsets opened on server data but bound directly to your forms).

I wouldn't say any one is a stand out "best" approach. (There would usually be reasons determining any specific choice.)
But a bit more information on your reasoning - might help make sure decisions are made for the right reasons.

Cheers.
Commented:
I assume the four mdb's contain identically formatted tables and forms - no reports?  Somewhat like RegionA.mdb, RegionB.mdb, RegionC.mdb, RegionD.mdb.  It makes sense to amalgamate these mdb's into one - depending on where you are in the food chain (hierarchy).  Daily, weekly, monthly, yearly updates can be imported seamlessly.  However, to insure A, B, C, and D have nothing but Forms and Reports in their application, it is probably best to create mde's for each of those users.

Author

Commented:
partially accepted
Leigh PurvisDatabase Developer
CERTIFIED EXPERT

Commented:
Partially involved.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.