MSAccess Connection to Database

I have some few Questions regarding How MSAccess Connects to other databases:

1- In what circumstances Should I use :
   Set Dbs=DAO.DBengine.Workspaces(0).opendatabase(pathtothedatabase)
instead of
  dim dbs as database
  Set Dbs=dbs.opendatabase(pathtothedatabase)

2- Is there anyway to Connect to SQL server Database programmaticaly without going   through ODBC datasource wizard?

3- Pass-through query are assumed to be the fastest way to get responses from SQL server database. Does Pass-Through query uses the same SQL statement that is used in SQL Server Query analyzer? if so , is it possible to copy an SQL statement from the Query analyzer and paste it in Pass-through query window and run it?

4-How can I create a stored procedure and/or triggers from MSAccess?
5-Is MSAccess a 03 tier environment? please explain the concept of 03 tiers if you can.

I will much appreciate your help


Who is Participating?
Arthur_WoodConnect With a Mentor Commented:
1) the two are essentially doing EXCATLY the same thing.  You would use the former if your REALLY felt compelled to type all the extra characters, because the end result is PRECISELY the same thing.

2) Yes, it is actually quite easy if you are willing to switch from DAO to ADO as the data connection technology.

5) NO, Access is not a Database Server, and should not be used in a traditional Client/Server environment.  When a Table is opened from Access, the ENTIRE table will be transfered from the MDB to the code, and this can create a real bottelnexct if the MDB is on a remote PC, accessed over a network.

In a true 3-tiered environment, Tier 1 is the user Interface, and handles getting values to and from thebuser, and NOTHING else - there is little or no 'processing' in the User Interface tier - for instance, data validation should not be conducted in the User Interface tier (is the user authorised to view the data, is the user OLD enough for the rules of the application, or from the proper location - or what ever happen to be the business rules that apply to the application).  The user interface tier is ONLY responsible for getting the data to and/or from the user.

The Middle Tier (Tier 2) is where the bulk of the 'processing' of the data takes place - Business rules are applied, and the bulk of the calculations that might be relevasnt to the application.  Thses should be INDEPENDENT of how the data is gotten from (or presented to) the user (User Interface tier) and also INDEPENDENT of how the data is actually stored (the actual database tables).  Business Rules are the 'rules' that control how the applciation 'works' - are there age constraints, experience constraints, gender restrictions, certain pro essing that is relevant if the user lives in one location as opposed to another,... this list can be quite long, and is VERY specific to the requirements of the application, and also these rules can frequently modified, without changing the user interface.

Tier 3 - the Data Access layer, is simply responsible for storing and retrieving the data and should not be involved in doing the Business processing of the applications (as these can change, sometimes frequently) and should NOT be involved in how the data is presented to (or gotten from) the User.  This layer is typically on a separate Computer (if possible) called the DataBase Server (though this can also be accomplished with SOFTWARE that places the role of the Database Server (SQL Server, ORACLE, mySQL are all examples of Database Server programs) Note that Access is NOT a Database Server.

ChuckbuchanAuthor Commented:
let me make some points about what I asked and what you answered

[ My Question # 2 says - Is there anyway to Connect to SQL server Database programmaticaly without going   through ODBC datasource wizard?
your answer says : 2) Yes, it is actually quite easy if you are willing to switch from DAO to ADO as the data connection technology.]

Can you give me an example on how to do that?

I would like you to give me an answer for question #3 and #4 if possible.

As for the 03 tiers question: I still couln't get the grasp of the middle tier [the business logic]  an example would help.

if tier1 : has the compiled application that gives a user interface example a VB compiled application

and the tier3: the server database example sql servere database(tables,queries,views,stored procedures,triggers)

what would be in tier2?


set a reference to the ActiveX Data Objexct Library

then you would have code like this:

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset
Dim strSQL as String

set cn = New ADODB.Connection

cn.ConnectionString = "Provider=sqloledb;" & _
           "Data Source=myServerName;" & _
           "Initial Catalog=myDatabaseName;" & _  ' you will need to change the Data of the Server instance, the name of the
Database, your Username and Your passeword for the Database
           "User Id=myUsername;" & _


strSQL = "Select * from <yourTable>"

set rs = cn.Execute(strSQL)

You CANNOT create Stored Procedures or Triggers from Access.  That MUST be done entirely within the SQL Server database - Access does NOT have anything like a Stored Procedure or Triggers, so #4 is a meaningless question.

The middle tier is where you would write Classes and DLLS in order to carry out any business processing loginc that is necessary for your application.  You can have all of the processing coce in a SINGLE program (which manages the User interface, the business processing logic and the movement of the data to and from the database, but such a program IS NOT 3-tier.  It is Sinlge tier program, and almost impossible to maintain.  That was not what you asked.  You asked to describe what makes up a 3-tier application, and I gave you that definition.  Access DOES NOT qualify as a 3-tier application.

All three tiers can be done using VB , typically Tier 1 would be a VB 6 (or VB.NET) EXE, which includes support DLLS that comprize Tiers 2 and 3, to isolate the Business Logic in one set of DLLS, and the Data Access logic in a separate set of DLLs.

SQL Server and the necessary DLLS to encapsulate the movement of data to and from the database itself would make up tier 3.


Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

ChuckbuchanAuthor Commented:
Thanks   for your help Arthur_Wood .

I am just wondering if there is any tool that separate the tier1 and tier2 , because initially the developer write an application(code) in  one machine then compile it (so every thing is in this machine except for the data which is in the server).

Is there a tool that separate the tier1 and tier2 ?

If you have developed a 03 tier application or you know the concept, could you give me some details on how you separated tiers?

I heared that DLLS and custom classes are stored in middle tier, but how did they separate them from tier1, seeing that when we develop an appication we work in one machine where the development software is(example VB) and write a code that queries the data in another machine(example SQL server).

Tier 1 and 2 are not separate boxes.  tier 1 code is the code for the user interface, while tier 2 code is in one or more DLLS (possibly developed in Visual Basic, or another programming language, that allows you to write code and compile that code into a DLL).  That/those DLLS are then included in the coding of the User Interface.  Once the application is compiled, you will have 1 exe.  If it is really important to use distributed processors, then the DLLs can be installed under COM+, but they will still be used in the coding for the User Interface EXE.

I would suggest that you might want to look into Rocky Lhotka's book:

Visual Basic 6 Business Objects
Rockford Lhotka
WROX Press
ISBN 1-861001-07-X

'Business Objects" in this context, is another term meaning Tier 2 code  (do NOT confuse this book with the Company named Business Objects - the current owner of Crystal Reports)

ChuckbuchanAuthor Commented:
As for the open questions, I am aware of that.

Those open questions either they didn't get the right answers that would have solved the issue. either I need to go back for a few of them and retry the suggested answers from Experts, But most of them I am sure they didn't get the right answer.

I just feel that it's unfair to give points to an answer that I am not sure it's the good one, and some other people they pick it up and use it , if they have similar issues, this would mess up their work and they would never trust this site.

I have unlimited points, but I prefer giving 500 pts for a simple questions but when I am sure it is the corret one, rather than giving just 50 or 60 pts to an answer that I am not sure is correct in order to clean my open questions.

I usually, get excited when I try someone else answer that I tested and convinced that is working fine, most often I increase the points to 500, because many people get benifited from that correct answer.


Then ask that the questions be deleted by posting a 0 point request to Community Support to have the questions removed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.