Results 1 to 11 of 11
  1. #1
    max is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2011
    Posts
    17

    SQL crashes access

    Hi, The following SQL code crashes access, can anyone see why?

    Thanks

    Set Rst4 = CurrentDb.OpenRecordset("SELECT CurrentPrimaryQry.Location, CurrentPrimaryQry.FanName, FanConfigurationTbl.ConfigurationDate, FanPerformanceTbl.Q, FanPerformanceTbl.StaticPressure " & _
    " FROM (CurrentPrimaryQry INNER JOIN FanConfigurationTbl ON (CurrentPrimaryQry.MaxOfConfigurationDate = FanConfigurationTbl.ConfigurationDate) AND (CurrentPrimaryQry.FanIndexID = FanConfigurationTbl.FanIndexID)) " & _
    " INNER JOIN FanPerformanceTbl ON FanConfigurationTbl.ConfigurationID = FanPerformanceTbl.ConfigurationID " & _
    " WHERE CurrentPrimaryQry.Location='" & LocationName & "' ORDER BY FanPerformanceTbl.Q;", dbOpenSnapshot)


  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you try this SQL in the Access query designer?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    max is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2011
    Posts
    17
    I did, that is where it came from....

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So it works in the Access query?

    Where is the value for LocationName coming from?

    What is the error message?

    Show the entire procedure code.

    Try saving the Access query object then referring to the saved query as the source for the VBA recordset.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    max is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2011
    Posts
    17

    Query def does not help

    Hi,

    I tried putting SQL into Querydef but this does not help. I have the same crash. When I try to populate my recordset 'Rst4' Access crashes with the message ' Microsoft Office Access has encountered a problem and needs to close......'

    Code:
    Sub ViewOemData()
    Set appExcel = Excel.Application
    appExcel.Visible = True
    Set wbook = appExcel.Workbooks("Jundee Pressure surveys Template.xls")
    Set wsheet = appExcel.Worksheets(LocationName)
    cnt3 = 0
        P1 = wsheet.Cells(25, 20) * 1000
        Q1 = wsheet.Cells(15, 2)
    Set wsheet = appExcel.Worksheets("Fan Data")
        wsheet.Cells(8, 3 * NoCurves) = Q1
        wsheet.Cells(9, 3 * NoCurves) = P1
        
    With CurrentDb
        Set QdfOemData = .CreateQueryDef("", "SELECT CurrentPrimaryQry.Location, CurrentPrimaryQry.FanName, FanConfigurationTbl.ConfigurationDate, FanPerformanceTbl.Q, FanPerformanceTbl.StaticPressure " & _
                                        " FROM (CurrentPrimaryQry " & _
                                        " INNER JOIN FanConfigurationTbl ON (CurrentPrimaryQry.MaxOfConfigurationDate = FanConfigurationTbl.ConfigurationDate) " & _
                                        " AND (CurrentPrimaryQry.FanIndexID = FanConfigurationTbl.FanIndexID)) " & _
                                        " INNER JOIN FanPerformanceTbl ON FanConfigurationTbl.ConfigurationID = FanPerformanceTbl.ConfigurationID " & _
                                        " WHERE CurrentPrimaryQry.Location='" & LocationName & "' " & _
                                        " ORDER BY FanPerformanceTbl.Q;")
                                        
    End With
    With QdfOemData
        Set Rst4 = .OpenRecordset(dbOpenSnapshot)
             With Rst4
             
                 wsheet.Cells(6, 3 * NoCurves - 1) = .Fields(1)
                 wsheet.Cells(41, 3 * NoCurves - 1) = .Fields(1)
                 wsheet.Cells(12, 3 * NoCurves - 1) = .Fields(0)
                 
                 Do While Not .EOF
                 
                     wsheet.Cells(44 + cnt1, 3 * NoCurves - 1) = .Fields(3)
                     wsheet.Cells(44 + cnt1, 3 * NoCurves) = .Fields(4)
                     
                     .MoveNext
            
                 Loop
                 
             End With
             
    End With
            
    End Sub

    Any help much appreciated

  6. #6
    max is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2011
    Posts
    17
    Crashes at line 24

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Try declaring the rst

    Dim rst As ADODB.Recordset

    Also, set reference to Microsoft ActiveX Date Objects x.x Library

    You are setting two cells to Fields(1), is that correct?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    max is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2011
    Posts
    17

    hmmm...

    Hi,

    Better show the whole module,

    I have already declared the recordset Rst4 - do I need to declare as ADODB? Am I using ADODB? I thought I was using DAO? (you have probably gathered I have no idea what the differences are!)

    I cant find Microsoft ActiveX Date Objects x.x Library - Is this a typo, should it be... Data Objects...

    Code:
    'ALLOWS SELECTED TEST DATA TO BE VIEWED IN XL
    Option Compare Database
    Option Explicit
    Dim OpXl As String
    Dim Rst1 As Recordset
    Dim Rst2 As Recordset
    Dim Rst3 As Recordset
    Dim Rst4 As Recordset
    Dim QdfOemData As QueryDef
    Dim appExcel As Excel.Application
    Dim wbook As Excel.Workbook
    Dim wsheet As Excel.Worksheet
    Dim LocationID As Integer
    Dim LocationName As String
    Dim P1 As Single
    Dim Q1 As Single
    Dim NoCurves As Integer
    Dim cnt1 As Integer
    Dim cnt2 As Integer
    Dim cnt3 As Integer
    
    Sub TestIndex_DblClick(Cancel As Integer)
    SelectViewData
    End Sub
    Static Sub SelectViewData() ' Puts query data into recordset for manipulation
    NoCurves = NoCurves + 1
    Dim TestInd As Integer
    TestInd = Forms!TestsTbl1!TestIndex
            Set Rst1 = CurrentDb.OpenRecordset("SELECT TestsTbl.TestIndex, TestsTbl.LocationID, TestsTbl.TestDate, TestsTbl.DriveEndTemp, TestsTbl.NonDriveEndTemp, TestsTbl.Vibration, TestsTbl.Current, TestsTbl.SurfaceBarometricPressue, TestsTbl.AirwayWetBulbTemp, TestsTbl.AirwayDryBulbTemp, ReadingsTbl.ReadingIndex, ReadingsTbl.TotalPressure, ReadingsTbl.VelocityPressure FROM TestsTbl INNER JOIN ReadingsTbl ON TestsTbl.TestIndex = ReadingsTbl.TestIndex WHERE (((TestsTbl.TestIndex)=" & TestInd & "));", dbOpenSnapshot)
            ViewDatainXL Rst1
            
            ViewFanAndLocationData
            
            ViewOemData
            
            
    End Sub
    Sub ViewDatainXL(Rst1 As Recordset)
     'Enumerate the specified Recordset Object
     
        Dim appExcel As Excel.Application
        Dim wbook As Excel.Workbook
        Dim wsheet As Excel.Worksheet
        
       
        
          
        If OpXl <> "yes" Then   'Ensures XL only opens once
            Set appExcel = New Excel.Application
            appExcel.Visible = True
            Set wbook = appExcel.Workbooks.Open("G:\Underground\Engineering\VENT\Jundee Fans db\Jundee Pressure surveys Template.xls")
            OpXl = "yes"
     
        
        ElseIf OpXl = "yes" Then   'Ensures XL only opens once
            Set appExcel = Excel.Application
            appExcel.Visible = True
            Set wbook = appExcel.Workbooks("Jundee Pressure surveys Template.xls") 'Open("G:\Underground\Engineering\VENT\Jundee Fans db\Jundee Pressure surveys Template.xls")
            OpXl = "yes"
        End If
        
        With Rst1
        
            LocationID = .Fields(1)
            
                Set Rst3 = CurrentDb.OpenRecordset("SELECT PrimaryLocationsTbl.LocationID, PrimaryLocationsTbl.Location FROM PrimaryLocationsTbl WHERE (((PrimaryLocationsTbl.LocationID)=" & LocationID & "));", dbOpenSnapshot)
            
                With Rst3
                    LocationName = .Fields(1)
                End With
                    
            
            Sheets("TemplateA").Select
            Sheets("TemplateA").Copy Before:=Sheets("Fan Curves")
            Sheets("TemplateA (2)").Name = LocationName
            Set wsheet = appExcel.Worksheets(LocationName)
          
                
                wsheet.Cells(5, 2) = .Fields(1)
                wsheet.Cells(22, 2) = .Fields(3)
                wsheet.Cells(23, 2) = .Fields(4)
                wsheet.Cells(24, 2) = .Fields(6)
                wsheet.Cells(33, 2) = .Fields(5)
                wsheet.Cells(18, 20) = .Fields(8)
                wsheet.Cells(19, 20) = .Fields(9)
                wsheet.Cells(15, 20) = .Fields(7) / 10
           
        
            
         Do While Not .EOF
            For cnt1 = 7 To 35 Step 4
                For cnt2 = 7 To 9 Step 1
                    
                    If .EOF = True Then
                    Exit For
                    End If
                    
                        wsheet.Cells(cnt2, cnt1) = .Fields(11)
                    
                    If .EOF = True Then
                    Exit For
                    End If
                        
                        wsheet.Cells(cnt2, cnt1 + 2) = .Fields(12)
                        .MoveNext
                Next
                
                If .EOF = True Then
                Exit For
                End If
                
            Next
            
                
        Loop
      End With
      
      
      
      
      
    End Sub
    Sub ViewFanAndLocationData()
    
    Set appExcel = Excel.Application
    appExcel.Visible = True
    Set wbook = appExcel.Workbooks("Jundee Pressure surveys Template.xls")
    Set wsheet = appExcel.Worksheets(LocationName)
        
    Set Rst2 = CurrentDb.OpenRecordset("SELECT FanIDTbl.FanIndexID, FanIDTbl.FanName, FanIDTbl.Type, PrimaryFanLocationsTbl.LocationID, PrimaryLocationsTbl.DuctDiameter, PrimaryLocationsTbl.TestPointElevation, PrimaryLocationsTbl.Location, PrimaryLocationsQry.MaxOfDateOfMove, CurrentConfigurationQry.MaxOfConfigurationDate, CurrentConfigurationQry.Solidity, CurrentConfigurationQry.NumberOfBlades, CurrentConfigurationQry.BladePitch, CurrentConfigurationQry.EvaseDiameter " & _
                                            " FROM (((FanIDTbl LEFT JOIN PrimaryLocationsQry ON FanIDTbl.FanIndexID = PrimaryLocationsQry.FanIndexID) " & _
                                                " LEFT JOIN PrimaryFanLocationsTbl ON (PrimaryLocationsQry.MaxOfDateOfMove = PrimaryFanLocationsTbl.DateOfMove) AND (PrimaryLocationsQry.FanIndexID = PrimaryFanLocationsTbl.FanIndexID)) " & _
                                                " LEFT JOIN PrimaryLocationsTbl ON PrimaryFanLocationsTbl.LocationID = PrimaryLocationsTbl.LocationID) " & _
                                                " LEFT JOIN CurrentConfigurationQry ON PrimaryLocationsQry.FanIndexID = CurrentConfigurationQry.FanIndexID " & _
                                            " WHERE PrimaryLocationsTbl.Location='" & LocationName & "' AND FanIDTbl.Current=Yes AND FanIDTbl.PrimaryFan=Yes;", dbOpenSnapshot)
    
    With Rst2
      
        Do While Not .EOF
            
        
                wsheet.Cells(6, 2) = .Fields(2)
                wsheet.Cells(8, 2) = .Fields(10)
                wsheet.Cells(9, 2) = .Fields(9)
                wsheet.Cells(2, 1) = .Fields(1)
                wsheet.Cells(10, 2) = .Fields(4)
                wsheet.Cells(14, 20) = .Fields(5)
      
        
        .MoveNext
       
        Loop
        
    .MoveFirst
        
    Set wsheet = appExcel.Worksheets("Fan Data")
        
        wsheet.Cells(6, NoCurves * 3 - 1) = .Fields(1)
        wsheet.Cells(12, NoCurves * 3 - 1) = LocationName
        wsheet.Cells(41, NoCurves * 3 - 1) = .Fields(1)
        
    End With
        
    End Sub
    Sub ViewOemData()
    Set appExcel = Excel.Application
    appExcel.Visible = True
    Set wbook = appExcel.Workbooks("Jundee Pressure surveys Template.xls")
    Set wsheet = appExcel.Worksheets(LocationName)
    cnt3 = 0
        P1 = wsheet.Cells(25, 20) * 1000
        Q1 = wsheet.Cells(15, 2)
    Set wsheet = appExcel.Worksheets("Fan Data")
        wsheet.Cells(8, 3 * NoCurves) = Q1
        wsheet.Cells(9, 3 * NoCurves) = P1
        
    With CurrentDb
        Set QdfOemData = .CreateQueryDef("", "SELECT CurrentPrimaryQry.Location, CurrentPrimaryQry.FanName, FanConfigurationTbl.ConfigurationDate, FanPerformanceTbl.Q, FanPerformanceTbl.StaticPressure " & _
                                        " FROM (CurrentPrimaryQry " & _
                                        " INNER JOIN FanConfigurationTbl ON (CurrentPrimaryQry.MaxOfConfigurationDate = FanConfigurationTbl.ConfigurationDate) " & _
                                        " AND (CurrentPrimaryQry.FanIndexID = FanConfigurationTbl.FanIndexID)) " & _
                                        " INNER JOIN FanPerformanceTbl ON FanConfigurationTbl.ConfigurationID = FanPerformanceTbl.ConfigurationID " & _
                                        " WHERE CurrentPrimaryQry.Location='" & LocationName & "' " & _
                                        " ORDER BY FanPerformanceTbl.Q;")
                                        
    End With
    With QdfOemData
        Set Rst4 = .OpenRecordset(dbOpenSnapshot)
             With Rst4
             
                 wsheet.Cells(6, 3 * NoCurves - 1) = .Fields(1)
                 wsheet.Cells(41, 3 * NoCurves - 1) = .Fields(1)
                 wsheet.Cells(12, 3 * NoCurves - 1) = .Fields(0)
                 
                 Do While Not .EOF
                 
                     wsheet.Cells(44 + cnt1, 3 * NoCurves - 1) = .Fields(3)
                     wsheet.Cells(44 + cnt1, 3 * NoCurves) = .Fields(4)
                     
                     .MoveNext
            
                 Loop
                 
             End With
             
    End With
            
    End Sub

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    x.x will be some number such as 1.2, although I am not familiar with Access97 so don't know what libraries are offered. Access97 might not have ADODB. I use ADODB for most of my recordset coding. Example:
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Set cn = CurrentProject.Connection
    Set rs = New ADODB.Recordset
    rs.Open "SELECT * FROM AllPCIAGE WHERE FAAID='" & strFAAID & "';", cn, adOpenStatic, adLockReadOnly
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    max is offline Novice
    Windows XP Access 97
    Join Date
    Sep 2011
    Posts
    17

    not resolved

    Hi after updating to office 2010 I have isolated the problem code but I still do not know why it does not work.

    The problem now appears to be that the recordset will not populate. I receive the error message 'run time error '1004' no current record.

    By removing the Where clause everything works OK except that i get alot of data in the recordset that I do not want

    Code:
     
    Set Rst4 = CurrentDb.OpenRecordset("SELECT CurrentPrimaryQry.Location, CurrentPrimaryQry.FanName, FanConfigurationTbl.ConfigurationDate, FanPerformanceTbl.Q, FanPerformanceTbl.StaticPressure " & _
    " FROM (CurrentPrimaryQry " & _
    " INNER JOIN FanConfigurationTbl ON (CurrentPrimaryQry.MaxOfConfigurationDate = FanConfigurationTbl.ConfigurationDate) " & _
    " AND (CurrentPrimaryQry.FanIndexID = FanConfigurationTbl.FanIndexID)) " & _
    " INNER JOIN FanPerformanceTbl ON FanConfigurationTbl.ConfigurationID = FanPerformanceTbl.ConfigurationID " & _
    " WHERE CurrentPrimaryQry.Location='" & LocationName & "' ORDER BY FanPerformanceTbl.Q;", dbOpenSnapshot)
     
    With Rst4
     
     
    wsheet.Cells(6, 3 * NoCurves - 1) = .Fields(1)
    wsheet.Cells(41, 3 * NoCurves - 1) = .Fields(1)
    wsheet.Cells(12, 3 * NoCurves - 1) = .Fields(0)
    All help appreciated

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You said the query as a saved Access object does work? Did you try having the code reference the saved query instead of building the SQL string? I can't see anything wrong with the SQL statement but you have identified the WHERE clause is issue. Did you step debug, is the LocationName variable still populated at this step? Doubt if I can be of any more help without being able to actually test code.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. VBA Crashes Access
    By ChuckColeman1812 in forum Programming
    Replies: 5
    Last Post: 12-12-2011, 03:33 AM
  2. Replies: 16
    Last Post: 07-20-2011, 08:01 AM
  3. Access crashes everytime I run applyfilter??
    By latestgood in forum Access
    Replies: 1
    Last Post: 06-22-2011, 12:25 PM
  4. Access 2007 crashes on the network
    By riit in forum Access
    Replies: 7
    Last Post: 11-26-2009, 08:37 AM
  5. Access Crashes When Wizards are Used
    By DCSwearingen in forum Access
    Replies: 6
    Last Post: 04-12-2009, 07:35 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