Importing Excel into Access (you think it would be easy) (1 Viewer)

[kevlray]

I had been using DoCmd.TransferSpreadsheet to import data into a table. But now the user wants to change one of the column headings in Excel to a name that does not match the field name of table. I found some code that looked like it would work (using ADODB), but we are on Office 2016, 64 bit and apparently Microsoft does not support using ADODB anymore. So is there another solution? Also running on Windows 10 if that matters.

 

[MajP]

Microsoft does not support using ADODB anymore.

That is not a true statement AFAIK. However, easy solution is to import into table A and then do an append query into Table B mapping the correct fields.

 

[vba_php]

apparently Microsoft does not support using ADODB anymore.

like Maj said, why do you think that? if the library is present in your operating system directory, you can use it. the resources inside of that don't just magically disappear on their own.

 

[kevlray]

I am just going by what I read on other posts on the internet. All I know I am attempting to use a ADODB connection and I am getting an error ("Could not find installable ISAM.") with following code snippet.

cnn2.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"File=" & filename & "; Extended Properties=""Excel 12.0 Xml; HDR=YES"""

 

[jdraw]

Can you post some sample data -- an example of the spreadsheet and the structure of the table?
Details of the issue might help clarify things also. Seems you should be able to map fields???

 

[isladogs]

The ability to edit Excel spreadsheets directly from Access was removed with Access 2002 following a copyright lawsuit.
It has never been restored.
Nothing to do with ADODB.

 

[Uncle Gizmo]

The ability to edit Excel spreadsheets directly from Access was removed with Access 2002 following a copyright lawsuit.

Funny that this should reoccur, Chris mentioned it just the other day!

Access-Excel. How to edit an excel file from Access using SQL

As I'm sure you are aware, if you create a linked table to an excel sheet/range you can sort it, filter it, join it to other tables. What you can't do is edit the data. In many cases this does not matter since the requirement is to import the data or use as a reference. However on occasion...

www.access-programmers.co.uk

 

[CJ_London]

The ability to edit Excel spreadsheets directly from Access was removed with Access 2002 following a copyright lawsuit.
It has never been restored.

you can still do it with DAO - see this link https://www.access-programmers.co.u...t-an-excel-file-from-access-using-sql.310141/

For the OP to manually correct the header row each time, they would need to set HDR=No. That will then display the header as a record

cnn2.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"File=" & filename & "; Extended Properties=""Excel 12.0 Xml; HDR=YES"""

I think this connection string is wrong - pretty sure it should be 'Data Source=' rather than 'File='

Edit - beaten by Tony

 

[isladogs]

The topic recurs regularly!

 

[The_Doc_Man]

"Could not find installable ISAM."

Just for clarification: If you have ever personally installed Access, you know that one step involves deciding which database types you want to be able to read for purposes of import or export. Choices like Paradox, DBase, Informix, etc. These provide the Indexed Sequential Access Method that is your "installable ISAM" and the message is really telling you (in effect) "You didn't install the right conversion option." As Colin points out, you might not be able to do so because the conversion is no longer supported. However, if that IS the correct reason, then DAO wouldn't work for that data set either.

EDIT: CJ says you can, through DAO. I defer that fine point to his expertise.

 

[Uncle Gizmo]

The ability to edit Excel spreadsheets directly from Access was removed with Access 2002 following a copyright lawsuit.

I reckon I spent the best part of a year berating Google on missing functionality from some of their products. For instance the ability to search Google Drive is abysmal, you cannot search for particular file types, only the set of files types that they allow you to. If you have your own proprietary file types you can't do anything, is ridiculous considering Google is the eminent search based business in the world! However it occurred to me reading the comments here that maybe be its not that they can't do it, it's there may be some legal impediment....

The other thing that is difficult to do is search back through your calendar appointments on Google Calendars bloody stupid if you ask me.

I loved Time and Chaos... That was brilliant. I think it's still about...

 

[kevlray]

Of course my Access was installed by our organization. I have no idea what options they may have chosen. Attached is a screen shot of the table structure (at least what matters) and a test Excel file. The first column and the last column will not be imported (long story on why). So the scc column in Excel will be mapped to the DMSCC column in the table. And the columns B-L in the Excel sheet (except for the SCC column) should go in directly.

 

[vba_php]

The ability to edit Excel spreadsheets directly from Access was removed with Access 2002 following a copyright lawsuit.
It has never been restored.
Nothing to do with ADODB.

I don't think I follow this idea at all Colin. Would you care to expand on this a little bit? Because using a d o d b allows you to edit Excel files so it doesn't really make sense what you said. What was the lawsuit about anyway? And who filed it?

 

[CJ_London]

Microsoft Excel - Wikipedia

en.wikipedia.org

 

[vba_php]

Microsoft Excel - Wikipedia

en.wikipedia.org

but if that is supposed to answer my question it didn't do a very good job

 

[CJ_London]

try this one then

Microsoft loses Excel patent case

$8.9m damages

www.theregister.co.uk

 

[vba_php]

try this one then

Microsoft loses Excel patent case

$8.9m damages

www.theregister.co.uk

sorry CJ, but that doesn't say anything about what colin was talking about. am I simply supposed to INFER it?

 

[isladogs]

I thought I was absolutely clear in post #6
Prior to Access 2002, just like linked Access or SQL tables, a linked Excel file could be DIRECTLY edited in Access. No need for ADODB or DAO.
As I stated earlier, that functionality was removed by MS in Access 2002 after it lost the court case mentioned in post #16

 

[vba_php]

a linked Excel file could be DIRECTLY edited in Access. No need for ADODB or DAO.

I guess I missed the part about the "linking" and editing the excel file that way and through that medium. sorry about that, col. now I get it.

 

[kevlray]

Okay, so back to the subject at hand. Any ideas on how to append the data to the table in question using the spreadsheet provided (with the notes I provide)?