Linking On Unique Identifier (Access 2003 SQL 2005)

[AndrewKKWalker]

Having an issue BUT cannot figure out why. (actually 2 issues)
1. Have Access 2003 linked to SQL 2005 tables.
Primary Key in 1 is a uniqueidentifier data type and child in second is the same.
This is true on several tables.
Some link OK and some do not using this link
BUT Problem only occurs in Access.
If I do query in SQL then it works ok.
If I do it in Access I get NO return rows UNLESS query uses Outer Join with Child Link ID field set to NOT NULL.

2. Other issue I have is retrieving the UniqueIdentifier value in VBA to a variable.
Although the value exist ok it returns ?????? to the variable NOT the correct ID
Does this if I use variant, or String and error with Long.
Sometimes I need this value so how can I get it?

Any answers to either of these questions would save me a lot of aggravation and stress.

Over to you guys.

AW

[HansV]

About your second question, see ACC2000: "????????" Is Displayed as the Value of a SQL Server GUID Field (applies to later versions of Access too).

[HansV]

About problem 1: does it work better if you use a WHERE clause instead of a join?

[AndrewKKWalker]

Sadly NO.

It ONLY works with the outer join.

If the same query is tried on the SQL server it works OK.

Using either method link or where.

[HansV]

I hope someone with SQL Server experience can help you.

[WendellB]

I'll see if I can give you a hand. Several questions:
<UL><LI>I presume when you say you have SQL Server 2005 tables linked to an Access database that you are using ODBC. What SQL Server driver are you using?
<LI>Can you post the SQL Server view or query text so we can see the exact syntax you are using?
<LI>Can you post the field definitions from SQL Server for the two tables - a screen shot of the SQL server table design or the Access linked table design would work nicely, but keep it fairly small.[/list]We do work routinely with SQL Server as the backend and Access as the front-end, and haven't seen issues like that, so I suspect something is messed up either in the ODBC definition, or in the SQL syntax.

[AndrewKKWalker]

NO WORRIES!
Your idea of the driver was the issue (fine fellow can I say).
The machine I was testing on was linking tables via ODBC using the SQL Native Client Driver.
Changed it back to the SQL SERVER driver and all was ok again.

I wasted several hours this afternoon trying to figure this out, BUT reckon you just saved me a day at least.
I just didn't consider the driver an issue, because strangely some queries worked and others didn't.

Reckon I'll avoid Native Client driver with access from now on.

Interestingly, this is NOT a problem when using Access 2007.

Thanks

[WendellB]

I would certainly proceed with caution using the standard SQL Server ODBC driver, as I believe Microsoft strongly recommends using that for connecting to SQL Server 2005. And I've seen strange behavior when using that instead of the Native Client driver. Are you sure you are connecting to SQL Server 2005? The Native Client will give odd results when connecting to SQL Server 2000 for example. Also, if you are using any code that uses ADO, you may well have trouble using the incorrect driver.

[AndrewKKWalker]

YES.
It is 2005 with SP2 (it's the ONLY SQL server installed).
Not even MSDE or Express is used JUST 2005.

Have used the standard SQL SERVER driver with 2005 for ages on 3 different systems
and never had a problem (inc loads of stuff with ADODB).

Have also used the Native Driver for other systems.

Only time I have had any problems with either was today when joining the tables, and
Access 2003 could not link until I used the SQL driver.