Microsoft Access Development

Access stores all database tables, queries, forms, reports, macros, and modules in the Access Jet database as a single file.
For query development, Access offers a "Query Designer", a graphical user interface that allows users to build queries without knowledge of structured query language. In the Query Designer, users can "show" the datasources of the query (which can be tables or queries) and select the fields they want returned by clicking and dragging them into the grid. One can set up joins by clicking and dragging fields in tables to fields in other tables. Access allows users to view and manipulate the SQL code if desired. Any Access table, including linked tables from different data sources, can be used in a query.
Access also supports the creation of "pass-through queries". These snippets of SQL code can address external data sources through the use of ODBC connections on the local machine. This enables users to interact with data stored outside the Access program without using linked tables or Jet. Users construct the pass-through queries using the SQL syntax supported by the external data source.
When developing reports (in "Design View") additions or changes to controls cause any linked queries to execute in the background and the designer is forced to wait for records to be returned before being able to make another change. This feature cannot be turned off.
Non-programmers can use the macro feature to automate simple tasks through a series of drop-down selections. Macros allow users to easily chain commands together such as running queries, importing or exporting data, opening and closing forms, previewing and printing reports, etc. Macros support basic logic (IF-conditions) and the ability to call other macros. Macros can also contain sub-macros which are similar to subroutines. In Access 2007, enhanced macros included error-handling and support for temporary variables. Access 2007 also introduced embedded macros that are essentially properties of an object's event. This eliminated the need to store macros as individual objects. However, macros were limited in their functionality by a lack of programming loops and advanced coding logic until Access 2013. With significant further enhancements introduced in Access 2013, the capabilities of macros became fully comparable to VBA. They made feature rich web-based application deployments practical, via a greatly enhanced Microsoft SharePoint interface and tools, as well as on traditional Windows desktops.
In common with other products in the Microsoft Office suite, the other programming language used in Access is Microsoft VBA Visual Basic for Applications. VBA is similar to Visual Basic 6.0 (VB6) and code can be stored in modules, classes, and code behind forms and reports. To create a richer, more efficient and maintainable finished product with good error handling, most professional Access applications are developed using the VBA programming language rather than macros, except where web deployment is a business requirement.
To manipulate data in tables and queries in VBA or macros, Microsoft provides two database access libraries of COM components:

1. Data Access Objects (DAO) (32-bit only), which is included in Access and Windows and evolved to ACE in Microsoft Access 2007 for the ACCDE database format
2. ActiveX Data Objects ActiveX Data Objects (ADO) (both 32-bit and 64-bit versions)

As well as DAO and ADO, developers can also use OLE DB and ODBC for developing native C/C++ programs for Access. For ADPs and the direct manipulation of SQL Server data, ADO is required. DAO is most appropriate for managing data in Access/Jet databases, and the only way to manipulate the complex field types in ACCDB tables.
In the database container or navigation pane in Access 2007 and later versions, the system automatically categorizes each object by type (e.g., table, query, macro). Many Access developers use the Leszynski naming convention, though this is not universal; it is a programming convention, not a DBMS-enforced rule. It is particularly helpful in VBA where references to object names may not indicate its data type (e.g. tbl for tables, qry for queries).
Developers deploy Microsoft Access most often for individual and workgroup projects (the Access 97 speed characterization was done for 32 users). Since Access 97, and with Access 2003 and 2007, Microsoft Access and hardware have evolved significantly. Databases under 1 GB in size (which can now fit entirely in RAM) and 50 simultaneous users are well within the capabilities of Microsoft Access. Of course, performance depends on the database design and tasks. Disk-intensive work such as complex searching and querying take the most time.
As data from a Microsoft Access database can be cached in RAM, processing speed may substantially improve when there is only a single user or if the data are not changing. In the past, the effect of packet latency on the record-locking system caused Access databases to run slowly on a Virtual Private Network (VPN) or a Wide Area Network (WAN) against a Jet database. As of 2010 broadband connections have mitigated this issue. Performance can also be enhanced if a continuous connection is maintained to the back-end database throughout the session rather than opening and closing it for each table access. If Access database performance over VPN or WAN suffers, then a client using Remote Desktop Protocol (such as Microsoft Terminal Services) can provide an effective solution. Access databases linked to SQL Server or to Access Data Projects work well[citation needed] over VPNs and WANs.
In July 2011, Microsoft acknowledged an intermittent query performance problem with all versions of Access and Windows 7 and Windows Server 2008 R2 due to the nature of resource management being vastly different in newer operating systems. This issue severely affects query performance on both Access 2003 and earlier with the Jet Database Engine code, as well as Access 2007 and later with the Access Database Engine (ACE). Microsoft has issued hotfixes KB2553029 for Access 2007 and KB2553116 for Access 2010, but will not fix the issue with Jet 4.0 as it is out of mainstream support.
In previous versions of Microsoft Access the ability to distribute applications required the purchase of the Developer Toolkit; in Access 2010 and Access 2013 the "Runtime Only" version is offered as a free download, making the distribution of royalty-free applications possible on Windows 7 and Windows 8.x.

