Results 1 to 2 of 2
  1. #1
    Thomasso is offline Competent Performer
    Windows 10 Office 365
    Join Date
    Nov 2016
    Location
    Czech Republic
    Posts
    250

    Working with SQL Server HIERARCHYID data type in MS Access

    Hi all,



    I have recently come across the HIERARCHYID data type in SQL server and it looks like it's gonna work great for my hierarchies.

    I can produce something like this:

    Click image for larger version. 

Name:	hierarchy1.PNG 
Views:	14 
Size:	67.4 KB 
ID:	48109

    However, I would like to know how I should work with this in MS Access. For example, if I want to add a new category, I want to select the parent from a combo box (NULL = this is a new root, selected category = this is a child of that category).
    Can you please advise how I should do this, so the correct hex node is saved in the SQL Server?

    Another question would be about aggregate queries. Let's say that I want to show total sales of a category, including its child categories. How should I do that?

    Thanks a lot for any advice and wish you a nice weekend.

    Best regards,
    Tomas

  2. #2
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, I don't think access has a data type for this, but you can create a SQL view where you use tostring() to represent the level info in the field in a text string and link this to a text field in access.

    Code:
    select [Node].ToString() as NodeTextInfo

Please reply to this thread with any new information or opinions.

Similar Threads

  1. attachment data type in SQL Server
    By Esmatullaharifi in forum SQL Server
    Replies: 3
    Last Post: 05-15-2015, 12:07 AM
  2. Replies: 3
    Last Post: 05-31-2013, 04:32 PM
  3. How to Define a Hyperlink Data Type in SQL Server
    By EddieN1 in forum SQL Server
    Replies: 3
    Last Post: 02-07-2013, 11:26 AM
  4. Replies: 3
    Last Post: 05-07-2012, 07:57 PM
  5. Access and SQL Server data type problem
    By rayc in forum Access
    Replies: 1
    Last Post: 09-10-2010, 07:27 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums