UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Connect Excel 2003 To Access 2019 Tables, Office 2003    
 
   
jpiper1921
post Feb 11 2019, 08:11 AM
Post#1



Posts: 36
Joined: 11-December 18



My work has a mix of old and new software. I am trying to connect/link Excel 2003 (Office 2003) to Access 2019 tables. In Excel I don't understand how to make the connection. I am thinking ODBC but am unsure of the settings to use. Anyone know how to make this work?

Thank you,

John P
Go to the top of the page
 
dflak
post Feb 11 2019, 01:02 PM
Post#2


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


My first thought is why not open the 2003 Excel files and save them as something from this decade?

ODBC looks like it might work I have a 32-bit Win 7 Excel 2013 system and a 64-bit Win 7 Excel 2016 system. Both of them have Excel drivers for XLS files.

If you are asking how to make an ODBC connection then let me know whether you have a 32 or 64 bit system. The process is slightly different. I don't think the windows version matters. I know that I have both documented somewhere but will have to find them.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jpiper1921
post Feb 11 2019, 02:01 PM
Post#3



Posts: 36
Joined: 11-December 18



Sorry - all we have is Excel 2003... I wish I had 2016 or later. I'm using Win 10 64 bit; is that your question? I've toyed with the ODBC options and only see data source type for SQL server.
Go to the top of the page
 
dflak
post Feb 11 2019, 02:20 PM
Post#4


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


Here are some instructions on making an ODBC connection on a 64-bit machine. This example explains who to do it for an Oracle driver, but as you an see, there should be an option for Excel Drivers. The dialog box may lead you down a path where you can enter the directory that contains the Excel files. You will probably also get a option to open a specific Excel file. You probably don't want to do that. You will probably want to select any excel file in the directory.

Note that the utility that you can launch from the control panel appears to work on a 64-bit system but it doesn't. You have to use the executable in SYSWOW64 as indicated in the word document.


Attached File(s)
Attached File  New_ODBC_64_Bit.zip ( 159.49K )Number of downloads: 2
Attached File  Excel_Driver.jpg ( 28.27K )Number of downloads: 0
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jpiper1921
post Feb 11 2019, 02:59 PM
Post#5



Posts: 36
Joined: 11-December 18



dflak - sorry to be a pain... can you save document in .doc format? I am limited with my tools. Also, just to be clear, I using Excel 2003 and trying to connect to Access 2019 Dbase table and or query. Thanks again.
Go to the top of the page
 
dflak
post Feb 11 2019, 03:03 PM
Post#6


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


If I recall correctly, there should be an add-on that you can get to read DOCX files even though you can't save them in the upgraded format

Anyway, here is the conversion.
Attached File(s)
Attached File  New_ODBC_64_Bit.zip ( 152.4K )Number of downloads: 1
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jpiper1921
post Feb 11 2019, 03:42 PM
Post#7



Posts: 36
Joined: 11-December 18



I found the file and built the connection. See attached screenshot. Final result is Excel 2003 says my .accdb is not a recognized database type. Uggh
Attached File(s)
Attached File  connection_options.GIF ( 84.8K )Number of downloads: 0
 
Go to the top of the page
 
dflak
post Feb 11 2019, 03:45 PM
Post#8


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


Explain the overall situation. Is this a one-time thing you need to do? Meaning that you need to get the data into Access and then move on or will the XLS files be changing all the time?

If the former, then open the XLS files and save as CSV and use those files as your Access source.

If the latter, then a program can be written that will do this wholesale.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jpiper1921
post Feb 11 2019, 04:48 PM
Post#9



Posts: 36
Joined: 11-December 18



Sorry... the data is in Access already; I want to link Excel to that data in Access. Somehow my explanation hasn't been adequate. Once I can establish the connection say to a table, I'll then expand and connect to queries. Thanks.
Go to the top of the page
 
dflak
post Feb 11 2019, 05:03 PM
Post#10


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


Then you have got to work it backwards. You will have to run an Access utility on the Access side to produce CSV files for Excel to read.

Excel 2003 does not recognize ACCDB formats. What's worse is that except for Access 2007, you can't convert an ACCDB to an MDB format. Later versions (2013 and later - not sure of 2010) won't back convert.

Short of upgrading to a more modern version of Excel that's the best I can offer.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jpiper1921
post Feb 12 2019, 08:12 AM
Post#11



Posts: 36
Joined: 11-December 18



dflak - Your info is important in my quest for an Office upgrade. My leadership insists on modern Excel reporting and I hope to influence them that an upgrade is needed. Thank you again for your guidance.
Go to the top of the page
 
GroverParkGeorge
post Feb 12 2019, 09:41 AM
Post#12


UA Admin
Posts: 34,584
Joined: 20-June 02
From: Newcastle, WA


Well, that depends....

Saving an accdb as an mdb from Access 2010 and later depends partly on what version specific features are actually in that particular accdb.

Attached File  saveass.jpg ( 85.49K )Number of downloads: 1


Off the top of my head, one of those features would be layout on forms, IIRC.

But a plain vanilla accdb can be saved as an mdb--I've done it several times from A2013 and A2016.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
dflak
post Feb 12 2019, 10:03 AM
Post#13


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


Good luck to you on your quest for an upgrade.

The upgrade from Office 2003 to Office 2007 was a huge leap in capability. It was not just a handful of features. The three biggest things were a jump from 65K rows of data to over a million, multiple select for filters and the introduction of Excel Tables. There were other features also introduced but these could be replicated with more work in 2003. There have also been many good features added since 2007.

Of course, in your case, the biggest need for post-2003 Excel is that it recognizes ACCDB files in Access.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
jpiper1921
post Feb 12 2019, 05:01 PM
Post#14



Posts: 36
Joined: 11-December 18



Yes. I have at previous job and personally at home kept up to date with newer versions. It's my new current job that is holding out with 2003 until forever maybe.
Go to the top of the page
 
dflak
post Feb 12 2019, 05:28 PM
Post#15


Utter Access VIP
Posts: 6,273
Joined: 22-June 04
From: North Carolina


We had to fight hard to get Excel 2007 to replace the 97 we were using. smile.gif Considering how much they pay us guys, they can recover the cost of a license in a couple of months' gained productivity.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd February 2019 - 09:17 AM