Results 1 to 9 of 9
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Linking On Unique Identifier (Access 2003 SQL 2005)

    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
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking On Unique Identifier (Access 2003 SQL 2005)

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

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking On Unique Identifier (Access 2003 SQL 2005)

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

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Linking On Unique Identifier (Access 2003 SQL

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking On Unique Identifier (Access 2003 SQL

    I hope someone with SQL Server experience can help you.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Linking On Unique Identifier (Access 2003 SQL

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

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Linking On Unique Identifier (Access 2003 SQL

    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
    Andrew

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Linking On Unique Identifier (Access 2003 SQL

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

  9. #9
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Linking On Unique Identifier (Access 2003 SQL

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •