Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2010
    Posts
    11

    Unanswered: Access Module to SQL Conversion - HELP!

    Hi Guys,

    I've inherited some Access databases which I have managed to get into SQL. However I have zero programming skills and I am struggling getting a module replication and migrated to SQL, or at the very least getting it to work with the now SQL environment.

    I know that it could well be to do with the library being used but I have no idea what this should be. Code is shown below:

    Code:
    Function CreateBuffer()
    
        DoCmd.SetWarnings False
        DoCmd.OpenQuery "Buffer_CLEAR_qry", acViewNormal, acEdit
        DoCmd.OpenQuery "Buffer_ADD_qry", acViewNormal, acEdit
    
    Dim MyDB As Database, MyTable As Recordset, BUFF As Recordset
    Set MyDB = DBEngine.Workspaces(0).Databases(0)
    Set MyTable = MyDB.OpenRecordset("DPC_TABLE_Defects_found", DdbOpenDb)        ' Open table.
    Set BUFF = MyDB.OpenRecordset("BUFFER", dbOpendb)        ' Open table.
    
    Dim FldName
    Dim A
    Dim DEFECT, LOC, Rec
    
    MyTable.MoveFirst
    For A = 1 To MyTable.RecordCount
    
    If MyTable!DEFECT1TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT1TYPE
         LOC = MyTable!DEFECT1LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
        
    If MyTable!DEFECT2TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT2TYPE
         LOC = MyTable!DEFECT2LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
        
    If MyTable!DEFECT3TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT3TYPE
         LOC = MyTable!DEFECT3LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT4TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT4TYPE
         LOC = MyTable!DEFECT4LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT5TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT5TYPE
         LOC = MyTable!DEFECT5LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT6TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT6TYPE
         LOC = MyTable!DEFECT6LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT7TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT7TYPE
         LOC = MyTable!DEFECT7LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT8TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT8TYPE
         LOC = MyTable!DEFECT8LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT9TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT9TYPE
         LOC = MyTable!DEFECT9LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT10TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT10TYPE
         LOC = MyTable!DEFECT10LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    '11 On
    
    If MyTable!DEFECT11TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT11TYPE
         LOC = MyTable!DEFECT11LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
        
    If MyTable!DEFECT12TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT12TYPE
         LOC = MyTable!DEFECT12LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
        
    If MyTable!DEFECT13TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT13TYPE
         LOC = MyTable!DEFECT13LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT14TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT14TYPE
         LOC = MyTable!DEFECT14LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT15TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT15TYPE
         LOC = MyTable!DEFECT15LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT16TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT16TYPE
         LOC = MyTable!DEFECT16LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT17TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT17TYPE
         LOC = MyTable!DEFECT17LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT18TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT18TYPE
         LOC = MyTable!DEFECT18LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT19TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT19TYPE
         LOC = MyTable!DEFECT19LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
    
    If MyTable!DEFECT20TYPE <> "NONE" Then
         DEFECT = MyTable!DEFECT20TYPE
         LOC = MyTable!DEFECT20LOC
         Rec = MyTable!RecNo
        GoSub BUFF_Write
        End If
           
          
       MyTable.MoveNext
       Next
       
    DoCmd.SetWarnings True
    Exit Function
    
    BUFF_Write:
        With BUFF
                .MoveNext ' Check if there's a record to update
                If .BOF Then
                 .AddNew 'MoveLast
                 .Update
               .MoveNext
                   End If
                   If .EOF Then
                   .AddNew 'MoveLast
                   .Update
                   .MovePrevious
                    End If
            .Edit
            .AddNew
            !RecNo = Rec
            !DEFECTTYPE = DEFECT
            !DEFECTLOC = LOC
            .Update
            
            DEFECT = ""
            LOC = ""
            Rec = 0
            End With
      Return
    This, from what I can tell, grabs the data entered in a form from a table and basically pivots the data into a buffer table so reporting is made easier. Re-writing the application, thought the right thing to do isn't an option currently.

    Any help with this is GREATLY appreciated....to say I'm lost is an understatement. Like I say, the errors occur when it attempts to open the db tables which are "linked" tables in the Access db.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    There's no reason to move this from the application to the Server as far as I can see. This sort of thing should stay at the front end. It could be improved a bit (better handling the buttock loosening use of DoCmd.SetWarnings False for example) but otherwise I would leave it exactly where it is.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Actually, reading it again this appears to be taking a table with repeating groups and normalising it in a cursor.
    Ok - this should be rewritten in a set based format. Also, the design of the DPC_TABLE_Defects_found table is highly suspect.
    UNPIVOT: Normalizing data on the fly
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jul 2010
    Posts
    11
    Thanks for your replies Pootle, but I'm still no further forward really. The Access forms and update queries behind them all talk to SQL without any issue, however this module to aid reporting won't run at all.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have a read of the link. Where it uses the example contact1, contact2, contact3 you would use Defect1Type, Defect2Type, Defect3Type etc.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jul 2010
    Posts
    11
    Cheers for the help mate. Appreciated...but perhaps I understated my n00bness. I'm n00b to the max when it comes to this and I still don't know what the hell I'm looking at. If you can maybe give me an example using my own code to set me on my way?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - do you understand what the code you posted is doing? Do you know what first normal form is? Can you read and write any SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jul 2010
    Posts
    11
    I understand what the code is doing sort of. Effectively there are multiple bits of data against an individual record....this code is parsing the data and splitting it down so each time there is a "DEFECTLOC" or "DEFECTTYPE" it creates a new row based on the record number.

    Basically the "unpivot" but I had a fiddle and I couldn't get it to work with my data

    As for first normal form - my understanding is that effectively one row = one bit of data relating to it...other than that no

    And finally, SQL, yes I can read it....but no I can't write it without a bit of googling.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes, that's correct.

    Tell you what, post what you "fiddled" and I'll help correct it. I don't have time to write it ATM.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jul 2010
    Posts
    11
    Quote Originally Posted by pootle flump View Post
    Yes, that's correct.

    Tell you what, post what you "fiddled" and I'll help correct it. I don't have time to write it ATM.
    Firstly thanks very much for your help.

    What I have so far in a Query is this:

    Code:
    select RecNo, DefectType
    from dbo.DPC_TABLE_FPIDEFECTS_DefectsFound
    unpivot (DefectType for DefectID in (DEFECT1TYPE, DEFECT2TYPE, DEFECT3TYPE, DEFECT4TYPE, DEFECT5TYPE, DEFECT6TYPE, DEFECT7TYPE, DEFECT8TYPE)) as DefectType
    This returns 8 rows of each found record number, with the relevant defectype shown in a column called "defecttype". I obviously need to created another unpivot (nested?) for the Defect'x'Loc column and consolidate into one query?

    I was also wondering if I could easily add some validation such as "don't unpivot if defecttype is NONE or defectloc is NONE" sort of thing?

    Cheers

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Since you have two columns you would be better served with the UNION solution.
    Code:
    SELECT recNo, DefectType = DEFECT1TYPE, DefectLoc = DEFECT1Loc
    FROM dbo.DPC_TABLE_FPIDEFECTS_DefectsFound
    WHERE 'None' NOT IN (DEFECT1TYPE, DEFECT1Loc)
    UNION ALL
    SELECT recNo, DefectType = DEFECT2TYPE, DefectLoc = DEFECT2Loc
    FROM dbo.DPC_TABLE_FPIDEFECTS_DefectsFound
    WHERE 'None' NOT IN (DEFECT2TYPE, DEFECT2Loc)
    .....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jul 2010
    Posts
    11
    Quote Originally Posted by pootle flump View Post
    Since you have two columns you would be better served with the UNION solution.
    Code:
    SELECT recNo, DefectType = DEFECT1TYPE, DefectLoc = DEFECT1Loc
    FROM dbo.DPC_TABLE_FPIDEFECTS_DefectsFound
    WHERE 'None' NOT IN (DEFECT1TYPE, DEFECT1Loc)
    UNION ALL
    SELECT recNo, DefectType = DEFECT2TYPE, DefectLoc = DEFECT2Loc
    FROM dbo.DPC_TABLE_FPIDEFECTS_DefectsFound
    WHERE 'None' NOT IN (DEFECT2TYPE, DEFECT2Loc)
    .....
    Thanks again for your help. I've tried that but I keep getting an "Incorrect syntax near 'ALL'" error in the query I run it in?

  13. #13
    Join Date
    Jul 2010
    Posts
    11
    Scratch that...I hadn't given it anything to "union" - cheers for your help mate! Really impressed with my first post's response!

Posting Permissions

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