You save a record in a form or run an append query, and Access shows: “Run-time error 3155: ODBC – insert on a linked table failed.” The message does not say which table or why it failed—so fixing it feels like guesswork. This guide gives you the exact causes and step-by-step solutions so you can resolve Error 3155 yourself. You will find: the fastest fix (often under five minutes), how to identify the failing table and column, permanent fixes on SQL Server and ODBC, copy-paste VBA and ADO code when linked tables keep failing, and what not to do so you do not waste time. By the end you will know exactly what to change so inserts work every time. If you are still connecting Access to SQL Server, see Microsoft Access ODBC connection and convert Access to SQL first.

Quick Fix for Access Error 3155 (Try This First)

Most Error 3155 cases are fixed by one of these. Do them in order; stop when the insert works.

1. Relink the table (wrong server or database): In Access: External Data tab → Linked Table Manager. Check “Select All” or only the table that receives the insert. Click OK. If Access asks for the server or database, choose the correct SQL Server and database and finish. Try the insert again. If it used to work and stopped after a server move or rename, relinking usually fixes it.
2. Fix nvarchar(MAX) or varchar(MAX) on SQL Server (most common cause): In SQL Server Management Studio (SSMS), open the table that receives the insert. If any column is nvarchar(MAX) or varchar(MAX), change it to a fixed size (e.g. nvarchar(500) or varchar(1000)) using ALTER TABLE, and set a default of '' (empty string) if the column allows NULL. In Access, use Linked Table Manager → select that table → OK to relink. Then run the insert again. Details for this fix are in the “Fix nvarchar(MAX) on SQL Server” section below.
3. Set ODBC commit mode to commit immediately: Press Windows key, type “ODBC”, open “ODBC Data Sources (64-bit)” or “(32-bit)” to match your Access. Go to System DSN tab → select your SQL Server DSN → Configure. In the wizard, go to the end or look for “Commit” or “Transaction” and set it to “Commit immediately” or *NONE. Finish and test the insert again.
4. Give the SQL login INSERT permission: In SSMS, connect to the SQL Server. Find the login used by the Access link (Windows user or SQL login). In the database, ensure that login has INSERT (and SELECT) on the table. Right-click the table → Properties → Permissions, or run GRANT INSERT, SELECT ON dbo.YourTable TO YourLogin; then try the insert from Access again.

If the insert still fails after these four steps, use the detailed sections below to find the exact failing table, check timeouts and connection strings, and switch to an ADO-based insert if needed.

What Access Error 3155 Actually Means

Error 3155 means: Access sent an INSERT to a table that is linked via ODBC (almost always SQL Server), and the insert was rejected. Access does not show the real error from SQL Server—only “ODBC – insert on a linked table failed.” The failure can happen when you save a form, run an append query, or use VBA to add a record to a linked table. The real cause is usually one of:

1. the SQL Server table has nvarchar(MAX) or varchar(MAX) columns that the Jet/ACE ODBC driver handles badly on insert;
2. the link points to the wrong server, database, or login;
3. the SQL login does not have INSERT permission;
4. the connection timed out or the ODBC commit mode is wrong;
5. the data violates a constraint (duplicate key, NOT NULL, check).

Fixing 3155 means identifying the table and then applying the right fix for that cause.

How to Find Which Table Is Failing (So You Know What to Fix)

Access does not name the table in the error. Use context: If the error appears when you save a form, the failing table is the form’s RecordSource (the table or query the form is bound to). Open the form in Design View, click the form (not a control), press F4, and look at Record Source—that is the table (or base table of the query) that is failing. If the error appears when you run an append query, the failing table is the query’s destination. Open the query in Design View and check “Append To” (or in SQL view, the table after INTO). If you use VBA (Recordset.AddNew or RunSQL "INSERT..."), the failing table is the one you are inserting into. Once you know the table, check that table’s link, the SQL Server schema for that table, and the login’s permissions on that table.

Why Error 3155 Matters in Real Use

In production, 3155 often appears when users save from a form or when an append query runs. If you ignore it, data never reaches SQL Server and you get incomplete or missing records. Recurring 3155 can also point to a bad link (wrong server after a move), a driver that does not match your SQL Server version, or schema changes (new columns or types) that the linked table definition does not handle. Fixing it properly avoids silent data loss and support calls. For broader troubleshooting, troubleshooting Microsoft Access database issues covers locks, corruption, and other ODBC-related problems.

Common Causes of Access Error 3155 (Complete List)

