subhashini
(Moderator):
hello everbody . A very good evening to all of you. :-) subhashini
(Moderator):
I am sure all of you had a long and fun filled weekend of Holi subhashini (Moderator):
Welcome to today's webchat on Microsoft Access Projects subhashini
(Moderator):
We have Naresh Nichani with us today subhashini
(Moderator):
To give a quick intro about him subhashini
(Moderator):
Naresh Nichani manages a software consulting company ‘GL Infotech’
based in Chennai. He develops and manages projects in Visual Basic
(VB.Net and VB 6.0) and MS SQL Server. He also does VBA programming
in MS-Access, MS-Excel and MS Visio. subhashini
(Moderator):
He has completed his MBA (Finance) from University of Massachusetts,
Lowell and had worked for Village Software (www.villagesoft.com)
for a year. Village Software is a key developer of spreadsheet applications
(the free templates in MS-Office 97 and MS-Office 2000 are from Village
Software). subhashini (Moderator):
He enjoys programming with Microsoft technologies as they are fairly
easy to use and developers can build fairly complex solutions for
customers with visually appealing interfaces quickly. He is currently
working on a DHTML Interface, which encapsulates the Web Browser control
to create a “web” like interface in a Windows Form Application. subhashini (Moderator):
In his spare time he likes to play shuttle, swim and watch movies. subhashini (Moderator):
And Naresh can be reached at nareshnichani@hotmail.com subhashini (Moderator):
Now before we start a few chat rules subhashini
(Moderator):
Please refrain from sending any private messages to the expert during
the chat subhashini (Moderator):
Chat Procedures:
This chat will last for one hour. During this hour, our Experts will
respond to as many questions as they can. Please understand that there
may be some questions we cannot respond to due to lack of information
or because the information is not yet public. We encourage you to
submit questions for our Experts. We ask that you stay on topic for
subhashini (Moderator):
the duration of the chat. This helps the Guests and Experts follow
the conversation more easily. We invite you to ask off topic questions
after this chat is over. subhashini (Moderator):
So lets get the chat started subhashini
(Moderator):
Welcome Naresh Naresh[MVP] (Expert):
Hi Everybody Naresh[MVP] (Expert):
First and foremost let me say MS-Access is my favorite topic as I
find the Form and Report designers in Access very intuitive and powerful Naresh[MVP] (Expert):
Have many of you used MS-Access forms and Reports in your solutions? Naresh[MVP] (Expert):
Microsoft Access supports two file formats MDB (Microsoft Database)
and ADP (Access Database Project). The new ADP format is supported
from Access 2000 onwards (not supported in Access 97 and Access 2). Naresh[MVP] (Expert):
A Microsoft Access project (.adp) is an Access data file that provides
efficient, native-mode access to a Microsoft SQL Server database through
the OLE DB component architecture.
With the MDB format we can connect to SQL Server database with ODBC
using the Jet engine. However with the ADP format we can connect to
SQL database using the OLE DB architecture which is more efficient. Naresh[MVP] (Expert):
An MDB file is a database - it has tables which store REAL data. An
ADP file stores no data - all the tables, views, stored procedures,
user defined functions, database diagrams are stored on the SQL Server.
Only Forms, Reports, Modules and Macros are stored locally in the
ADP file. So the ADP is basically a “Front End” to the
SQL Server “Back End”. You can connect the Access project
to a remote SQL Server database, a local SQL Server database, or a
local installation of SQL Server 2000 Desktop engine (MSDE). Naresh[MVP] (Expert):
So the ADP can function as a “Front End” to Naresh[MVP]
(Expert): • SQL Server database on the web.
• SQL Server database on LAN or local machine • MSDE
database (free SQL Server for small workgroups) Naresh[MVP]
(Expert):
Just imagine many a time you have a SQL database on web - you need
to use Enterprise Manager to work with it. This is good - however
if you do not have Enterprise Manager a very nice option is to create
an Access ADP file. Naresh[MVP] (Expert):
How do we create an ADP file? Naresh[MVP]
(Expert):
From Microsoft Access 2000 onwards when you click File | New you have
the option to create A: - • “Blank database”
(creates a new file in MDB format) • “Project with
existing data” (creates a new ADP which connects to an existing
SQL database) • “Project with new data” (creates
a new ADP and also a new database on a SQL Server instance). Naresh[MVP]
(Expert):
When you open the ADP file in Microsoft Access you see the following
tabs - Naresh[MVP] (Expert):
• Tables - these are table on the SQL Server (not stored in
the ADP) • Queries - these are Views, Stored Procedures
and User Defined Functions all stored on the SQL Server •
Database Diagrams - these are database diagrams stored on the SQL
Server Naresh[MVP] (Expert):
• Forms- Access Forms which allows you to Add/Edit/Delete data
on the SQL Server tables and views. Stored in the ADP File. •
Reports - Access Reports to display data. Stored in the ADP file.
• Pages - also called Data Access Pages - these are HTML pages
which allow you to enter/edit data in the database. • Macros
- small programs to automate repetitive tasks. • Modules
- VBA Modules to automate the ADP. Naresh[MVP]
(Expert):
SO basically an ADP allows you to work with all SQL Objects (Tables,
Views, Stored Procedures, User Defined Functions) etc - however all
these "data" related objects are not stored in ADP but stored
on the SQL Server itself Naresh[MVP] (Expert):
However they key to ADP file format is "Forms" and "Reports"
- you can design Access Forms and Reports to enter and display data Naresh[MVP] (Expert):
Nowmally we use VB6 or .Net Forms or Web Forms to display data from
SQL Server Naresh[MVP] (Expert):
One more option is to use ADP. Advantages are Access Forms are very
easy to use and build prototypes Naresh[MVP]
(Expert):
Also all you have to do is ship on ADP to another user - no building
installs to distribute your solution - as long as your user has MS-Access
2000 or later. Naresh[MVP] (Expert):
Consider this example - I want to build some reports for a SQL database
on web. Naresh[MVP] (Expert):
I could write some ASPX pages if it was for general use. Naresh[MVP]
(Expert):
If it is for internal company use - just for a few people and they
have MS-Access building an ADP is much much quicker Naresh[MVP]
(Expert):
The Reports can be done fast and a neat UI created as well Naresh[MVP]
(Expert):
Where does the ADP store the SQL Server connection information? Naresh[MVP] (Expert):
It is stored in the ADP file itself. If you want to change the connection
information click File | Connection when the ADP is open. Here you
can set the
1. SQL Server Name or IP Address
2. Authentication - Windows or SQL. If SQL authentication you have
option to “remember the password” or to use a “blank
password”.
3. Database Name Naresh[MVP] (Expert):
Since ADP work only with SQL Server one question can be do I need
SQL Server to work with ADP's? Naresh[MVP]
(Expert):
You can still use the ADP format - however install the free SQL Server
2000 Desktop Engine. This is “free” and is part of the
Office 2000 and later CD and also downloadable from Microsoft web
site. Naresh[MVP] (Expert):
Microsoft SQL Server 2000 Desktop Engine is a technology that provides
local data storage in a format compatible with Microsoft SQL Server
2000. You can also use SQL Server 2000 Desktop Engine as a remote
data storage solution. Think of SQL Server 2000 Desktop Engine as
a client/server data engine alternative to the file server Microsoft
Jet database engine SQL Server 2000 Desktop Engine runs under Microsoft
Windows 2000 or later. It is designed and optimized for use on smaller
computer systems, such as a single-user computer or small workgroup
server. Naresh[MVP] (Expert):
Because SQL Server 2000 Desktop Engine is based on the same data engine
as SQL Server, most Microsoft Access projects or client/server applications
run on either version. However unlike SQL Server, SQL Server 2000
Desktop Engine has a 2 gigabyte database size limit, and when using
transactional replication, can't be a replication publisher (although
it can act as a replication subscriber). Naresh[MVP]
(Expert):
So bascially we have the option to Micrsoft SQL Server 2000 Desktop
Engine as a backend to your ADP projects. This is free database engine.
Once clients and users like the prototype we can scale to a real SQL
backend. Naresh[MVP] (Expert):
When to use SQL Server 2000 Desktop Engine subhashini
(Moderator):
We apologise for a lil inconvenience subhashini
(Moderator):
Guys Naresh ahs accidentally logged out , but will be soon logged
in subhashini (Moderator):
So please hold on for few mins subhashini
(Moderator):
naresh should be in any minute subhashini
(Moderator):
meanwhile take time to sumbit any question subhashini
(Moderator):
by using teh submit a question "radial" button subhashini
(Moderator):
Naresh is back guys subhashini (Moderator):
shoot your questions to him ! Naresh[MVP]
(Expert):
HI Sorry my machine got rebooted :$ Naresh[MVP]
(Expert):
OK Let me take some questions now Naresh[MVP]
(Expert): Q: I have a ASP Project with MS-Access,
How can build the setup project or Exe? A: ASP Projects
normally have no connection to ADP - they are deployed on web server
(IIS). Am I missing something. Access runtime is only for Access projects
running on desktops (MDB or ADP) Naresh[MVP]
(Expert): Q: what is the latest version of access
database what are the new changes? i have worked with ACCESS 97 A: Latest version of MS-Access is MS-Access 2003. New features
include enahncements to ADP FOrmat to work with SQL Server, Security,
Better Data Access Pages Naresh[MVP] (Expert): Q: This seems like an office add on, what's interesting for
developer community? A: This is not a Office Add On - it
is a developer tool to build Front Ends to SQL databases. Naresh[MVP]
(Expert): Q: can i fetch queries from an oracle
server which is a linked server with my sql server ? A:
I think so In Access 2003 ADP a new feature is to Link to other SQL
Servers or other database and Link their tables/views. With ADP Open
Right click in Tables tab and click "Link Tables..." Naresh[MVP] (Expert): Q:
How all these things are important to a typical developer? A:
As a developer you develop Front End solutions to databases. We sometimes
use Access MDB format as a Front End - an idea to consider is the
ADP format which is a optimized Front End to SQL Server. Naresh[MVP]
(Expert): Q: No, what I asked is can it be a
replacement for other reporting tools, like Crystal reports, you mentioned
aspx, can I use it with aspx for reporting A: It depends
- it can be. Crystal Reports can be distributed with a runtime so
can Access Reports - it depends which you like or prefer. Access reports
can be easier to develop than Crystal Reports and easier for users
to change as well if needed Naresh[MVP]
(Expert):
With Access 2003 you have a MS-Access runtime which ships with MS-Access
developer Extensions - with this you can build solution in ADP and
roll out to anybody who does not have Access as well subhashini
(Moderator):
Hi guys , we have the last 15 mins left for teh chat to conclude Naresh[MVP] (Expert):
I once had a problem like this - my client runs a trade show company
and we needed to build a solution on Win XP. Win XP does not allow
install of Enterprise Manager. So we installed SQL Server Desktop
Edition with ADP to Edit the tables etc and do some reporting. Data
is etnered in web pages via a questionniare format - reporting was
in ADP. Naresh[MVP] (Expert):
Basically this is a option waiting to be used and not many people
use it subhashini (Moderator):
So please feel free to ask your last few questions to the expert Naresh[MVP] (Expert):
Creating and Deploying Access Solutions with the Microsoft Data Engine Naresh[MVP] (Expert): http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/msdedeploy.asp Naresh[MVP] (Expert):
Data Structure Differences between Access and SQL Server Naresh[MVP]
(Expert): http://www.databasejournal.com/features/mssql/article.php/1490561 Naresh[MVP] (Expert):
MS Access for the Business Environment: MS Access as a Documentation
Tool: Database Diagramming Naresh[MVP]
(Expert): http://www.databasejournal.com/features/msaccess/article.php/3388551 Naresh[MVP] (Expert):
Any other questions on Access in general? Naresh[MVP]
(Expert):
It is normally better to use VBA for MS-Access development - you can
access the entire object model. For VBA tutorials look at http://www.mvps.org/access/
- this is the best site for Access developemnt (Macros and VBA) Naresh[MVP] (Expert):
Another popular Access page for programming http://www.granite.ab.ca/accsmstr.htm Naresh[MVP] (Expert):
What about Outlook Express? Naresh[MVP]
(Expert):
You normally program Outlook and not Outlook Express Naresh[MVP]
(Expert):
For Outlook programming this is the best http://www.slipstick.com/ Naresh[MVP] (Expert):
In Access you have option to use Data Access Pages (DAP) - these are
HTML pages designed inside MDB or ADP and provide Access to backend
Access database on server Naresh[MVP]
(Expert):
Look at Pages tab in Access 2000 onwards Naresh[MVP]
(Expert):
Will work well on Intranet subhashini
(Moderator):
guys , time up! subhashini (Moderator):
and this brings us to the end of the chat. subhashini
(Moderator):
Please feel free to mail naresh for any queries regarding today's
topic at nareshnichani@hotmail.com Naresh[MVP]
(Expert):
Thanks everybody sorry again for machine problems subhashini
(Moderator):
thanks naresh for this informative session subhashini
(Moderator):
I hope all of you enjoyed it and feel free to pool in your feedback
at commind@microsoft.com subhashini (Moderator):
Thanks again and have a lovely evening :-) Naresh[MVP]
(Expert):
You can develop any app database related - suggest VBA with Forms+Reports subhashini (Moderator):
Thanks Naresh
