Hello, I am doing a project where I am using Access along with a linked table from SQL server using ODBC source.
The data is being put in to the table by using a module in access with VBA code. The VBA code uses a select query that has been created before running the module. After going through the first 43 records, I get the ODBC insert on table failed. Any ideas how I can get pass this?
The current driver for the ODBC is SQL Server and not SQL Server Native Client 11.0. I did try SQL Server Native client but I was getting the ODBC Call Failed error.
I've been messing with the Execution Timeout connection time. Nothing seems to work.
The table from the SQL side comes from a Database that is password protected and I do have admin rights.
I don't think it is a timeout issue - had that been the case, you would not consistently get an error after exactly 43 records. It is likely not a driver issue either. Had that been the case, you should not have been able to insert any records at all.
What I suspect is the data in the 44th record. Is it violating some constraint on the table. Perhaps a primary key violation or unique index or check constraint violation. Examine the 44th record and see what is special about it. You can catch the error in VB code and see what that says.
I am not sure but several years ago when we did this if the datatype or column size were different between SQL and Access there could be a problem. I seem to remember a problem with BIT.
NVARCHAR(MAX) can accept 200K characters or more (the limit is like 2GB which is about a billion characters).
More likely, the cut-off/error is happening not within SQL Server, but in transit. For NVARCHAR(MAX), the parameter datatype should be SQL_WVARCHAR, but I don't know which versions/api's accept that. Do a search for how to define parameters to accept large value types