Split Database Architecture

Microsoft Access applications can adopt a split-database architecture. The single database can be divided into a separate "back-end" file that contains the data tables (shared on a file server) and a "front-end" (containing the application's objects such as queries, forms, reports, macros, and modules). The "front-end" Access application is distributed to each user's desktop and linked to the shared database. Using this approach, each user has a copy of Microsoft Access (or the runtime version) installed on their machine along with their application database. This reduces network traffic since the application is not retrieved for each use. The "front-end" database can still contain local tables for storing a user's settings or temporary data. This split-database design also allows development of the application independent of the data. One disadvantage is that users may make various changes to their own local copy of the application and this makes it hard to manage version control. When a new version is ready, the front-end database is replaced without impacting the data database. Microsoft Access has two built-in utilities, Database Splitter and Linked Table Manager, to facilitate this architecture.
Linked tables in Access use absolute paths rather than relative paths, so the development environment either has to have the same path as the production environment or a "dynamic-linker" routine can be written in VBA.
For very large Access databases, this may have performance issues and a SQL backend should be considered in these circumstances. This is less of an issue if the entire database can fit in the PC's RAM since Access caches data and indexes.

Access to SQL Server Upsizing (Upgrading) (SQL as a backend)

To scale Access applications to enterprise or web solutions, one possible technique involves migrating to Microsoft SQL Server or equivalent server database. A client–server design significantly reduces maintenance and increases security, availability, stability, and transaction logging.
Access 2010 included a feature called the Upsizing Wizard that allowed users to upgrade their databases to Microsoft SQL Server, an ODBC client–server database. This feature was removed from Access 2013. An additional solution, the SQL Server Migration Assistant for Access (SSMA), continues to be available for free download from Microsoft.
A variety of upgrading options are available. After migrating the data and queries to SQL Server, the MDB/ACCDB Access database can be linked to the database. However, certain data types are problematic, most notably "Yes/No". In MS Access there are three states for the Yes/No (True/False) data type: empty, No/False (zero) and Yes/True (-1). The corresponding SQL Server data type is binary, with only two states, permissible values, zero and 1. Regardless, SQL Server is still the easiest migration, and most appropriate especially if the user does not have rights to create objects such as stored procedures on SQL Server. Retrieving data from linked tables is optimized to just the records needed, but this scenario may operate less efficiently than what would otherwise be optimal for SQL Server. For example, in instances where multi-table joins still require copying the whole table across the network.
In previous versions of Access, including Access 2010, databases can also be converted to Access Data Projects (ADP) which are tied directly to one SQL Server database. This feature was removed from Access 2013. ADP's support the ability to directly create and modify SQL Server objects such as tables, views, stored procedures, and SQL Server constraints. The views and stored procedures can significantly reduce the network traffic for multi-table joins. Fortunately, SQL Server supports temporary tables and links to other data sources beyond the single SQL Server database.
Finally, some Access databases are completely replaced by another technology such as ASP.NET or Java once the data is converted. However any migration may dictate major effort since the Access SQL language is a more powerful superset of standard SQL. Further, Access application procedures, whether VBA and macros, are written at a relatively higher level versus the currently available alternatives that are both robust and comprehensive. Note that the Access macro language, allowing an even higher level of abstraction than VBA, was significantly enhanced in Access 2010 and again in Access 2013.
In many cases, developers build direct web-to-data interfaces using ASP.NET, while keeping major business automation processes, administrative and reporting functions that don't need to be distributed to everyone in Access for information workers to maintain.
While all most Access data can migrate to SQL Server directly, some queries cannot migrate successfully. In some situations, you may need to translate VBA functions and user defined functions into T–SQL or .NET functions / procedures. Crosstab queries can be migrated to SQL Server using the PIVOT command.

Protection

Microsoft Access offers several ways to secure the application while allowing users to remain productive.
The most basic is a database password. Once entered, the user has full control of all the database objects. This is a relatively weak form of protection which can be easily cracked.
A higher level of protection is the use of workgroup security requiring a user name and password. Users and groups can be specified along with their rights at the object type or individual object level. This can be used to specify people with read-only or data entry rights but may be challenging to specify. A separate workgroup security file contains the settings which can be used to manage multiple databases. Workgroup security is not supported in the Access 2007 and Access 2010 ACCDB database format, although Access 2007 and Access 2010 still support it for MDB databases.
Databases can also be encrypted. The ACCDB format offers significantly advanced encryption from previous versions.
Additionally, if the database design needs to be secured to prevent changes, Access databases can be locked/protected (and the source code compiled) by converting the database to a .MDE file. All changes to the VBA project (modules, forms, or reports) need to be made to the original MDB and then reconverted to MDE. In Access 2007 and Access 2010, the ACCDB database is converted to an ACCDE file. Some tools are available for unlocking and "decompiling", although certain elements including original VBA comments and formatting are normally irretrievable.

Free protection is available to help secure your computer against malware:

• Build up your computer's defenses
• Don't be tricked into downloading malware

Build up your computer's defenses

Install antivirus and antispyware programs from a trusted source

• Never download anything in response to a warning from a program you didn't install or don't recognize that claims it will protect your PC or offers to remove viruses. It is highly likely to do the opposite.
• Get reputable anti-malware programs from a vendor you trust.
• Windows 8 includes antivirus protection called Windows Defender. It's turned on by default.
• If your computer is not running Windows 8, download Microsoft Security Essentials for free.
• Choose security software that is compatible with Windows 7.

Update software regularly

Cybercriminals are endlessly inventive in their efforts to exploit vulnerabilities in software, and many software companies work tirelessly to combat these threats. That is why you should:

• Regularly install updates for all your software—antivirus and antispyware programs, browsers (like Windows Internet Explorer), operating systems (like Windows), and word processing and other programs.
• Subscribe to automatic software updates whenever they are offered—for example, you can automatically update all Microsoft software. Windows 8 and Windows 7 turn on automatic updating during installation.
• Uninstall software that you don't use. You can remove it using Windows Control Panel.

Use strong passwords and keep them secret

• Strong passwords are at least 14 characters long and include a combination of letters, numbers, and symbols. Learn more about how to create them.
• Don't share passwords with anyone.
• Don't use the same password on all sites. If it is stolen, all the information it protects is at risk.
• Create different strong passwords for the router and the wireless key of your wireless connection at home. Find out how from the company that provides your router.
• Use our password checker.

A firewall puts a protective barrier between your computer and the Internet. Turning it off for even a minute increases the risk that your PC will be infected with malware.

Use flash drives cautiously

Minimize the chance that you'll infect your computer with malware:

• Don't put an unknown flash (or thumb) drive into your PC.
• Hold down the SHIFT key when you insert the drive into your computer. If you forget to do this, click Close in the upper-right corner to close any flash drive-related pop-up windows.
• Don't open any files on your drive that you have not expected to see.

Don't be tricked into downloading malware

• Be very cautious about opening an attachment or clicking a link in an email, instant message, or post on social networks (like Facebook)—even if you know the sender. Call to ask if a friend sent it; if not, delete it or close the IM window.
• Avoid clicking Agree, OK, or I accept in banner ads, in unexpected pop-up windows with warnings or offers to remove spyware or viruses, or on websites that may not seem legitimate.
• Instead, press CTRL + F4 on your keyboard to close the window.
• If the window doesn't close, press ALT + F4 on your keyboard to close the browser. If asked, close all tabs and don’t save any tabs for the next time you start the browser.
• Only download software from websites you trust. Be cautious of "free" offers of music, games, videos, and the like. They are notorious for including malware in the download.
• Take advantage of technology—such as Windows SmartScreen in Windows 8—designed to help protect you from phishing scams and new malware that your anti-malware software hasn't detected yet.

Follow these guidelines to help keep your computer Virus and Spyware Free

1. Never believe a website will give you anything free

   Free usually means more than likely if you download or try free software or games you will receive spyware or virus's that will be installed without your knowledge.

2. If a Web page or Popup reports that your computer is infected with a virus or spyware

   Most likely it is the spyware or virus trying to install with your permission when you click the window to try a free scan, after which it will report that it found numerous problems and if you buy the software it will remove them, this is a scam, close the window or popup by pressing both (Alt+F4) keys together and do not click anywhere on the window.

3. Never fill-out a online form that asks for your email address unless you know for a fact that it is a well know institution such as a Bank or Major store

   Never fill out a feedback form on a web site that you visit they will likely want your email address and it will go to a web surfing analyzing and tracking institution which will cause you to start receiving all kinds of spam and popup advertisements.

4. Set your browser to clear browser cache when closing

   This will help prevent unknown files from launching the next time you open the browser, plus helps get rid of internet clutter.

5. Always create a password for your user account

   It is a basic protection to implement, it may seem a nuisance to some people, but it is the world we have created, also I always advise and configure the majority of systems I build and maintain with 2 accounts, 1 for everyday use with limited computer access, unlike a account with administrative access there are limited things that can be modified or installed when logged in with this account (If a virus or spyware installs itself into your computer while logged in with administrative access it will have access to your entire computer including windows operating services, this makes it easier to infest your system) you can leave the password off on this account if you prefer, just make sure the other account has a password (this is especially important when teenagers or children are in the home). When you need to install software or printers etc. just login to the other account and when finished logout and log back into the basic account (I know this is extra work) but which is worse entering a password occasionally or having to pay someone to remove virus's or spyware?

6. If you must fill in a form over the Internet such as ordering merchandise or paying for something make sure and read each screen carefully

   It is very common for most Internet merchants to include a field such as Receive Promotions Y/N or Sign me up for Notifications for a variety of reasons and it will almost always be already checked for Yes! which means you will need to clear the default setting before proceeding to next screen (Just read the screen and you should never miss this).

The most widely spread security issue today is the safe use of INTERNET SEARCH ENGINES, by far the worst is GOOGLE, not because it is a bad product in itself but because of the vast amount of information It can return, in my opinion this area needs a lot of attention, most people today are not what I classify as Safe Search Engine users, let me just mention a brief summary of what this is all about, Spyware and Virus's come in so many variations that is mind boggling, and the latest is what is termed as search engine poisoning.

We also need to be on the lookout for what is called Rogue/Phoney virus programs that claim that you have either Virus or Spyware on your computer or Hard drives failing etc. It then says that for a small registration fee it will remove these non-existent threats or correct non-existent computer problems, basically this is a form of extortion. These always look like a Webpage not a real local computer program.

This is how this is done: Keywords are monitored all over the internet, the bad people watch closely for the most common phrases searched for, they then embed these words in a bogus website which will have nothing to do with what someone is searching for, but if a user inadverintently clicks on a link to the bogus site in the list of search results, they then attempt to poison your computer with a phoney application or toolbar or more...

Here is a sample of one:

This is a good Video worth watching of the entire process: Youtube Rogue-Spyware Video, It will help educate users of what not to do.

Windows 7 has an elevated User Account Control included, but it only works if the current user logged into the Windows computer has what is known as a reduced access account (either standard, limited or guest login), this helps prevent a unwanted application or addin/plugin from being installed without an administrator login password.

Follow these guidelines to help lower your risk of getting junk e-mail.

• Take advantage of the Junk E-mail Filter in Outlook 2003

  The Junk E-mail Filter uses state-of-the-art technology developed by Microsoft Research to evaluate whether a message, including advanced analysis of the words and structure of the message, to determine the probability that it is junk e-mail. By default, this filter is set to a low setting that is designed to catch the most obvious junk e-mail. Any message that is caught by the filter is moved to a special Junk E-mail folder, where you can retrieve or review it at a later time. In addition, you can add e-mail addresses to the Safe Senders List, Safe Recipients List, and Blocked Senders List to give you even more control over what will and won't be treated as spam.

• Increase your protection level as you need to

  To obtain the maximum protection possible from using the Junk E-mail Filter and other enhanced privacy features, set the protection level of the Junk E-mail Filter to High or to Safe Lists Only.

• Keep your Junk E-mail Filter updated

  Updates are available at Downloads on Office Online. Under Office Update, click Check for Updates.

• Block images in HTML messages that spammers use as Web beacons

  A Web beacon can be a graphic image, linked to an external Web server, that is placed in an HTML-formatted message and can be used to verify that your e-mail address is valid when the message is opened and images downloaded. By default, Outlook is set to block automatic picture downloads. To verify what your automatic download settings are, on the Tools menu, click Options. Click the Security tab, and then click Change Automatic Download Settings. Verify that the Don't download pictures or other content automatically in HTML e-mail check box is selected.

• Turn off automatic processing of meeting requests and read and delivery receipts

  Spammers sometimes resort to sending meeting requests and messages with delivery receipts requested. Responding to meeting requests and read and delivery receipts automatically makes you vulnerable to Web beacons.

• Limit where you post your e-mail address

  Be cautious about posting our e-mail address on public Web sites, and remove your e-mail address from your personal Web site. If you list or link to your e-mail address, you can expect to be spammed.

• Disguise (or "munge") your e-mail address when you post it to a newsgroup, chat room, bulletin board, or other public places

  For example, you can give your e-mail address as "s0me0ne@example.c0m" by using the number zero instead of the letter "o." This way, a person can interpret your address, but the automated programs that spammers use cannot.

• Use multiple e-mail addresses for different purposes

  You might set up one for personal use to correspond with friends, family, or colleagues, and use another for more public activities, such as requesting information, shopping, or for subscribing to newsletters, discussion lists, and newsgroups.

• Review the privacy policies of Web sites

  When you sign up for online banking, shopping, and newsletters, review the privacy policy closely before you reveal your e-mail address and other personal information. Look at the Web site for a link (usually at the bottom of the home page) or section called "Privacy Statement," "Privacy Policy," "Terms and Conditions," or "Terms of Use." If the Web site does not explain how it will use your personal information, think twice about using that service.

• Watch out for check boxes that are already selected

  When you buy things online, companies sometimes add a check box (already selected!) to indicate that it is fine to sell or give your e-mail address to other businesses (third parties). Clear the check box so that your e-mail address won't be shared.

• Don't reply to spam

  Don't reply even to unsubscribe unless you know and trust the sender. Answering spam just confirms that your e-mail address is live.

• If a company uses e-mail messages to ask for personal information, don't respond by sending a message

  Most legitimate companies will not ask for personal information in e-mail. Be suspicious if they do. It could be a spoofed e-mail message meant to look like a legitimate one. This tactic is known as "phishing" because, as the name implies, the spam is used as a means to "fish" for your credentials, such as your account number and passwords that are necessary to access and manipulate your financial accounts. If the spam is from a company that you do business with - for example, your credit card company - call the company, but don't use a phone number provided on the e-mail. Use a number that you find yourself, either through directory assistance, a bank statement, a bill, or other source. If it is a legitimate request, the telephone operator should be able to help you.

• Don't contribute to a charity based on a request in e-mail

  Unfortunately, some spammers prey on your good will. If you receive an appeal from a charity, treat it as spam. If it is a charity that you want to support, find their number elsewhere and call them to find out how you can make a contribution.

• Don't forward chain e-mail messages

  Besides causing more traffic over the line, forwarding a chain e-mail message might be furthering a hoax, and you lose control over who sees your e-mail address.

Well, its confirmed Google Fiber is coming to KC

Using computers today on the internet as most people know is a risky business, thats why a routine check on your virus and spyware protection is important. You can download a free Spyware scanner such as Malwarebytes Antimalware and use it on a regular basis (Maybe once a week or once a month) to keep your computer running a lot smoother.