When you’ve collected info via a Word form, you don’t have to manually enter it into Access. Mary Ann Richardson steps through an example that demonstrates the wizard way to knock out the task more efficiently and accurately.

A Word form makes it easy to gather information from users or clients so that you can analyze it later. But getting the information into an Access table can be time-consuming, not to say error-prone. To avoid having to print out and reenter data into a table, you can use the Access Import Text Wizard to bring in the form data for you.

Before you can use the wizard, you must save the form’s data to a text file. In this example, we have asked employees to complete the form shown in Figure A.

Figure A

Follow these steps to save the data in each completed form to a text file that will be imported into Access:

You can now import the data in the text files into an Access table by following these steps:

1. Create a blank database.
2. Click File | Get External Data | Import. (In Access 2007, click the External Data tab and then click the Text File button in the Import Group.)
3. Click in the File Name box and enter the full path name of the text file your want to import (Figure B).

Figure B

1. Click Import and then click Next.
2. Click the Delimited Button (Figure C) and then click Next.

Figure C

1. In the following window (Figure D), click Next again.

Figure D

1. Click the Field One Column and enter Project ID in the Field Name box.
2. Click the Field Two Column and enter Project Name in the Field Name box.
3. Click the Field Three Column and enter Project Start Date in the Field Name box.
4. Click the Field Four column enter Project End Date in the Field Name Box (Figure E).

Figure E

1. Click Next.
2. Click Choose My Own Primary Key.
3. Select Project ID from the drop-down list and click Next.
4. Enter Training Projects in the Name box.
5. Click Finish and then Close.

To add the next form, close the Training Projects table and follow these steps:

1. Start the Import Wizard in Access.
2. Enter the full path name of the next text file.
3. Click the Append button and select Training Projects from the drop-down list.
4. Click Next twice and then click Finish (Figure F).

Figure F



Important Note: If you need to enter more than a handful of forms a day, you should consider using  ADO (ActiveX Data Object) to create a code connection to the table that will automate the data transfer process.

Your customers will appreciate it if you send them a shipping notification.  See how a  little command button magic allows you to automate the process in your Access forms.

For better customer service, you decide you’d like to send an e-mail message informing customers when their orders have been shipped. However, you don’t want to have your shipping personnel spend time composing those messages. Good news: They don’t have to! You’ve already created a form that your staff uses to enter the Ship Date after an item has been shipped. Just add a Command button, like the one in Figure A, to send a shipping confirmation to the customer’s e-mail address automatically.

Figure A

cialis dosage information align=”justify”>To create the button, follow these steps:

1. Press Alt + Q.

Figure B

Now, when the user enters the ship date and clicks the Send Shipping Confirmation button, Access will compose the e-mail shown in Figure C.

Figure C

The user can review the e-mail and click the Send button. Access will then send the e-mail to the address in the ShipEmail field for that order.

Your users don’t have to waste time bouncing between Access and a Web browser. See how to add a control that lets them browse without ever leaving their form.

Do your users often need to stop and search the Web for data? Make it easier for them by adding a Web browser control to their forms. For example, say your users need to input the latest stock prices of the companies where your clients have placed investments. Follow these steps:

Figure A

1. Right-click the text box control and select Properties.
2. Click in the After Update property box and then click the Build button (Figure B).

Figure B

1. Type the following code at the prompt:

1. Press Alt + Q.

Now when a user enters a Web address, the Web page displays in the Web browser control window, as shown in Figure C.

Figure C

cialis discount price in place” width=”500″ height=”170″ />

By Susan Sales Harkins and Gustav Brock

Nothing frustrates the full spectrum of Access users — from casual user to developer — quite like a corrupted database. With a little know-how, you might get lucky enough to repair the database or at least recover the data. Sometimes, a third-party product that specializes in recovering data from a corrupted database can help. But you could end up rebuilding and re-entering data. (Or rather, your replacement will rebuild and re-enter it.)

A more proactive approach to the situation is to avoid corruption in the first place. Here are some strategies for preventing your databases from becoming corrupted.

Note: This information is also available as a PDF download.

#1: Split your database

If more than one person will access the database, split it into two pieces: a backend that stores the data in tables and a front end that contains everything else (forms and reports). Access has a wizard that holds your hand through the process so there’s no excuse not to split a shared database. Name the two ends appropriately. Remember, the backend contains your “gold” — your data. With the data and interface objects in separate databases, you can easily replace the front end from a backup, if necessary. In addition, this setup makes enhancements easier to incorporate into your system.

