You are currently offline, waiting for your internet to reconnect

ACC2000: Append Query on Linked Tables Adds Incorrect GUID Values

This article was previously published under Q248907
This article has been archived. It is offered "as is" and will no longer be updated.
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).

You have an append query that takes values from a linked SQL Server table that contains a field of type UniqueIdentifier and appends the values to another linked SQL Server table. When you open the table to which the records are appended, you find that the UniqueIdentifier field for each record appended contains values different from those in the source table.
Obtain the latest Microsoft Jet 4.0 service pack that contains an updated version of the Microsoft Jet 4.0 database engine.

For additional information about how to obtain the latest version of the Jet 4.0 database engine, click the following article number to view the article in the Microsoft Knowledge Base:
239114 How To: Obtain the Latest Service Pack for the Microsoft Jet 4.0 Database Engine
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Steps to Reproduce Behavior

  1. Use either Microsoft SQL Server 7.0 Enterprise Manager or Access 2000 to open the sample database NorthwindCS (log on as the database owner).
  2. Use the following SQL syntax to create a new stored procedure. This procedure creates two test tables in the SQL Server or MSDE database.
    Create Procedure MakeTestTablesAsif exists (select * from sysobjects where id =object_id(N'[dbo].[GUID_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[GUID_Test]if exists (select * from sysobjects where id =object_id(N'[dbo].[GUID_Test2]') and OBJECTPROPERTY(id, N'IsUserTable') =1)drop table [dbo].[GUID_Test2]CREATE TABLE [dbo].[GUID_Test] (	[ColA]  uniqueidentifier ROWGUIDCOL  NOT NULL ,	[ColB] [varchar] (50) NULL )                        ON [PRIMARY]ALTER TABLE [dbo].[GUID_Test] WITH NOCHECK ADD 	CONSTRAINT [PK_GUID_Test] PRIMARY KEY  NONCLUSTERED 	([ColA])            ON [PRIMARY] CREATE TABLE [dbo].[GUID_Test2] (	[ColA] [uniqueidentifier] NULL ,	[ColB] [varchar] (50) NULL )             ON [PRIMARY]INSERT INTO GUID_TEST VALUES ('{DBAB6FFE-82B2-4D65-819E-32DD4D904C51}','TESTRECORD1')INSERT INTO GUID_TEST VALUES ('{DBAB6FFE-82B2-4D65-819E-32DD4D904C52}','TESTRECORD2')INSERT INTO GUID_TEST VALUES ('{DBAB6FFE-82B2-4D65-819E-32DD4D904C53}','TESTRECORD3')return					
  3. Save and close the stored procedure. Accept the default name of MakeTestTables, and then run the procedure by double-clicking it.
  4. Open Access 2000 and create a new Access Database named MyTest.mdb.On the File menu, point to Get External Data, and then click Link Tables.
  5. In the Link dialog box, change the Files of type box to ODBC Databases.
  6. In the Select Data Source dialog box, open or create a data source name (DSN) that points to NorthwindCS.
  7. Create links to the GUID_Test and GUID_Test2 tables on the server. Leave the linked tables with the names dbo_GUID_Test and dbo_GUID_Test2. If you are asked for an index column, select ColA.
  8. Create a new query in Design view, but add no tables.
  9. In the query design grid, click SQL View on the View menu.
  10. Type the following SQL statement:
    INSERT INTO dbo_GUID_Test2 (ColA, ColB)SELECT dbo_GUID_Test.ColA, dbo_GUID_Test.ColBFROM dbo_GUID_Test;					
  11. Save and run the query.
  12. Open both the dbo_GUID_Test and dbo_GUID_Test2 tables. Size the windows and columns so that you can see their entire contents at the same time.
Note that GUID values inserted into dbo_GUID_Test2.ColA do not match the GUID values in dbo_GUID_Test.ColA.
For additional information about another issue involving linked tables with UniqueIdentifier fields, click the article number below to view the article in the Microsoft Knowledge Base:
253837 ACC2000: #Deleted in Linked Table Containing UniqueIdentifier
pra not correct wrong don't aren't

Article ID: 248907 - Last Review: 10/22/2013 04:14:15 - Revision: 3.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbbug kbfix KB248907