'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
Fix Excel Macro Error 1004 ODBC After 32-bit to 64-bit Upgrade
Question details
The user needs to fix a macro failure that occurs after migrating from a 32-bit to a 64-bit version of Office.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro to refresh a QueryTable connected to an external database in a newly upgraded 64-bit Excel environment.
- Observed behavior
- The macro halts execution and displays 'Run-time error 1004: General ODBC Error', specifically highlighting the line 'Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False'.
Ensure you have administrator privileges on your computer, as resolving ODBC architecture mismatches requires installing new 64-bit database drivers.
Install and Configure the 64-bit ODBC Driver
64-bit Excel cannot communicate with 32-bit ODBC drivers. You must install the 64-bit equivalent of your database driver to refresh your QueryTable.
When upgrading from Office 2019 32-bit to Office 2021 64-bit, the underlying architecture changes. Data Source Names (DSNs) and ODBC drivers configured for 32-bit applications are invisible to 64-bit applications. To fix the General ODBC Error, the correct 64-bit driver must be installed and configured.
Check your macro's connection string or the existing 32-bit ODBC settings to identify which database system (e.g., SQL Server, MySQL, Oracle) the workbook connects to.
Visit the database vendor's official website and download the 64-bit version of the ODBC driver.
Run the downloaded installer and follow the on-screen prompts to install the 64-bit driver on your Windows system.
Press the Windows key, search for 'ODBC Data Sources (64-bit)', and open the application. Recreate your System or User DSN exactly as it was named in the 32-bit version so your Excel macro can recognize it.
Update the Connection String in the VBA Macro
If you opted to use an OLEDB connection or if your 64-bit DSN name had to be changed, you must update the VBA code to reflect the new connection parameters.
Try WPS Office for Seamless Spreadsheet Management
Struggling with complex architecture upgrades and macro errors? WPS Office provides a highly compatible, lightweight, and free alternative to Microsoft Office. It handles standard spreadsheet formats effortlessly, allowing you to focus on your data without the steep troubleshooting curve.
- 1. Download the software: Visit the official WPS website and download the free WPS Office suite.
- 2. Install WPS Office: Run the installer and follow the quick setup instructions.
- 3. Open your files: Launch WPS Spreadsheets and open your existing Excel workbooks to seamlessly continue your work.
Frequently Asked Questions
Why does my Excel macro work in Office 32-bit but fail in Office 64-bit?
Office 64-bit requires all external dependencies, such as database drivers and API libraries, to also be 64-bit. If your macro attempts to call a 32-bit ODBC driver, it will fail because the architectures are fundamentally incompatible.
What does Run-time error 1004 General ODBC Error mean?
This error indicates that Excel could not establish a connection to an external database. In the context of migrating to a 64-bit environment, it almost always means the required 64-bit ODBC driver is missing or incorrectly configured.
How do I access the 64-bit ODBC settings on Windows?
Click the Windows Start button, type 'ODBC' in the search bar, and select 'ODBC Data Sources (64-bit)' from the list of results. This tool allows you to configure connections specifically for 64-bit applications like Excel 2021 64-bit.
