www.databasejournal.com Tutorials

Import Data from Microsoft Excel
Maybe you have noticed that Excel spreadsheets do not always import data very nicely into Access. Sure, Access can import data from various sources with just a few clicks, but Excel files seem to give the most grief. Out of necessity, I created a code module that employs automation to loop through rows of an Excel spreadsheet and systematically add data to an Access table, cell by cell. While this is not exactly a "cut and paste" solution for your data import, it does provide a nice starting point.

How to Pass Access Data Across the Web
Learn how can you pass a little bit of data from an internal Access application behind a firewall out to a public web site in real time?

Concatenate Column Values from Multiple Rows into a Single Column with Access
Danny Lesandrini tackles concatenating column values from multiple rows into a single column.

Access TreeView-ListView Basics
Ok, this truly is a 'basics' article. I've never been fond of the TreeView & ListView because the library file that exposes these controls, MSComCtl.ocx, always seemed to behave a little quirky. It was as if I could never be sure which version of the controls any given user would have on their computer and while the controls worked fine for me, users constantly experienced issues. This article focuses on getting started with Access TreeView and ListView controls.

Fascinating Query Tricks
I love writing VBA code but I am convinced that, many times what I am trying to accomplish can be done in a more efficient, graceful and elegant way using SQL queries. Over the last few months, I have tried to develop my applications with this in mind. The following article is a summary of problems I have been able to solve by using fascinating query tricks. Here is an overview of what we will cover: * How to pass a variable parameter to a query calculation * How to add "<All Employees>" to a list of employee names * Use UNION query to add a Totals row to a dataset output

Using Microsoft Access to query and update Web sites
Learn how you can use an Access Database and VBA to manipulate a Web 2 application.

Create XML Web Reports From Access XP
Transforming XML data with XSL is a great way to deliver formatted content to the web. Although creating XSL stylesheets can be daunting for the initiated, the new ExportXML method in Access XP makes it easy to publish even the most complicated reports. The following article will show how you can "push" or "pull" your Access reports to the Internet.

Searching For Text Anywhere In a Database
Thanks to internet search engines, users are used to searching for text but unfortunately even the desktop search tools from Microsoft and Google do not have a search that looks into tables (easily). This article outlines a simple tool to search tables or linked tables to find if a text string(s) exists in those tables. It does this by building SQL queries after analyzing the system tables in any Access/Jet database. This code is suited to Access 2000, XP, 2003 and 2007.

How to Execute SQL Stored Procedures from Microsoft Access
The simplicity and popularity of SQL Server means that more and more developers who build applications with Microsoft Access will want to learn how to take advantage of server side processing using SQL Server Stored Procedures. The following article will demonstrate a simple method for executing procs from Access. In addition, we'll cover questions like these: * How to login to SQL Server from Access and refresh object connect properties. * Check for existence of stored procs and add new ones where necessary. * Build dynamic SQL Pass Through query with parameter values. * Display the results of the query output in a listbox.

Implementing the Equivalent of a FULL OUTER JOIN in Microsoft Access
There are many different kinds of joins, like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN. The first three of these different join types are implemented in Access, but FULL OUTER JOIN is not. If you need to perform a FULL OUTER JOIN in Access, are you "up a creek, without a paddle"? No, you are not. This article will show you how you can write some SQL code in Access to implement a simulated FULL OUTER JOIN.

Simple FTP Methods from Microsoft Access
Extend the usefulness of Microsoft Access with this tool from Danny Lesandrini.

Access Paired Forms
Nearly every Access developer will admit to having learned much about user interface design from the Northwind sample database but in time most of us look for ways to improve upon, or deviate from, the template we know so well. My latest idea makes its début in this month's article: Paired Forms.

Merge Records from Two Identical Databases
Several months ago I was asked to create a utility to simplify the process of combining data from identical MS Access databases into one file. Not long after completing that project, someone posted a request to the newsgroup, comp.databases.ms-access, asking for suggestions for doing the same with his data. I replied to his post with an outline of the steps taken to perform my data merge.

Redesigning a FoxPro database / program using Access and Visual Basic
Designing a program from scratch in many instances is an easier task than modifying an existing solution. A recent project started as a request to take data from a program and display it on the web. As a future consideration the data displayed on the web page would eventually be updated by customers via a web browser. This article will explore the design of the database while discussing the use of some tools to facilitate the process.

Access 97/2000 and SQL Server 7 / 2000 Compared
Often people in newsgroups ask about some comparison of Access and SQL Server databases. In this article, I want to tell you about some general characteristics of Access 97/2000 and SQL Server 7.0/2000, about their restrictions, and about the comparison of these products.

Focus on Popular Objects to Speed up an Access 2007 Database
Garry Robinson outlines how usage data can make the quest to speed up an Access database more focused. The article also delves into an Access 2007 ACCDB only feature called TempVars.

Navigate Access Forms with Faux Hyperlinks
I started this article because I felt like I'd stumbled on a clever way to simulate web browser type hyperlinks on my Access Windows forms and I wanted to share that with readers of Database Journal. However, as is always the case, I began to question my own practice, looking for ways to make it more efficient and extensible. I found one, but in the end, I decided that I liked my original solution better. I'll be curious to see what you think, especially if you have an alternate solution. Learn a way to simulate web browser type hyperlinks on Access Windows forms.

Secure Microsoft Access Passwords and Encryption in Access 2007
Hello Microsoft Access fans and skeptics. In this article, I am going to describe how you can improve on what already is a very significant security improvement in Access 2007, database encryption. Microsoft Access 2007 introduced a new file format for storing Access database information. This file format can be identified by any file that ends with .ACCDB. Whilst Microsoft Access 2007 still supports the file type of .MDB, the future of Access is going to be built around the .ACCDB format. One of the most significant changes with the .ACCDB format is a new method of encryption that is based around the database password. This change is a very significant security improvement because encrypted ACCDB databases are (in theory) strong enough that they can only be cracked using very computer intensive brute-force password recovery. Of course, as with all security, if lots of people do things the same way the risk is far higher that someone will work out how to breach the security. This definitely will apply to encrypting an Access 2007 database because the default encryption algorithm used is RC4 with a 40-bit key, one that is not as strong as it could be. In this article I am going to show you how to make your password encrypted database more secure than the standard ACCDB encryption.

A Comparison of SQL Server 2000 with Access 2000
Compare SQL Server 2000 with Microsoft Access regarding price, platforms supported, features, and product limits.

Manual Table Relinking
Last month's article, Automatic Table relinking, demonstrated how to implement table linking behind the scenes, with no user interaction required. However, there are times when we have to trust the users with the awesome responsibility of pointing to the correct database. Perhaps it is only at the initial setup of the client application, or perhaps your program allows users to connect to different databases. Whatever the reason, at times you need to allow for manual table relinking.