MSAccess Connection to Database

Posted on 2005-05-15
Last Modified: 2010-04-17
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


Question by:Chuckbuchan
    LVL 44

    Accepted Solution

    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.


    Author Comment

    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?


    LVL 44

    Expert Comment

    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.



    Author Comment

    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).

    LVL 44

    Expert Comment

    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)

    LVL 44

    Expert Comment


    Author Comment

    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.


    LVL 44

    Expert Comment

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

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Suggested Solutions

    Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
    A short article about a problem I had getting the GPS LocationListener working.
    In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now