Solved

Link to Excel data, make a report

Posted on 2014-01-30
4
88 Views
Last Modified: 2015-04-03
Experts,

I have a flat file from the companies db.
I want to make a report off of it.

Is it better to link to the excel file or import it?  

I seem to remember there could be naming issues (error msg boxes) if imported and access wants to change the names of the columns.  Not sure if you get this error if linking.  

any other tips I would appreciate as I have never done this.

thank you
0
Comment
Question by:pdvsa
  • 2
4 Comments
 
LVL 1

Accepted Solution

by:
SarahDaisy8 earned 250 total points
ID: 39822190
Hi Pdvsa,

I've come across a similar question with my daily data imports.  I've always preferred to import the data into the database because often times I need to edit the data or perhaps it's slower to link it.  Are you going to be routinely using the same file or getting a new file?  

If you are getting a new file daily, weekly, etc. you can automate the process and even change the names of the fields using VBA prior to importing the data.  I've done this on several occasions.  This allows me to control the names of my fields because a lot of times they are named something funny and I can't use it.  

Let me know if the automation of importing the data would be helpful to you and I can post some of my VBA code.  

Hopefully I've understood what you are looking for.  

Sarah
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39823995
If all you need to do is report on what is in the spreadsheet, and it doesn't have excess header rows (1 row only), then linking to the spreadsheet using the

DoCmd.Transferspreadsheet

method is the way I generally do it.  Look that up in the Access Help.

As Sarah mentions, you can automate this process to allow you to select the specific spreadsheet and give it a consistent local "linked table" name so that your queries and reports won't have to change when the source file does.
0
 

Author Comment

by:pdvsa
ID: 39835471
Sorry for my late reply.  On vacation at the moment.  

If I need to edit the data, can I do this with the link or is it better to import?  I probably won't have to edit the data but there could be a case where I might.  I think if I click the linked table it might display as a table inside of access and I can edit if not mistaken.

Thank you
0
 

Author Comment

by:pdvsa
ID: 39857153
Ok I have this about done now.  

Just one more question:
Should the excel data have an [ID]?   currently the raw excel data has no [ID].  I think I will need one if I want to make a clickable hyperlink to the record to modify the record instead of manually finding it in the thousands of rows.  Not sure how to best approach this since the raw data would have to be modified and I would prefer for it not be be modified if not easily done and done automatically.  Another dept will use this and they dont know anything about Access.  

thank you for your advice....
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Can i Import Access Table Into Oracle Using Toad 36 167
vba sql wild card passing in code 3 22
Can not open the Access Help ? 3 53
Reference Controls on subforms 7 27
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question