Solved

Excel Import into Access 2007

Posted on 2012-04-05
6
305 Views
Last Modified: 2012-04-15
I need to export a spreadsheet sheet  into a new Access Table. No problem doing so, when I manually run the import, the rows come into the table in the same order as the spreadsheet, which is exactly what I want. When I run this in a Macro using RunSavedImportExport, the import works fine, but the order of the data is different than in the original spreadsheet. This is a problem for me as I use the contents of the data in the first 3 rows to run a series of modules.

How do I keep the same order in the table as I had in the spreadsheet? I am not using any Keys or auto numbering?
0
Comment
Question by:rrudolph
  • 2
  • 2
  • 2
6 Comments
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 250 total points
ID: 37813790
This is why I try to create a "Numbering" (SortBy) column in Excel too.

This way even if the data comes in with the wrong order, you can resort the fields (via Indexing or setting the SortBy column as the Primary key) in the Access table correctly.

Perhaps another Expert has something more elegant...
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 37813903
use recordsets, read the excel file, row by row and insert to table as you go.
0
 

Assisted Solution

by:rrudolph
rrudolph earned 0 total points
ID: 37813932
Can you point me to a code snippet that would show this in action:

Spreadsheet Name is:  MasterPrice_Template.xls
Sheet Name is: Export
Access Table is PriceImport

Keep in mind, I never know how many columns or rows the spreadsheet will have

Thank you
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 250 total points
ID: 37813936
upload a copy of the db and excel file
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 250 total points
ID: 37823661
A table, technically, does not have an "Order"
If you need the records in a certain order, then why not simply create query to sort them in that order?

In other words, I am not quite sure why the table needs to import in a certain order...
If the table does not import in your specific order, then make a query to do so...

A query can be used just like a table in most cases...
0
 

Author Closing Comment

by:rrudolph
ID: 37848035
I ended up adding a key field programatically and sorting the record set the way I wanted to read the records. Nobody furnished a silver bullet, but all help was appreciated and did confirm what I already suspected.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

749 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