MS Access - Joining GUID and String columns

Question

I'm having trouble with Access, when joining two tables where one of the join columns is of varchar and the other of guid type.

The generated SQL statement looks as follows:

INSERT INTO adnVFD
SELECT dbo_adnVFD.*
FROM PRC INNER JOIN dbo_adnVFD ON PRC.PrcId = dbo_adnVFD.VarType;

I tried to convert the PRC.PrcId column using the StringFromGUID function, so that both columns should be of the same type. But without success.

Any ideas about how to solve this issue?

score 1 · Accepted Answer · answered Jun 29 '12 at 13:03

1

How about using a derived table, for example:

SELECT  b.* 
FROM Table2 b
INNER JOIN (SELECT StringFromGUID([ID]) As Expr1 FROM Table1) a
ON b.Expr1=a.Expr1

answered Jun 29 '12 at 13:03

Fionnuala

I tested before I posted. Are your tables both MS Access? – Fionnuala Jun 29 '12 at 14:39
No, actually I'm importing rows from SQL server with the help of linked tables. I link a couple of tables from SQL server. In the first query I add a where clause, while for the subsequent queries I simply join on the (not linked) tables in order to select only related data. – Marc Jun 29 '12 at 14:45
Can you use a passthrough query and , say, http://stackoverflow.com/questions/6299918/sql-server-converting-uniqueidentifier-to-string-in-a-case-statment? I do not think you are going to get this to work with MS Access functions. – Fionnuala Jun 29 '12 at 14:49

Lynn Crumbling · Answer 2 · 2012-06-29T13:10:07.697

If I

(1) Create a new table that has two fields -- (a) ID, set as an autonumber, field size replication ID (aka Guid), and (b) ID2, text(255)

(2) Add a row, let it create the autonumber, and copy and paste that from ID into ID2

(3) SELECT Table1.ID, Table1.ID2 FROM Table1 where ID = ID2

I get back my row. Note that the pasted string does contain braces. You may want to make sure the comparison isn't dropping them somehow.

I then created a second table, with two text fields, both text(255), and joined on:

SELECT Table1.ID, Table1.ID2, Table2.Field1
FROM Table1 INNER JOIN Table2 ON Table1.ID = Table2.ID;

Which worked fine and brought me back my row. My money's on missing braces in your case. I didn't need to use StringFromGUID() at all.

2 Answers2