1

Immediately after I add a new record into a SQL-linked table from my Access 2016 front end, an ODBC error message pops up and all data in the table turns to #Name?

This is a merge-replicated database.

The record DOES get successfully inserted prior to the error, I can see it after I close and re-open the table. Existing records can also be edited and deleted. The only problem is that after a new record is added, the ODBC is dropped with an Invalid Cast Specification (#0) error. It doesn't make sense that I'm putting invalid data in, because the new record is inserted with all data intact exactly how I entered it. I just have to close and reopen the table to view the new data.

I'm on week two of dealing with this and I feel like I've tried everything. I cannot have the system generate an error every time a new record is added in this specific table. All other tables in the database are fine.

Here is my table layout:

CREATE TABLE [dbo].[TrnRegistration](
    [fkClassSchedID] [int] NULL,
    [fkCompanyID] [int] NULL,
    [NumberUsers] [int] NULL,
    [Notes] [nvarchar](max) NULL,
    [Contact] [nvarchar](50) NULL,
    [fkUserTypeID] [int] NULL,
    [fkOrderTypeID] [int] NULL,
    [OrderRef] [nvarchar](50) NULL,
    [FloatClassID] [int] NULL,
    [LocFrstID] [int] NULL,
    [LocScndID] [int] NULL,
    [SalesRep] [int] NULL,
    [RegistrationID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [VerifDate] [bit] NOT NULL DEFAULT ((0)),
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL CONSTRAINT     [MSmerge_df_rowguid_8C3233BCD5FB4C7AA23281E8C482C778]  DEFAULT   (newsequentialid()),
 CONSTRAINT [TrnRegistration_PK] PRIMARY KEY NONCLUSTERED 
(
    [RegistrationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
dkimbrell
  • 67
  • 1
  • 8
  • 2
    I'm not sure if I'll be able to help but I'm confused about when the error occurs. You said the record insert is fine, because you can see the new record. But then you say when you get the error, the inserted record changes to #Name? And the error happens after insert. If the error happens after insert, how can you confirm that the insert was fine? Insert record -> Error -> record has #Name. Where in that flow you open the table and the insert looks fine? Does the error occur after you view the new record? – artm Jul 02 '16 at 05:00
  • Linked ODBC tables in Access have problems with some datatypes or column configurations. E.g. [Bit columns that can be NULL](http://stackoverflow.com/questions/4764508/nullable-bool-fields-in-ms-access-linked-tables), but that doesn't seem to be the problem here. My guess would be the `ROWGUIDCOL` column. What datatype does Access show for that in table design view? Do you have `ROWGUIDCOL` columns in other tables that work? – Andre Jul 02 '16 at 06:51
  • Try re-linking the table(s) using the standard SQL driver, not the native 10/11 driver. Right now, the native 11 driver does not work on your given table - and I am not sure why. However, the standard default SQL driver seems to work – Albert D. Kallal Jul 02 '16 at 07:24
  • artm - Here is my flow: Open Table>Enter data in new record>Hit Enter to save record>All data in all records (entire table display) instantly turns to #Name? and I get an ODBC failed error message>Close table filled with #Name? values>Re-open table>All records can be seen and edited with no problem, including the last one I just entered. – dkimbrell Jul 02 '16 at 11:07
  • @Andre - yes I think the Rowguidcol does have something to do with it because when I recreate the table in question from scratch in SQL and link to it via Access, it works fine. When I then add the table as an article to merge replication (which is exactly when Access automatically adds the ROWGUIDCOL), and re-link it to Access again, that is when the error starts happening. – dkimbrell Jul 02 '16 at 11:13
  • @Albert D. Kallal - I am linking the table with the driver that says simply "SQL Server", not the SQL Server Native Client 10.0 driver which is the only other one showing for SQL Server – dkimbrell Jul 02 '16 at 11:18
  • In Access, open the table in design view and see if Access is using the correct PK. It sometimes doesn't. If not, set it yourself (in Access) using DROP INDEX/CREATE INDEX commands. Open SQL Profiler and look at what Access is sending to SQL Server. This sounds very similar to an issue I discovered several years ago, for which there is no fix.When Access inserts a record, it immediately requests the record back using 'WHERE = NULL, which WE all know won't work. It then tries a different where clause. If that fails, it displays #DELETED. – AVG Jul 02 '16 at 13:02
  • What version of SQL Server are you using? Which ODBC driver are you using? As Albert suggested, have you tried a different ODBC driver? Since the issue seems to be with the rowguid column, instead of linking to the table, create a view casting rowguid as varchar. Then link to the view. SQL Server will create the new guid anyway, so no need for it to be editable in Access. – AVG Jul 02 '16 at 13:28
  • It is certainly possible that the rowguid is overflowing the number value. Best as noted is to create a view and leave out that replication column. – Albert D. Kallal Jul 03 '16 at 22:22

1 Answers1

0

If I paste in your create table, then linking with native 11 does not work, but the default standard driver (the one you seem to be using) does work. However since my test is not a replicated database then it makes sense the column used for replication is the issue.

The simple solution is to thus create a view leaving out the replication column (rowGuid). You then link to that view in place of the table. You can of course after linking delete the original table link and then re-name the linked table to the SAME name as you used in Access – this suggestion will thus allow all forms/reports/code/SQL to work as before based on the original table name used.

So using a view with the one column left out, and linking to that view from Access should allow this to work.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51