Access VB to Stored Procedure Question

• Bob Keeney

  Newbie

  Points: 1

  More actions

  March 3, 2005 at 5:27 pm

  #92615

  I'm a newbie to SQL Server so be gentle....

  I inherited an Access project that populates a temporary table for hierarchical data (maximum of ten levels deep). I would like to move all of this code on to the server for speed but I have no clue on how to put this into a stored procedure/view/whatever.

  Any help is appreciated.

  Thanks!

  Bob K.

  s = "INSERT INTO CusJobsExplosionTBL (L0, N0, Name, NestLevel, CusJobID) SELECT CustJobs.id, CustJobs.Name, CustJobs.Name, CustJobs.NestLevel, CustJobs.id FROM CustJobs WHERE CustJobs.NestLevel=0 AND CustJobs.Inactive=False; 'level 0

  DoCmd.RunSQL s

  For n = 1 To 9

  s = "SELECT CustJobs.id, CustJobs.Name as CustJobsName, CustJobs.NestLevel as CustJobsNestLevel, CusJobsExplosionTBL.* FROM CusJobsExplosionTBL INNER JOIN CustJobs ON CusJobsExplosionTBL.L" & n - 1 & " = CustJobs.ParentID WHERE CustJobs.NestLevel=" & n & " AND CusJobsExplosionTBL.L" & n & " = 0 AND CustJobs.Inactive=False;"

  Set rs = db.OpenRecordset(s, dbOpenSnapshot)

  If rs.EOF Then GoTo OptionAddEdit_AfterUpdate_Exit

  Set rsCusJobsExplosionTBL = db.OpenRecordset("CusJobsExplosionTBL") 'output table

  Do While Not rs.EOF

  With rsCusJobsExplosionTBL

  .AddNew

  !L0 = rs!L0

  !L1 = rs!L1

  !L2 = rs!L2

  !L3 = rs!L3

  !L4 = rs!L4

  !L5 = rs!L5

  !L6 = rs!L6

  !L7 = rs!L7

  !L8 = rs!L8

  !L9 = rs!L9

  !N0 = rs!N0

  !N1 = rs!N1

  !N2 = rs!N2

  !N3 = rs!N3

  !N4 = rs!N4

  !N5 = rs!N5

  !N6 = rs!N6

  !N7 = rs!N7

  !N8 = rs!N8

  !N9 = rs!N9

  !CusJobID = rs!id

  !Name = Space(rs!CustJobsNestLevel * 2) & rs!CustJobsName

  !NestLevel = rs!CustJobsNestLevel

  .Fields("L" & n) = rs!id

  .Fields("N" & n) = rs!CustJobsName

  .Update

  End With

  rs.MoveNext

  Loop

  Next

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  March 3, 2005 at 10:50 pm

  #545383

  Change CustJobs.Inactive=False to  CustJobs.Inactive=0

  this is the only change that i can see

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 4, 2005 at 12:33 am

  #545393

  There are many ways for this. If you search the web for trees and/or hierachies and SQL Server, you'll find some very good approaches. Maybe you can start from here: http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

  Good thing about this link is, that it contains additional reference material for further investigations. And, yes, Joe Celko, has written a whole book on this topic. I know, I know.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]