• nvarchar(MAX) or varchar(MAX) columns (number-one cause): SQL Server columns defined as nvarchar(MAX) or varchar(MAX) often make ODBC inserts fail. The driver can mis-handle NULL or long text in these columns. Fix: change to nvarchar(n) or varchar(n) with a reasonable max (e.g. 500 or 2000) and a default of '' if needed, then relink the table in Access.
• Wrong or stale DSN / connection: The linked table points to the wrong server, database, or login—for example after a server rename, migration, or restore to a new server. SELECT might still work if the old server is still reachable; INSERT can fail. Fix: update the DSN or connection string and use Linked Table Manager to relink.
• SQL login lacks INSERT permission: The Windows or SQL login used by the ODBC connection has SELECT but not INSERT on the table. Inserts fail with 3155; opening the table or running SELECT may work. Fix: in SSMS, grant INSERT (and SELECT) on the table to that login.
• ODBC commit mode: Some ODBC driver configurations require “Commit immediately” (*NONE). If the DSN is set to a different transaction mode, inserts can fail. Fix: in ODBC Data Source Administrator, edit the System DSN and set commit mode to commit immediately.
• Timeout or dropped connection: Slow network or a large insert can hit the command timeout. The connection might also drop between operations. Fix: increase the ODBC command/query timeout in the DSN or in the connection string; use a stable network path.
• Reserved words or invalid data: Column names like Order, Date, or User can cause SQL syntax issues. Values that break constraints (duplicate primary key, NULL in a NOT NULL column, check constraint) can also produce 3155. Fix: in SQL Server use bracketed names or rename columns; in Access ensure data meets constraints.
• Wrong or old ODBC driver: Using an old “SQL Server” driver instead of “ODBC Driver 17 for SQL Server” (or 18) can cause odd insert behavior. Fix: install the correct driver and relink using that driver in the connection string or DSN.

Fix nvarchar(MAX) on SQL Server (Step-by-Step)

This is the fix that resolves Error 3155 in most cases. You change MAX-length columns to a bounded size on the server, then relink in Access.

1. Open SQL Server Management Studio: Connect to the SQL Server that holds the table. Expand Databases → your database → Tables. Find the table that receives the insert (the one you identified earlier).
2. Check column types: Right-click the table → Design. Look at each column’s Data Type. If you see nvarchar(MAX), varchar(MAX), or ntext/text, those can cause 3155. Note the column names.
3. Change MAX to a bounded size: In a new query window (against the same database), run ALTER TABLE to change each MAX column to a size you will never exceed. Example: if Notes is nvarchar(MAX) and you never need more than 2000 characters, use the script below. Use nvarchar(n) if you need Unicode; varchar(n) otherwise. If the column allows NULL and your Access form sends empty string, you can add a default of '' so existing rows and new inserts are consistent.

-- Replace YourTable and YourColumn with your table and column names.
-- Use a length that fits your data (e.g. 500, 1000, 2000).

ALTER TABLE dbo.YourTable
ALTER COLUMN YourColumn nvarchar(2000) NOT NULL;
-- If you want to allow NULL and default empty string:
-- ALTER COLUMN YourColumn nvarchar(2000) NULL;
-- Then: ALTER TABLE dbo.YourTable ADD CONSTRAINT DF_YourColumn DEFAULT (N'') FOR YourColumn;
GO

Run the script, then in Access open Linked Table Manager, select that table, click OK to relink. Try the insert again. If you have multiple MAX columns, alter each one. Do not use a size smaller than the longest value you will ever store—otherwise you will get truncation errors.

Set ODBC Commit Mode and Timeout (Exact Steps)

On the PC where Access runs (or on every PC if multiple users), open ODBC Data Source Administrator. In Windows 10/11: press Windows key, type “ODBC”, click “ODBC Data Sources (64-bit)” or “ODBC Data Sources (32-bit)”—use 32-bit if your Access is 32-bit (most common). Go to the System DSN tab. Select the DSN that your Access linked tables use (same name you see when you relink). Click Configure. In the SQL Server DSN wizard: step through until you see options like “Change the default database to” or an “Advanced” or “Finish” area. Look for “Commit mode”, “Transaction”, or “Connection” and set it to “Commit immediately” or *NONE (meaning no long transaction). Click Finish, OK. For timeout: in the same DSN configuration or in Advanced, look for “Query timeout” or “Connection timeout” and set to 60 or 120 seconds if inserts are slow. Save and test the insert from Access again.

Grant INSERT Permission on SQL Server (Exact Steps)

In SSMS, connect to the server. Expand Security → Logins and find the login used by Access (Windows user name or SQL login). In your database, run: GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.TableName TO LoginName; (replace TableName and LoginName). Or: right-click the table → Properties → Permissions → search for the login → check Insert (and Select). Click OK. From Access, try the insert again. If you use Windows authentication, the login is the Windows user; if you use SQL authentication in the connection string, the login is that SQL user.