There is some discussion about whether the front end should be stored on a network server (and shared) or on a local drive. This decision really has no impact on corruption, so the choice is yours. If possible, consider write-protecting a shared front end. If the worst happens and something destroys the front end, simply obtain a fresh copy of it to replace the broken one and reboot the local system.

#2: Store temporary tables in a backend

If a database creates, populates, and then deletes temporary tables, keep those tables in a separate backend database to prevent bloat. Name the additional backend accordingly, making its purpose obvious. This additional backend can be shared or local.

#3: Don’t use memo fields

Avoid using memo fields if possible. They often, indirectly, cause corruption. Even though the database in these cases often can be repaired, some content in the memo fields might be lost. If you need memo fields, keep them in separate tables and create a one-to-one relationship to the parent table(s). Even better, move memo tables to a separate backend database file and name the file accordingly, to indicate its purpose.

#4: Don’t store picture files

Usually, you shouldn’t store picture files in a database. If you must, treat them the same way you would a memo field (see #3). Access has no problem attaching tables from multiple backend databases.

#5: Create temporary tables to speed up queries

If you run complex or nested queries (where one query pulls data from others that hit still others), Access may write a lot of temporary data that you never see. Most often, this happens when a query that works with a small amount of data performs slowly, putting stress on the JET engine. If Access chokes during this process, you can end up with a corrupt backend file.

To prevent this problem, write some of the temporary data to temporary tables. There’s no universal propecia new york method to recommend. Analyze the specifics and run some tests to find the best solution. However, sometimes the use of just one temporary table can minimize the chance of corruption and speed up the queries by a factor of 10 or more.

#6: Be careful with wireless networks (WiFi)

A connection may work fine, but multiple users or powerful neighborhood networks (or other noise sources) can abruptly cut off the connection. That can corrupt the database file if you are writing to it at the time. This type of interference isn’t a problem if users are mostly reading from the database.

#7: Be careful with WAN connections

A WAN connection that covers any connection from a local system to a server via the Internet can cause trouble. Reading the database may be slow but acceptable. However, writing to the database is error prone and can cause corruption. When bottlenecked traffic interrupts data transfer, Access times out, believing the connection has been lost. This behavior usually leaves the backend database in a corrupted state.

#8: Don’t put Mac and Windows users on the same network

If Mac and Windows users share the same network and experience problems, establish a separate network for database users. Macs are extremely noisy, and typical Mac applications generate vast amounts of network traffic when moving large graphics files and printing. In a shared environment, use only high-quality network components.

It’s best to keep the database traffic separate from the graphics traffic, as much as possible. You can accomplish this by allowing administrative workstations to connect directly to the server with the shared database via a local switch.

#9: Troubleshoot network hardware

If corruption just happens from time to time, you may have to deal with a network hardware issue. First, try to narrow down the workstation and swap the error-prone station with another. If the issue follows the workstation, you know that’s the source. It might be easiest to get rid of the workstation.

If the problem isn’t specific to the workstation, the error is most likely to be found in the connection from the workplace to the network switch — including the actual switch port. If the cable’s in good shape, swap the ports between the workstations. If the error source now swaps too, replace the switch; if not replace the cable. If cables aren’t permanently installed or don’t cross from floor to floor, you can try replacing them before swapping workstations.

#10: Check the server’s configuration

Sometimes, the configuration for the server where you’ve stored the shared backend database file is the culprit. You’ll need a specialist to track down and resolve this type of problem. There are several possibilities, from server parameters to a malfunctioning disc controller to a misconfigured RAID array.

You can temporarily move the backend file to a different location, like one of the popular NAS devices or a shared folder on a workstation. If the problem with corruption goes away, call the specialist. If not, the corruption’s source is somewhere else (#1 through #9).

Microsoft Access may not be the king of databases, but it does offer a number of advantages over solutions like SQL Server and Oracle in certain situations. Access guru Susan Harkins counts the ways.

Nothing starts a fire quicker than saying, “Hey, let’s use Access. Yeah, Access can do it!” Oh my… just thinking about it makes me want to don a fire retardant pocket protector. Seriously, though: Access elicits real passion. Developers and IT folk either love it or hate it. There’s no reason why you should use a tool you hate, but you should strive to use the tool that’s best for the job.

The truly smart and effective IT professional knows that there are many tools. The key is to know which database engine is the best for the job at hand. Why throw massive resources at a simple need? In other words, you don’t want to pay for a Rolls Royce engine if you’re building a go-cart. Knowing when and when not to use Access will help your budget and make you look good, whether you’re a freelance developer or you’re managing IT resources.

Note: This information is also available as a PDF download.

#1: It’s cheap

Access is a desktop database and its place in the hierarchy — layered between Excel and SQL Server — determines its price. Access costs the same as any other desktop application. Acquiring a copy of Access won’t require a loan or a call to investors.

The large client-server databases, such as SQL Server and Oracle, require specific hardware and expensive licensing. After the project’s up and running, a client-server database needs a dedicated administrator. Access requires none of that.

On the other hand, Access is a desktop application. That means that everyone who uses a customized database application needs Access installed on their local system. All those copies of Access can be expensive. One alternative is to invest the time and money it takes to turn your database into a runtime application. (Access 2007 doesn’t support this option.)

#2: It’s easy

Anybody with just a bit of time and reasonable intelligence can learn to use Access. It doesn’t take weeks of classroom instruction and then months of mentored on-the-job training to acquire the skills necessary to create and administer a database. It’s safe to say that most Access databases have one user and they live out their lives on one system. The user generally creates the database in his or her spare time. The casual user with no professional database or development skills can get data into an Access database and then manipulate that data without blowing up the building.

A good database grows and a bad one dies — regardless of the data engine that’s driving it or the skill set of the person who created it.

#3: Development costs less

Many developers make a good living creating custom database applications in Access. (Call me, let’s talk.) However, in general, they charge less than SQL Server and Oracle developers. Moreover, the development costs are just the beginning if you go with SQL Server or some other client-server software (see #1). If you really want to use Access and you’re smart, you’ll see that an enthusiastic and eager employee gets the right training. Then, pass out all that money you save in employee bonuses.

On the other hand, it doesn’t matter how much money you save initially, if you use the wrong database. Don’t let money be your only consideration or you’ll surely regret it. For instance, the security model is minimal (and doesn’t exist at all in Access 2007). Recovery isn’t as easy, either. Don’t use Access for mission-critical applications unless you really know what you’re doing — and even then, it might be a good idea to keep your resume updated.

#4: Prototyping is a snap

Access is a great way to show fast results for the impatient client or boss. You can collect a little data and in just a few hours (or days) wow them with a few neat forms and reports — I can hear them ooing and ahhing already. You don’t have to use Access to build the production database, but you can ease client concerns by showing that you understand their needs. Access lets you get results fast and often with little to no code.

#5: It’s easy to upsize once it outgrows Jet

People who control the purse strings aren’t usually willing to dedicate resources to developing a noncritical database. Most of the time, you’re on your own. However, that doesn’t mean that a good design won’t grow and evolve into a truly useful tool. If that happens, you can upsize an Access database to SQL Server. You don’t have to start from scratch.

Still, Access is limited to 2GB. Even if the database’s purpose isn’t critical, the amount of data alone might force you into the arms of a more robust engine. Realistically, you probably won’t run into that limitation too often. If you do, you can eliminate Jet from the picture and use an Access front end to link to SQL Server data.

#6: It’s a one-time fling

Not every custom database has a long shelf life, but that’s not because it’s bad and dies an agonizing death. Sometimes its purpose is timed. For instance, generating, collecting, and analyzing questionnaire data can be a big job, even for Access, but a single questionnaire has a limited lifespan. If you’re going to use a database once, or for only a short time, use Access if possible.

#7: It can provide a quick fix

The best solution for your needs might be a powerful client-server database such as SQL Server. However, while you’re waiting — and you will wait — how’s the work being done? You can use Access as a quick fix until the more robust version is ready. You’ll have to compromise, because if you really need SQL Server, you’re not going to get the same work done in Access. But you might get portions of the work done. Analyze the overall tasks and see what components you can automate in Access, at least for the time being.

#8: You want to change what?

Access is flexible, and that’s one of its best attributes. Even if you can put a custom database together in a matter of weeks, needs are likely to change. Almost immediately, the user or client will think of something they want to add or change. If you designed the database well in the first place, Access will handle enhancements and changes without complaint.

#9: It talks to Office

Access is part of the Microsoft Office suite, so it plays well with the other applications. Users can quickly and easily export data from or import data into Excel or publish reports to Word. In addition, it shares a similar interface with other Office apps, which helps new users feel more at home and diminishes the learning curve.

#10: There’s less code!

All things being equal, Access can get the job done with less code than SQL Server (or some other client-server database). In addition, VBA is an easy language to master.

#11: It offers connectability

Access offers an affordable solution for individual users and smaller teams. Despite protests from some member of the IT club, you can even use it across a network if you know what you’re doing (file server solutions on a local network).

On the other hand, Access isn’t optimized for the Web. Although a skillful developer can use Access on the Web, in general, it just isn’t a good idea. Jet can’t handle large numbers of simultaneous users, unless of course you really know what you’re doing — and that level of expertise is really closer to magic than development. It can be done, just not by many.

propecia missed dose