Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old July 13th, 2012, 12:47 PM
Authorized User
Points: 308, Level: 6
Points: 308, Level: 6 Points: 308, Level: 6 Points: 308, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default Appending data to a table from Excel

I need to append data from a spreadsheet (many spreadsheets over time) into an access table. There are several aspects of this I am not sure how to do.

First is that the spreadsheets have many tabs and within each tab are “tables” that contain the data. Thus, I believe that I need to designate named ranges for where to get data. And I believe the idea of linking to Access is out of the question considering the issue of multiple “tables’ within tabs in the spreadsheet. (And I really don’t want to think about linking and then unlinking tables via code. Maybe I just stated two problems, the idea of named ranges and the seeming unavailability of linking the tables to access.

Second, some of the “tables” in Excel have the field names vertically in a row with the data to the right. I have never tried to append data that was not in the traditional layout of field names spread horizontally in a row with the data below it. Is there a way to deal with that in the append query?
Reply With Quote
  #2 (permalink)  
Old July 13th, 2012, 08:03 PM
Authorized User
Points: 346, Level: 6
Points: 346, Level: 6 Points: 346, Level: 6 Points: 346, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 61
Thanks: 0
Thanked 13 Times in 13 Posts
Default

Hi,

It is possible to link to named ranges in Excel from Access. However, if you don't want to link Excel data to Access your only alternatives is to use VBA in Access to create an Excel.Application object and manipulate the workbooks via the Excel Object Model, that is pull the data from Excel. You could use VBA in Excel to push the data to your Access tables via a button the user presses within the spreadsheet. However, I believe you are wanting to batch process these spreadsheets so the former is required.
For the data with the field names in columns, that named range could be copied and transposed into the correct format for linking. This would require using VBA on the Excel object too.
I know you said in your other post that you are familiar with VBA but certain aspects were beyond your skill level, so maybe the options open to you with not linking and having the data in the correct layout could be issues for you! You could easily duplicate the data in the normal table layout using formulae anyway!
An append query is an Access object that uses SQL to insert records into a database table, so you must have the source data in a format that Access can read, that is either a linked or local table. So, you must get the data in the correct layout to link to Access to then build your Append Query or you use VBA to manipulate the Excel object, extract your data and insert your records into the destination table. Either way is feasible.
If you have sample data and it's not sensitive then I'd be happy to help you build the solution to your problems.

HTH.

Malcolm.
Reply With Quote
  #3 (permalink)  
Old August 13th, 2012, 01:29 PM
Authorized User
Points: 308, Level: 6
Points: 308, Level: 6 Points: 308, Level: 6 Points: 308, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2009
Posts: 50
Thanks: 0
Thanked 0 Times in 0 Posts
Default Your suggestions re importing data from Excel to Access

I have implemented some of your suggestions and am the next stage. Specifically what I have done thus far is to create a second of tabs (all of whose names begin with the word Access for easy recognition). these tabs look much like the input tabs filled out by the field people, but have formulas to draw over the data from the input tabs.

This allowed me to make sure all the data is in the top left of the worksheets with the field names running across row 1 and the data below it.

Once the data is either a linked or imported table, disbursing it out to the permanent Access tables is pretty easy with append queries.

Now I am at the point of whether to import or link the worksheets into Access.

I am going to post a question(s) about my options in this area but since you were already so kind once to give good advice, I am also copying it below.

We have people in the field collecting data about buildings into multi-tab spreadsheets. (between 10 and 20 tabs total). We then want to copy this data into Access. I might try to do this a bit differently if given a choice, but I have no option but to have data collected in Excel and for Access to the the ultimate repository for it all.

Thus far I have put the worksheets into a layout that Access can recognize and created the tables in Access that will match the worksheets in Excel. The data is reasonably well normalized among the Excel Worksheets. (This is a too long story that i will wpare you reading).

My plan is to use append queries to copy the data from the worksheets into the Access tables. To do this, I believe I need to either import the worksheets into Access as tables or to link the worksheets.

With the option comes the quandary. There seems to be goods and bads of both options and I would love to hear from people who have done both or either of these methods. I have used both in the past for more limited scope tasks.

At first I liked linking. Once I have all of the Excel tabs linked, I can run the append queries. Then replace that spreadsheet with the next one, update the links and run the queries again. But then come the details.
1. I don't want people to have to manually update over a dozen links with every new spreadsheet coming in from the field.
2. Using the Import Data process in Access 2007, when setting up links, does not allow you to save the process to re-run it if you want to. Thus apparently requiring updating existing links. (That is, if I run the first spreadsheet with links, run my append queries, and then want to go to the next workbook, I can rename the first workbook (to break the links), then give my second workbook the same file name as when I set up the links and update the links so they now show the data from the second workbook. Then run the append queries again to get this second set of data into Access. And continue with however many workbooks we receive. The total number of these will be in the dozens.
3. So my problems with linking seem to involve the repeated renaming of spreadsheets and updating of many links to update the data in the linked tables in Access.

So I looked at importing worksheets as tables. Importing, not linking. Again, details.
1. In this case I can set a name for each import and presumably set up a macro that would run each of the imports, one after another. Is this true? I have not used macros in Access. Will they run these imports?
2. It would seem that after I run the append queries, I can set up a macro, or series of macros that will then delete out these imported tables, setting the stage to import them again from the next workbook to be processed.
3. So my problems here are whether I can use a macro to automate these processes (importing and later deleting the tables that are imported from Excel) and, again, the repeated naming and renaming of workbooks before each new running of the process.

And I just bet that there are issues that will arise that I have not thought about. I would surely appreciate all helpful comments about the two processes envisioned above and any other issues I may be missing.

Thank you in advance.
Robert
Reply With Quote
  #4 (permalink)  
Old August 13th, 2012, 05:34 PM
Authorized User
Points: 346, Level: 6
Points: 346, Level: 6 Points: 346, Level: 6 Points: 346, Level: 6
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2010
Posts: 61
Thanks: 0
Thanked 13 Times in 13 Posts
Default

Hi Robert,

Personally, I would use linking. You can write VBA code to do the re-linking. It could iterate through Excel files in a folder, refresh links, run the append queries, etc. Although I've used Access since version 2.0 I've only ever done a macro once and that was due to this Access 2010 book!

I've never done anything like that before but I'm sure it would be fairly easy to do. I'll look at doing some code tomorrow for re-linking Excel workbooks contained in a folder and post it here if that will help.

Do your field staff have Access as a resource? If so, build the data collection system using Access! You can then create append queries in this database to add the data to your main system when they are on site. Although using SQL Server Express or LocalDB might be better, so that you can set up replication to SQL Server, that would be a more robust and scalable system and just use Access as the front end.

I hope someone can give you some advice based on their experience, let me know how you get on.

Malc.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Appending Records from One table to another Tallon Wolle Pro VB 6 4 September 9th, 2008 11:29 AM
Appending a Table to a Catalog using ADOX ShoeBucket Access VBA 3 June 15th, 2007 01:58 PM
appending Excel data to VB msflexgrid zach2004 VB How-To 0 July 26th, 2004 09:11 PM
appending a field to a table Dmouse Access ASP 1 February 29th, 2004 05:18 AM
Appending Records from One table to another Tallon Wolle Pro VB 6 1 November 20th, 2003 03:26 PM



All times are GMT -4. The time now is 05:48 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.