Full Step-by-Step: Fix Error 3155 From Scratch

If you prefer one ordered checklist, follow this. Stop when the insert works.

1. Identify the failing table: If it’s a form save, check the form’s Record Source. If it’s an append query, check the query’s destination table. Note the table name.
2. Verify the link in Access: External Data → Linked Table Manager. Select that table (or Select All). Click OK. If a dialog appears asking for server/database, choose the correct server and database. If the list shows “OK” for all, the link path is current.
3. Test a simple insert: In Access, open the linked table in Datasheet view. Type one value in the first column (or required columns), then save (move to next row or Ctrl+S). If you get 3155 here, the problem is not your form or query—it’s the table link or the server.
4. Check permissions in SSMS: Log in to SSMS as the same Windows user or SQL login that Access uses. Run: INSERT INTO dbo.YourTable (Col1, Col2) VALUES ('test', 'test'); If this fails, the server is rejecting the insert (permissions or constraint). Grant INSERT to the login and fix any constraint. If it succeeds, the issue may be ODBC or data type.
5. Fix nvarchar(MAX) / varchar(MAX): In SSMS, check the table design for MAX columns. Alter them to nvarchar(n) or varchar(n) as in the “Fix nvarchar(MAX)” section. Relink the table in Access and test again.
6. Set ODBC commit mode and timeout: In ODBC Data Source Administrator (32-bit or 64-bit to match Access), edit the System DSN. Set commit mode to commit immediately. Increase query/connection timeout if needed. OK out and test.
7. Relink and retest: Linked Table Manager → select the table → OK. Run your form or append query again. If 3155 still appears, use the ADO insert method below so you bypass the linked-table ODBC path.

Connection String and Timeout (VBA Example)

When you link via code or need a DSN-less connection with a longer timeout, you can build the connection string and relink. This example sets the connection string on an existing linked table; increase ConnectTimeout and adjust server/database to match your environment.

' Relink a single table with a connection string that has a longer timeout
Public Sub RelinkTableWithTimeout()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strConnect As String

    strConnect = "ODBC;DRIVER=ODBC Driver 17 for SQL Server;" & _
                 "SERVER=YourServer;DATABASE=YourDB;Trusted_Connection=Yes;" & _
                 "Connection Timeout=30;"
    ' For SQL auth: add "UID=youruser;PWD=yourpass;" instead of Trusted_Connection=Yes

    Set db = CurrentDb
    Set tdf = db.TableDefs("YourLinkedTableName")
    tdf.Connect = strConnect
    tdf.RefreshLink
    Set tdf = Nothing
    Set db = Nothing
    MsgBox "Table relinked.", vbInformation
End Sub

Replace YourServer, YourDB, and YourLinkedTableName. Connection Timeout applies to opening the connection; for long-running inserts, you may need to set command timeout in the driver or use ADO with CommandTimeout. After schema or server changes, run Linked Table Manager or this kind of relink so the table definition is current.

Use ADO to Insert When Linked Tables Keep Failing (Copy-Paste Solution)

If you have fixed the schema, DSN, and permissions and Error 3155 still appears, stop using the linked table for that insert and use ADO from VBA. You open a direct connection to SQL Server and run an INSERT with parameters. That bypasses the Jet/ACE ODBC layer that causes 3155. Use this for the specific table or form that fails; you can keep linked tables for everything else. Below is a full example you can adapt: change the connection string, table name, and parameters to match your table.

' Insert one row into SQL Server via ADO. Replace server, database, table, and fields.
' Requires: Tools → References → Microsoft ActiveX Data Objects 6.1 (or 2.8).

Public Sub InsertOrderViaADO()
    Dim conn As Object
    Dim cmd As Object
    Dim strConn As String

    strConn = "Provider=SQLOLEDB;Data Source=YourServer;Initial Catalog=YourDB;"
    strConn = strConn & "Integrated Security=SSPI;"
    ' Or SQL login: strConn = strConn & "User ID=youruser;Password=yourpass;"

    Set conn = CreateObject("ADODB.Connection")
    conn.ConnectionTimeout = 30
    conn.Open strConn

    Set cmd = CreateObject("ADODB.Command")
    cmd.ActiveConnection = conn
    cmd.CommandType = 1   ' adCmdText
    cmd.CommandText = "INSERT INTO dbo.Orders (CustomerID, OrderDate, Notes) VALUES (?, ?, ?)"
    cmd.Parameters.Append cmd.CreateParameter("@p1", 3, 1, , 123)           ' adInteger=3, adParamInput=1
    cmd.Parameters.Append cmd.CreateParameter("@p2", 7, 1, , Date)         ' adDate=7
    cmd.Parameters.Append cmd.CreateParameter("@p3", 202, 1, 2000, "Notes")  ' adVarWChar=202, size 2000

    cmd.Execute
    conn.Close
    Set cmd = Nothing
    Set conn = Nothing
    MsgBox "Insert done.", vbInformation
