Import MS Access Database To Excel 2010 Using MS Query
Thank you for taking the time to report an issue.
What's wrong... Please write below.
First of all, I should be able to import it into the current Access table(s), correct? Since Access data is normalized into different tables and data in Excel are not, would I import appropriate columns into each individual Access table? Would I have to make the Excel spreadsheet have the same columns and be in the same order as the table(s) in Access? Or can I just import it as is on Excel and Access can figure it out? Also, the Excel spreadsheet does not contain the information that was captured on the later version of Access.
Secondly, are there extra steps/precautions to take when importing data from an Excel spreadsheet into an Access database designed with autonumbers? The Excel spreadsheet does not contain an autonumber. However, an autonumber will be required when this information is imported into Access. Do I have Access select the primary key in the table so that it can recognize this imported data or would I have to run a query afterwards to assign an autonumber?
The spreadsheet has 59,000 rows. How can I quickly resolve this problem? I have tried changing the Excel file to a csv file and it comes back with even more errors.
This all works fine, however when I close down Excel and then try and try and run the query in Access, I get the following message "The Microsoft Office Access database engine cannot open or write to the file "name of query here".
It is already opened and exclusively by another user, or you need permission to view its data.
I've never had the message before and has only presented itself after making the connection between Excel and Access.
I do not have any other Excel or Access files opened.
I am trying to convert an old DOS based DB into Access and I can't figure out how to import data into a multi-select list box. Every time I try something it just add another field of choice.
that is if I want to import A , B and C into the field, it allows me to do this, but creates a new entry int he list as A, B, C. I want it to check the boxes for A, B and C. What Ineed to know is how to set up the data in the Excel spreadsheet and how to import into the table field that is the multi-select list box.
The functionality will also need to use a saved import routine I created earlier.
I'm using Access 2010, but this will need to be compatible with 2007 as well
At first we thought it was the extra three columns that were in the most recent monthly file that were not in the file that she tested. She had set up a template to import the data into and so we added the three columns that weren't there before.
Still got the error. Next I thought I should save it in Excel 2010 since the original file was in Excel 2003.
But upon saving the file in Excel 2010, there was another strange error message having to do with a saved name range. So, we deleted that but I keep getting the same message "subscript out of range".
I need to import these 2 columns into an Access table (2010).
I'm working on a method, but it feels like maybe I'm overthinking it. I've created a macro in Excel to copy column A & H to a new Excel workbook. Then I'm doing an import into Excel. Is there a way to do this directly through Access (without the Excel Macro)?