Question: Can I convert my Excel data into an Access database?
Answer: Yes, it is very easy to import Excel data into Access, providing it is set up correctly. Row 1 of your Excel worksheet should contain unique column headings and should not contain special symbols and column headings should not have wrapped text. Your data should be entered below the headings with no blank rows or columns within the dataset. Any cells with formulas will be imported as values. Close your Excel file and open Access 2007:
- Open an existing database or create a new blank Access database.
- Click the External Data tab in the ribbon and in the Import category, click Excel.
- Click Browse to search for and select your Excel file.
- Specify how and where you want to store the data in the current database. If you’re just wanting to create a table from your excel spreadsheet, select the first option and click OK
- Select the Sheet (or named range) and you’ll see how the data will import. Click Next.
- Check if your top row contains column headings. Click Next
- Look at each column and check if you wish to change column headings, data type or not to import. Click Next.
- Select the primary key option (unique field which identifies each record). Click Next
- Name your table. Click Finish.
- You may save your import steps if you plan to import the same file more often.
Your Excel worksheet is now an Access Table. Anything changed in Access will NOT be updated in Excel worksheet. If updated in Excel, it will NOT be updated in Access. It is totally separate.
If you choose to Link your Excel file into Access, changes made in Excel will automatically be updated in Access. However, you will NOT be able to make changes in Access. It will be used for creating filters, queries, forms and reports.
To export Access into Excel, select table you wish to export and click the External Data tab and in the Export group select Excel. Give it a filename, check Export and I like to see the data after it exports, so click Open the Destination file after the export operation is complete. With just a few clicks, you will see your Access table in Excel. It is much easier to email someone an Excel spreadsheet rather than an Access database.
For more information about Access, come to the ITS Access classes. See the schedule at http://www.unmc.edu/its and click on the Classes/Training link.
Question: In an Access report if you have three fields stacked, how can you assure your fields will move up if the field is empty? My report is currently leaving space for all three lines. If one or more lines is blank, is there a function that moves up the lines so they don’t take space on the report?
Answer: Yes, you can set the properties “To Shrink”.
- In the report design view, right click on the field and select Properties
- Click the Format tab and can change the format “Can Shrink” properties to YES on each of the fields
Be sure to remove any labels on those lines that may cause the blank line to display.
Question: I recently upgraded to Access 2007 but the command buttons in my database form which was created in Access 2003 no longer work.
Answer: The most common reason for this is that Access has built-in macro security. Your “buttons” use code which is blocked for your protection. To enable those macros to work, you must “Enable” the content. You will probably see a prompt below the ribbon which says, “Security Warning Certain content in the database has been disabled.” Click the Options…. button.
You can Enable this content, which will require you to enable it each time you use the database.
In order to enable it each time without having to click the options, you can add the folder with your Access files to your trusted locations. Do this by clicking on the Open the Trust Center from that Security Alert screen. Click on the Trusted Locations link and click add new location and browse for the folder where your Access database is located. You can also check Subfolders. and click OK twice. This will allow you to open the file without having to enable macros for each database.
(You can also find the Trust Center settings by clicking on the Microsoft Office button and select Access Options and click Trust Center. You will find Trusted Locations from the Trust Center settings link).
Question: How can I email an Access table to someone without emailing the entire database?
Answer: The only way to email a table is to export it in another format, such as an Excel spreadsheet, word table or PDF document, or you can copy and paste the table into an email message.
In Access 2003, select the desired table and click Tools > Office Links > Analyze it with Excel (or Publish it with Microsoft Word). To save in PDF format in Access 2003, you must have Acrobat installed).
In Access 2007, select the desired table and click the External Data tab and select Excel, PDF , Word or text file. (In order to expert to PDF you must have downloaded the free “Save as PDF or XPS” file from office.microsoft.com/downloads).
Question: I created a query joining several tables together, but when I run it no data displays. What am I doing wrong?
Answer: By default, when you link tables together in Access, it will display information for which there is data in both tables based on the linked field. If you have information in one table but some of the tables have no data, nothing will be displayed. So in order to get a list of all data in one large table, you need to change your join lines.
To do that, right click on the link and select Join Properties. Select option 2 or 3 (depending which is the main table containing information and click ok. In addition, I recommend you build your query little by little, adding one or two tables and conditions at a time. That way it is easier to troubleshoot problems.