End Sub

For a simple text INSERT without parameters (only if values are safe and not from user input), you can use: cmd.CommandText = "INSERT INTO dbo.Orders (CustomerID, OrderDate) VALUES (123, '2025-01-01')" and cmd.Execute. Prefer parameters to avoid SQL injection and type issues. After you confirm the insert works with ADO, you can call this sub from your form’s Save button or after validation instead of saving the bound form to the linked table. For migration and more connection options, see convert Access to SQL and Microsoft Access ODBC connection.

When to Use ADO Instead of Linked Table Inserts

Use linked tables when they work—they are simpler. Switch to ADO when: (1) Error 3155 keeps coming back after you fixed MAX columns and DSN; (2) you need a longer command timeout or transaction control; (3) you are doing bulk inserts and want one connection and batch logic. Do not rewrite every form to ADO; only the table or operation that fails. Keep linked tables for read-only or working write scenarios.

Troubleshooting: If This Happens, Do This

Use this to jump to the right fix instead of trying everything at random.

• Error appears only when saving a form: The failing table is the form’s Record Source. Fix that table’s link, its nvarchar(MAX) columns on SQL Server, and the login’s INSERT permission on that table.
• Error appears only when running an append query: The failing table is the query’s “Append To” table. Fix that table: relink, MAX columns, permissions. If the query appends from another linked table, also check that the source link and data types match.
• Insert worked before, stopped after server move or rename: The link still points to the old server or database. Update the DSN or connection string to the new server/database, then use Linked Table Manager to relink all affected tables.
• Error appears only for some records (e.g. when Notes is filled): Likely a long-text or MAX column. The row that works has short or NULL in that column; the row that fails has long text or a value the driver does not handle. Fix the MAX column on SQL Server as in the nvarchar(MAX) section.
• Error appears only over VPN or slow network: Timeout. Increase ODBC query/connection timeout in the DSN or connection string. Consider running the insert via ADO with a longer CommandTimeout.
• Same insert works in SSMS but not from Access: Permissions are OK (same login works in SSMS). The issue is ODBC or data type: fix MAX columns, set ODBC commit mode to commit immediately, and use the correct driver. If it still fails, use ADO from Access.

Common Mistakes When Fixing Error 3155

• Only relinking without fixing the cause: Relinking can temporarily mask a wrong server or DSN. If the server name or database changed, update the DSN or connection string and then relink. Otherwise the error returns.
• Ignoring nvarchar(MAX) / varchar(MAX): If the failing table has MAX columns, changing them to a bounded size (or avoiding NULL in them) often fixes 3155. Do not assume the problem is only network or permissions.
• Not checking the server: Always verify on SQL Server: same login can insert, table has correct schema, no constraint or trigger blocking the insert. Error 3155 does not show the server-side message—check SQL Server logs or run the insert in SSMS.
• Using an old or wrong ODBC driver: Use a driver that matches your SQL Server version (e.g. “ODBC Driver 17 for SQL Server”). Old “SQL Server” drivers can behave differently with newer types and settings.

Key Takeaways

• Access Error 3155 means the ODBC insert on a linked table failed. Find the failing table from the form’s Record Source or the append query’s destination.
• The most common fix: change nvarchar(MAX) and varchar(MAX) columns on SQL Server to nvarchar(n) or varchar(n), then relink the table in Access.
• Always verify: correct server/database in the link, INSERT permission for the SQL login on that table, ODBC commit mode set to commit immediately, and timeout high enough for slow networks.
• If 3155 continues after fixing schema and DSN, use ADO in VBA to run a parameterized INSERT so you bypass the Jet/ACE ODBC insert path.
• After any server move or DSN change, relink affected tables with Linked Table Manager and test with a single-row insert before trusting forms or queries.

Frequently Asked Questions

Conclusion and Next Steps

You now have a complete solution path for Access Error 3155: start with the quick fix (relink, fix nvarchar(MAX) on SQL Server, set ODBC commit mode, grant INSERT), follow the detailed steps to find the failing table and fix schema and permissions, and use the ADO insert code when linked tables still fail. Most users resolve 3155 by changing MAX columns to a bounded size and relinking. Keep this page bookmarked so you can run through the checklist the next time the error appears. For connecting Access to SQL Server and migration, see Microsoft Access ODBC connection and convert Access to SQL; for other errors and locks, troubleshooting Microsoft Access database issues. If you want Error 3155 fixed or your linked tables made reliable across your team, contact us for a free quote.