'%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)
How to Troubleshoot Access Linked Tables with a Remote SQL Server
Question details
The user needs to re-establish a connection between an MS Access front end and remote SQL Server linked tables after a network switch.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Attempting to use an Access database with linked SQL Server tables after switching network adapters (e.g., from Ethernet to Wi-Fi).
- Observed behavior
- The linked SQL tables stop working and fail to retrieve data, indicating a broken remote connection to the SQL Server.
Ensure you have administrator access to both your local machine (to check ODBC Data Sources) and the remote SQL Server (to verify Configuration Manager and firewall settings).
Configure SQL Server TCP/IP and Port Settings
Verify that SQL Server is set up to allow remote connections and that the TCP/IP protocol is configured to use the correct port.
By default, SQL Server might have TCP/IP disabled or configured to use dynamic ports, which can cause connection failures when a client like MS Access tries to connect remotely from a new network.
Open SQL Server Management Studio (SSMS), right-click your server, select 'Properties', navigate to the 'Connections' tab, and check 'Allow remote connections to this server'.
Launch SQL Server Configuration Manager, expand 'SQL Server Network Configuration', and select 'Protocols for [YourInstanceName]'.
Right-click 'TCP/IP' in the right pane and select 'Enable'. Then right-click it again and choose 'Properties'.
Navigate to the 'IP Addresses' tab. Scroll down to 'IPAll', clear any value in 'TCP Dynamic Ports' to leave it blank, and set 'TCP Port' to 1433. Click 'OK' and restart the SQL Server service.
Verify Windows Firewall and DSN Configurations
Ensure the firewall on the SQL Server is not blocking the connection and that your ODBC DSN points to the correct server address.
Looking for a Lightweight Alternative to Microsoft Office?
While Microsoft Access is used for robust database front ends, many standard data management and reporting tasks can be completed in WPS Spreadsheet. WPS Office offers a free, lightweight alternative that is highly compatible with Microsoft Office formats, allowing you to connect to external data sources easily without the overhead of heavy enterprise software.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Install WPS Office: Run the downloaded setup file and follow the standard on-screen instructions.
- 3. Connect to Data: Open WPS Spreadsheet, go to the 'Data' tab, and use the 'Import Data' feature to connect to your ODBC data sources.
Frequently Asked Questions
Why did my Access linked tables break after switching to Wi-Fi?
Switching to Wi-Fi can assign your device a new IP address or place you on a different subnet. If the SQL Server firewall restricts access to specific IP ranges, or if the Wi-Fi network blocks outbound traffic on port 1433, the connection to your linked tables will fail.
How do I refresh linked tables in MS Access?
Open your Access database, go to the 'External Data' tab, and click 'Linked Table Manager'. Select the check boxes for the tables you need to refresh, and click 'OK' to re-establish the connection to the SQL Server.
What is the default TCP port for SQL Server?
The default port for a standard SQL Server instance is TCP 1433. You must ensure this port is open in your server's firewall and properly mapped in your router if accessing it over a public network.
Can I connect WPS Spreadsheet to a SQL Server database?
Yes, similar to Excel, WPS Spreadsheet allows you to import data from external databases. You can use an ODBC data source connection to query and retrieve data from a SQL Server directly into your spreadsheet for analysis.
