- For Example
- The Problem with Word Tables and Excel Spreadsheets
- Access 101: Using Excel as a Starting Point
- Convert Linked Table to Local Table
- Ta Da and Next Time!
- Tip Sheet
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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!!!