Microsoft KB Archive/248907

= ACC2000: Append Query on Linked Tables Adds Incorrect GUID Values =

Article ID: 248907

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q248907



Advanced: Requires expert coding, interoperability, and multiuser skills.

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



SYMPTOMS
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.



RESOLUTION
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



STATUS
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
 Use either Microsoft SQL Server 7.0 Enterprise Manager or Access 2000 to open the sample database NorthwindCS (log on as the database owner).  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 MakeTestTables

As

if 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  Save and close the stored procedure. Accept the default name of MakeTestTables, and then run the procedure by double-clicking it. 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. In the Link dialog box, change the Files of type box to ODBC Databases.</li> In the Select Data Source dialog box, open or create a data source name (DSN) that points to NorthwindCS.</li> 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.</li> Create a new query in Design view, but add no tables.</li> In the query design grid, click SQL View on the View menu.</li>  Type the following SQL statement: <pre class="fixed_text">INSERT INTO dbo_GUID_Test2 (ColA, ColB) SELECT dbo_GUID_Test.ColA, dbo_GUID_Test.ColB FROM dbo_GUID_Test; </li> Save and run the query.</li> 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.</li></ol>

Note that GUID values inserted into dbo_GUID_Test2.ColA do not match the GUID values in dbo_GUID_Test.ColA.

<div class="references_section">