Access 101: Using Excel as a Starting Point
Having already begun the database, albeit just barely, I wonder whether it’s possible to use what I have as a starting point. Like you, I’m an infrequent, unfamiliar, and timid user of Access, so I’ll need to experiment to find out what’s possible.
I open Access and it entices me with the Getting Started task pane. Under Open, I see More, and click it, hopefully. Yes! It displays the Open dialog box, shown in Figure 3. I navigate to where I saved the Excel attempt and then set Files To Type: to Microsoft Excel. This is a good sign!
I click the Excel file and choose Open. Presented with the Link Spreadsheet Wizard shown in Figure 4, something worries me, but I ignore it and plunge ahead. I need to tell Access which worksheet to use. In my case, it was just Sheet 1, which I choose and then click Next.
In the next wizard window, shown in Figure 5, I click to enable First Row Contains Column Headings. Examining the headings, they do indeed look like the ones I set up. Breathlessly, I click Next.
Worry kicks in again, and now I know why. I noticed earlier that the wizard is the Link Spreadsheet Wizard. And, now, it wants a Linked Table Name. Is it really what I want? I thought I was divorcing Excel, not continuing to be wedded to it. Yet I forge ahead, naming the linked table Movies. I enable the Display Help After The Wizard Is Finished option, as shown in Figure 6, and then click Finish.
Access now confirms my fears by telling me that it’s finished linking Movies to movies.xls (see Figure 7). Prepared to rewrite this article, I take a breath and click OK.
Help has arrived. But, it’s not good news. The first option is Should I Import Or Link To A Table? It nicely informs me that I won’t be able to update a linked Excel table from Access and that I really should have imported. I told Access to open the Excel file, not link it, and nowhere along the way did it give me the option to do otherwise or even hint that I should be doing otherwise. Grrr!!!