Re: SQL Fustrations

From: Brian Shafer (BrianShafer_at_discussions.microsoft.com)
Date: 06/11/04 

Date: Fri, 11 Jun 2004 07:14:02 -0700

Ok Jeff here you go...
First Definitions of the Access 2000 table
        Columns
        Name Type Size
        Grinder no Long Integer 4
        Start date/time Date/Time 8
        Rolling mill Text 10
        Roll type Text 10
        Roll material Text 10
        Roll Nb Text 50
        Roll code Text 50
        Program code Text 50
        Profile code Text 50
        Profile height Text 10
        Dress code Text 50
        Dress height Text 10
        Diam before head Double 8
        Diam before mid Double 8
        Diam before tail Double 8
        DiamSemiFin Long Integer 4
        End date/time Date/Time 8
        Regrind status Text 10
        Present diam max Double 8
        Present diam min Double 8
        Present diam head Double 8
        Present diam mid Double 8
        Present diam tail Double 8
        Taper Double 8
        Profile Double 8
        Roundness Double 8
        Runout Double 8
        Crack Integer 2
        C Position Single 4
        C Angle Byte 1
        Bruise Integer 2
        B Position Single 4
        B Angle Byte 1
        MaxStructure Integer 2
        MinStructure Integer 2
        Ultrasound Integer 2
        U Position Single 4
        U Angle Byte 1
        Wheel dia start Double 8
        Wheel dia end Double 8
        Roughness head Single 4
        Roughness mid Single 4
        Roughness tail Single 4
        RoughnessAverage Double 8
        RoughnessDeviation Double 8
        Hardness head Single 4
        Hardness mid Single 4
        Hardness tail Single 4
        Remarks Text 50
         Validate/Discard Text 1
        Operator code Text 20
        Wheel type Text 20
        Wheel no Text 20
        Last use mid dia Double 8
        IntTime Long Integer 4
        WeightRemoved Single 4
        LastGrind Date/Time 8
        SentToMC Yes/No 1
        U Depth Double 8
        U TableLength Double 8

Definitions of the Access 97 table...
        Columns
        Name Type Size
        Grinder no Text 2
        Start date/time Date/Time 8
        Rolling mill Text 1
        Roll type Text 1
        Roll material Text 2
        Roll Nb Text 9
        Roll code Text 10
        Program code Text 12
        Profile code Text 4
        Profile height Text 9
        Dress code Text 4
        Dress height Text 9
        Diam before mid Number (Double) 8
        End date/time Date/Time 8
        Regrind status Text 1
        Present diam max Number (Double) 8
        Present diam min Number (Double) 8
        Present diam head Number (Double) 8
        Present diam mid Number (Double) 8
        Present diam tail Number (Double) 8
        Taper Number (Single) 4
        Profile Number (Single) 4
        Roundness Number (Double) 8
        Runout Number (Double) 8
        Crack Number (Integer) 2
        C Position Number (Single) 4
        C Angle Number (Byte) 1
        Bruise Number (Integer) 2
        B Position Number (Single) 4
        B Angle Number (Byte) 1
        MaxStructure Number (Integer) 2
        MinStructure Number (Integer) 2
        Ultrasound Number (Integer) 2
        U Position Number (Single) 4
        U Angle Number (Byte) 1
        Wheel dia start Number (Double) 8
        Wheel dia end Number (Double) 8
        Roughness head Number (Single) 4
        Roughness mid Number (Single) 4
        Roughness tail Number (Single) 4
        Hardness head Number (Single) 4
        Hardness mid Number (Single) 4
        Hardness tail Number (Single) 4
        Remarks Text 50
        Validate/Discard Text 1
        Operator code Text 20
        Wheel type Text 20
        Wheel no Text 4
        Last use mid dia Number (Double) 8
        IntTime Number (Long) 4
        WeightRemoved Number (Single) 4
        LastGrind Date/Time 8
        damage_code Number (Integer) 2
        lubr_used_flag Yes/No 1
        data_request_time Date/Time 8
        data_accepted_time Date/Time 8

The follow contains the code from what I call basDBFunctions,
Option Explicit
Public Function Connect_G3DB() As Boolean
On Error GoTo Error_Connect_G3DB
    Dim strConnect As String
    Dim strLogEvent As String
    
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & g_strGrinder3Path & ";Mode=ReadWrite;"
    
    Set cnG3 = New Connection

    'Pass connection parameters to my new connection
    With cnG3
        .ConnectionString = strConnect
        .CursorLocation = adUseClient
        .Open
    End With

    Connect_G3DB = True
Exit_Connect_G3DB:

    Exit Function

Error_Connect_G3DB:
    ErrorCN cnRSC
    Connect_G3DB = False
    g_blncnError = True
    strLogEvent = "Error " & Err.Number & " (" & Err.Description & ") in procedure Connect_G3DB of Module basDBFunctions"
    App.LogEvent strLogEvent, vbLogEventTypeError
    frmMain.SendMessageBox strLogEvent
    Resume Exit_Connect_G3DB
    
End Function

Public Function Connect_RSCDB() As Boolean
On Error GoTo Error_Connect_RSCDB
    Dim strConnect As String
    Dim strLogEvent As String
    
    strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=user;" & _
                 "Data Source=" & g_strRSCPath & ";Mode=ReadWrite;" & _
                 "Persist Security Info=False;" & _
                 "Jet OLEDB:System database=" & g_strRSCSecPath & ";" & _
                 "Jet OLEDB:Database Locking Mode=1 "
    'I've tried a 0/1 here for the locking mode.. for testing
    Set cnRSC = New Connection

    'Pass connection parameters to my new connection
    With cnRSC
        .ConnectionString = strConnect
        .CursorLocation = adUseClient
        .Open
    End With

    Connect_RSCDB = True
Exit_Connect_RSCDB:

    Exit Function

Error_Connect_RSCDB:
    ErrorCN cnRSC
    Connect_RSCDB = False
    g_blncnError = True
' MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Connect_RSCDB of Module basDBFunctions"
    strLogEvent = "Error " & Err.Number & " (" & Err.Description & ") in procedure Connect_RSCDB of Module basDBFunctions"
    App.LogEvent strLogEvent, vbLogEventTypeError
    frmMain.SendMessageBox strLogEvent
    Resume Exit_Connect_RSCDB
    
End Function

Public Function ErrorCN(objcn As ADODB.Connection) As Boolean
    Dim i As Long
    For i = 0 To objcn.Properties.Count - 1
        Debug.Print i & vbTab & objcn.Properties.Item(i).Name & " = " & objcn.Properties.Item(i).Value
    Next i
End Function

Public Sub ErrorRS(rsSource As ADODB.Recordset)
    Dim i As Long
    For i = 0 To rsSource.Fields.Count - 1
        Debug.Print rsSource.Fields.Item(i).Name & "|" & Len(rsSource.Fields.Item(i).Value) & "|" & rsSource.Fields.Item(i).Value
    Next i
End Sub

Public Function disConnect_DB(objcn As ADODB.Connection) As Boolean

    If objcn.State = adStateOpen Then
        objcn.Close
        Set objcn = Nothing
    End If
    
End Function
Public Function disConnect_RS(objRS As ADODB.Recordset) As Boolean
On Error Resume Next
    If objRS.State = adStateOpen Then
        objRS.Close
        Set objRS = Nothing
    End If
    
End Function
Public Function Count_RSCHistoryGround(ByRef lngCount As Long) As Boolean
On Error GoTo Error_Count_RSCHistoryGround
    Dim strLogEvent As String
    Dim strSQL As String

    strSQL = "SELECT Count(*) AS CountOf "

    strSQL = strSQL & "FROM [History-Roll ground]"
    
    Set rs_TEMP = New Recordset
    rs_TEMP.Open strSQL, cnRSC, adOpenForwardOnly, adLockReadOnly
    
    Count_RSCHistoryGround = True
    lngCount = rs_TEMP.Fields!CountOf
    
    
Exit_Count_RSCHistoryGround:
    disConnect_RS rs_TEMP
    Exit Function

Error_Count_RSCHistoryGround:
    Count_RSCHistoryGround = False
    
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Count_RSCHistoryGround of Module basDBFunctions"
    Resume Exit_Count_RSCHistoryGround
End Function

Public Function Count_G3HistoryGround(ByRef lngCount As Long) As Boolean
On Error GoTo Error_Count_G3HistoryGround
    Dim strLogEvent As String
    Dim strSQL As String

    strSQL = "SELECT Count(*) AS CountOf "

    strSQL = strSQL & "FROM [History-Roll ground]"
    
    Set rs_TEMP = New Recordset
    rs_TEMP.Open strSQL, cnG3, adOpenForwardOnly, adLockReadOnly
    
    Count_G3HistoryGround = True
    lngCount = rs_TEMP.Fields!CountOf
    
    
Exit_Count_G3HistoryGround:
    disConnect_RS rs_TEMP
    Exit Function

Error_Count_G3HistoryGround:
    Count_G3HistoryGround = False
    
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Count_G3HistoryGround of Module basDBFunctions"
    Resume Exit_Count_G3HistoryGround
End Function

Public Function GetRecDataG3() As Boolean 'ByRef strRollType As String, ByRef strRollMat As String) As Boolean
On Error GoTo Error_GetRecData
    Dim strLogEvent As String
    Dim strSQL As String

    strSQL = "SELECT * "
    
    strSQL = strSQL & "From [History-Roll ground] "
    
    strSQL = strSQL & "WHERE ((([History-Roll ground].[Start date/time])=(SELECT MAX( " & _
            "[History-Roll Ground].[Start date/time]) FROM [History-Roll Ground])));"
    'Debug.Print "GetRecDataG3 = " & strSQL
    
    Set rs_G3Data = New Recordset
    rs_G3Data.Open strSQL, cnG3, adOpenForwardOnly, adLockReadOnly
    
    'strRollType = rs_G3Data.Fields("Roll type")
    'strRollMat = rs_G3Data.Fields("Roll material")
    
Exit_GetRecData:
    'disConnect_RS rs_G3Data
    Exit Function

Error_GetRecData:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetRecData of Module basDBFunctions"
    Resume Exit_GetRecData
End Function
Public Function GetRecDataRSC() As Boolean
On Error GoTo Error_GetRecDataRSC
    Dim strSQL As String
    Dim strLogEvent As String
    
    strSQL = "SELECT * "
    
    strSQL = strSQL & "From [History-Roll ground] "
    
    strSQL = strSQL & "WHERE ((([History-Roll ground].[Start date/time])=(SELECT MAX( " & _
            "[History-Roll Ground].[Start date/time]) FROM [History-Roll Ground])));"
    Debug.Print "GetRecDataRSC = " & strSQL
    
    Set rs_RSC = New Recordset
    rs_RSC.Open strSQL, cnRSC, adOpenDynamic, adLockOptimistic 'adOpenForwardOnly, adLockReadOnly
    
Exit_GetRecDataRSC:
    'disConnect_RS rs_RSC
    Exit Function

Error_GetRecDataRSC:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetRecDataRSC of Module basDBFunctions"
    Resume Exit_GetRecDataRSC
End Function

Public Function ConvertRollType(strG3RType As String) As String
On Error GoTo Error_ConvertRollType
    Dim strSQL As String
    Dim strLogEvent As String
    
    strSQL = "SELECT [Identification of Roll].[Roll type number], [Identification of Roll].[Roll init] " & _
             "From [Identification of Roll] " & _
             "WHERE ((([Identification of Roll].[Roll init])='" & strG3RType & "'))"

    Set rs_RSC = New Recordset
    rs_RSC.Open strSQL, cnRSC, adOpenForwardOnly, adLockReadOnly
    
    ConvertRollType = rs_RSC.Fields("Roll type number")

Exit_ConvertRollType:
    disConnect_RS rs_RSC
    Exit Function

Error_ConvertRollType:
    strLogEvent = "Error " & Err.Number & " (" & Err.Description & ") in procedure ConvertRollType of Module basDBFunctions"
    App.LogEvent strLogEvent, vbLogEventTypeError
    App.LogEvent strSQL, vbLogEventTypeError
    Resume Exit_ConvertRollType

End Function
Public Function ConvertRollMat(strG3RMat As String) As String
On Error GoTo Error_ConvertRollMat
    Dim strSQL As String
    Dim strLogEvent As String

    strSQL = "SELECT [Identification of Roll Material].[Roll material number], [Identification of Roll Material].[Roll material type] " & _
             "From [Identification of Roll Material] " & _
             "WHERE ((([Identification of Roll Material].[Roll material type])='" & strG3RMat & "'))"

    Set rs_TEMP = New Recordset
    rs_TEMP.Open strSQL, cnRSC, adOpenForwardOnly, adLockReadOnly
    
    ConvertRollMat = rs_TEMP.Fields("Roll material number")

Exit_ConvertRollMat:
    disConnect_RS rs_TEMP
    Exit Function

Error_ConvertRollMat:
    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ConvertRollMat of Module basDBFunctions"
    strLogEvent = "Error " & Err.Number & " (" & Err.Description & ") in procedure ConvertRollMat of Module basDBFunctions"
    App.LogEvent strLogEvent, vbLogEventTypeError
    App.LogEvent strSQL, vbLogEventTypeError
    Resume Exit_ConvertRollMat

End Function
Public Sub Create_tblHistoryRollGround(rsSource As ADODB.Recordset, rsDest As ADODB.Recordset) 'This is a temp table to be used between L3 and L2 for the Caster
On Error GoTo Error_Create_tblHistoryRollGround
    Dim strLogEvent As String
    
    Set rsDest = New Recordset
    With rsDest
        .Fields.Append "Grinder no", adBSTR
        .Fields.Append "Start date/time", adDate
        .Fields.Append "Rolling mill", adBSTR
        .Fields.Append "Roll type", adBSTR
        .Fields.Append "Roll material", adBSTR
        .Fields.Append "Roll Nb", adBSTR
        .Fields.Append "Roll code", adBSTR
        .Fields.Append "Program code", adBSTR
        .Fields.Append "Profile code", adBSTR
        .Fields.Append "Profile height", adBSTR
        .Fields.Append "Dress code", adBSTR
        .Fields.Append "Dress height", adBSTR
        .Fields.Append "Diam before mid", adDouble
        .Fields.Append "End date/time", adDate, , adFldIsNullable
        .Fields.Append "Regrind status", adBSTR, , adFldIsNullable
        .Fields.Append "Present diam max", adDouble
        .Fields.Append "Present diam min", adDouble
        .Fields.Append "Present diam head", adDouble
        .Fields.Append "Present diam mid", adDouble
        .Fields.Append "Present diam tail", adDouble
        .Fields.Append "Taper", adSingle
        .Fields.Append "Profile", adSingle
        .Fields.Append "Roundness", adDouble
        .Fields.Append "Runout", adDouble
        .Fields.Append "Crack", adSmallInt
        .Fields.Append "C Position", adSingle
        .Fields.Append "C Angle", adUnsignedTinyInt
        .Fields.Append "Bruise", adSmallInt
        .Fields.Append "B Position", adSingle
        .Fields.Append "B Angle", adUnsignedTinyInt
        .Fields.Append "MaxStructure", adSmallInt
        .Fields.Append "MinStructure", adSmallInt
        .Fields.Append "Ultrasound", adSmallInt
        .Fields.Append "U Position", adSingle
        .Fields.Append "U Angle", adUnsignedTinyInt
        .Fields.Append "Wheel dia start", adDouble, , adFldIsNullable
        .Fields.Append "Wheel dia end", adDouble, , adFldIsNullable
        .Fields.Append "Roughness head", adSingle
        .Fields.Append "Roughness mid", adSingle
        .Fields.Append "Roughness tail", adSingle
        .Fields.Append "Hardness head", adSingle
        .Fields.Append "Hardness mid", adSingle
        .Fields.Append "Hardness tail", adSingle
        .Fields.Append "Remarks", adBSTR, , adFldIsNullable
        .Fields.Append "Validate/Discard", adBSTR
        .Fields.Append "Operator code", adBSTR
        .Fields.Append "Wheel type", adBSTR
        .Fields.Append "Wheel no", adBSTR
        .Fields.Append "Last use mid dia", adDouble
        .Fields.Append "IntTime", adInteger
        .Fields.Append "WeightRemoved", adSingle
        '.Fields.Append "LastGrind", adDate, , adFldIsNullable
        .Open
    End With
    
    If rsSource.RecordCount > 0 Then
        rsSource.MoveFirst
    End If

    Do While Not rsSource.EOF
        ' Add a new record
        rsDest.AddNew
        With rsDest
            .Fields("Grinder no").Value = "3" 'rsSource![Grinder no].Value
            .Fields("Start date/time").Value = rsSource![Start date/time].Value
            .Fields("Rolling mill").Value = rsSource![Rolling mill].Value
            .Fields("Roll type").Value = IIf(ConvertRollType(rsSource![Roll type].Value) = "", "NA", ConvertRollType(rsSource![Roll type].Value))
            .Fields("Roll material").Value = IIf(ConvertRollMat(rsSource![Roll material].Value) = "", "NA", ConvertRollMat(rsSource![Roll material].Value))
            .Fields("Roll Nb").Value = rsSource![Roll Nb].Value
            .Fields("Roll code").Value = rsSource![Roll code].Value
            .Fields("Program code").Value = rsSource![Program code].Value
            .Fields("Profile code").Value = rsSource![Profile code].Value
            .Fields("Profile height").Value = rsSource![Profile height].Value
            .Fields("Dress code").Value = rsSource![Dress code].Value
            .Fields("Dress height").Value = rsSource![Dress height].Value
            .Fields("Diam before mid").Value = rsSource![Diam before mid].Value
            .Fields("End date/time").Value = IIf(IsNull(rsSource![End date/time].Value), #6/24/1994 8:00:00 AM#, rsSource![End date/time].Value)
            .Fields("Regrind status").Value = IIf(IsNull(rsSource![Regrind status].Value), 0, rsSource![Regrind status].Value)
            .Fields("Present diam max").Value = rsSource![Present diam max].Value
            .Fields("Present diam min").Value = rsSource![Present diam min].Value
            .Fields("Present diam head").Value = rsSource![Present diam head].Value
            .Fields("Present diam mid").Value = rsSource![Present diam mid].Value
            .Fields("Present diam tail").Value = rsSource![Present diam tail].Value
            .Fields("Taper").Value = rsSource![Taper].Value
            .Fields("Profile").Value = rsSource![Profile].Value
            .Fields("Roundness").Value = rsSource![Roundness].Value
            .Fields("Runout").Value = rsSource![Runout].Value
            .Fields("Crack").Value = rsSource![Crack].Value
            .Fields("C Position").Value = rsSource![C Position].Value
            .Fields("C Angle").Value = rsSource![C Angle].Value
            .Fields("Bruise").Value = rsSource![Bruise].Value
            .Fields("B Position").Value = rsSource![B Position].Value
            .Fields("B Angle").Value = rsSource![B Angle].Value
            .Fields("MaxStructure").Value = rsSource![MaxStructure].Value
            .Fields("MinStructure").Value = rsSource![MinStructure].Value
            .Fields("Ultrasound").Value = rsSource![Ultrasound].Value
            .Fields("U Position").Value = rsSource![U Position].Value
            .Fields("U Angle").Value = rsSource![U Angle].Value
            .Fields("Wheel dia start").Value = IIf(IsNull(rsSource![Wheel dia start].Value), 0, rsSource![Wheel dia start].Value)
            .Fields("Wheel dia end").Value = IIf(IsNull(rsSource![Wheel dia end].Value), 0, rsSource![Wheel dia end].Value)
            .Fields("Roughness head").Value = rsSource![Roughness head].Value
            .Fields("Roughness mid").Value = rsSource![Roughness mid].Value
            .Fields("Roughness tail").Value = rsSource![Roughness tail].Value
            .Fields("Hardness head").Value = rsSource![Hardness head].Value
            .Fields("Hardness mid").Value = rsSource![Hardness mid].Value
            .Fields("Hardness tail").Value = rsSource![Hardness tail].Value
            .Fields("Remarks").Value = IIf(IsNull(rsSource![Remarks].Value), " ", rsSource![Remarks].Value)
            .Fields("Validate/Discard").Value = rsSource![Validate/Discard].Value
            .Fields("Operator code").Value = rsSource![Operator code].Value
            .Fields("Wheel type").Value = rsSource![Wheel type].Value
            .Fields("Wheel no").Value = rsSource![Wheel no].Value
            .Fields("Last use mid dia").Value = rsSource![Last use mid dia].Value
            .Fields("IntTime").Value = rsSource![IntTime].Value
            .Fields("WeightRemoved").Value = rsSource![WeightRemoved].Value
            '.Fields("LastGrind").Value = rsSource![LastGrind].Value
        End With
        ' Update Recordset
        rsDest.Update
        ' Move to the next record in the dataset
        rsSource.MoveNext
    Loop
    
Exit_Create_Create_tblHistoryRollGround:
    disConnect_RS rsSource
    Exit Sub

Error_Create_tblHistoryRollGround:
    strLogEvent = "Error " & Err.Number & " (" & Err.Description & ") in procedure Create_tblHistoryRollGround of Module basDBFunctions"
    App.LogEvent strLogEvent, vbLogEventTypeError
    'App.LogEvent strSQL, vbLogEventTypeError
    Resume Next
        
End Sub
Public Sub testme(rsSource As ADODB.Recordset)
    Dim i As Long
    
    For i = 0 To rsSource.Fields.Count - 1
        Debug.Print rsSource.Fields.Item(i).Name & "|" & Len(rsSource.Fields.Item(i).Value) & "|" & rsSource.Fields.Item(i).Value
    Next i
    
        
    
End Sub
Public Function InsertIntoRSC(rsSource As ADODB.Recordset, Optional ByRef rs_Key As String) As Boolean
On Error GoTo Error_InsertIntoRSC
    Dim strSQL As String
    Dim strLogEvent As String

    strSQL = "INSERT INTO [History-Roll ground] " & _
             "([Grinder no], [Start date/time], [Rolling mill], " & _
             "[Roll type], [Roll material], [Roll Nb], " & _
             "[Roll code], [Program code], [Profile code], " & _
             "[Profile height], [Dress code], [Dress height], " & _
             "[Diam before mid], [End date/time], [Regrind status], " & _
             "[Present diam max], [Present diam min], [Present diam head], " & _
             "[Present diam mid], [Present diam tail], Taper, " & _
             "Profile, Roundness, Runout, " & _
             "Crack, [C Position], [C Angle], Bruise, " & _
             "[B Position], [B Angle], MaxStructure, MinStructure, " & _
             "Ultrasound, [U Position], [U Angle], [Wheel dia start], " & _
             "[Wheel dia end], [Roughness head], " & _
             "[Roughness mid], [Roughness tail], [Hardness head], " & _
             "[Hardness mid], [Hardness tail], Remarks, " & _
             "[Validate/Discard], [Operator code], [Wheel type], " & _
             "[Wheel no], [Last use mid dia], IntTime, " & _
             "WeightRemoved) "
    With rsSource
    rs_Key = .Fields("Start date/time")
    Debug.Print rs_Key
    strSQL = strSQL & " VALUES( " & _
             "'3'" & ", #" & .Fields("Start date/time") & "#, '" & .Fields("Rolling mill") & "', '" & _
             .Fields("Roll type") & "', '" & .Fields("Roll material") & "', '" & .Fields("Roll Nb") & "', '" & _
             .Fields("Roll code") & "', '" & .Fields("Program code") & "', '" & .Fields("Profile code") & "', '" & _
             .Fields("Profile height") & "', '" & .Fields("Dress code") & "', '" & .Fields("Dress height") & "', '" & _
             .Fields("Diam before mid") & "', #" & .Fields("End date/time") & "#, '" & .Fields("Regrind status") & "', " & _
             .Fields("Present diam max") & ", " & .Fields("Present diam min") & ", " & .Fields("Present diam head") & ", " & _
             .Fields("Present diam mid") & ", " & .Fields("Present diam tail") & ", " & .Fields("Taper") & ", " & _
             .Fields("Profile") & ", " & .Fields("Roundness") & ", " & .Fields("Runout") & ", " & _
             .Fields("Crack") & ", " & .Fields("C Position") & ", " & .Fields("C Angle") & ", " & .Fields("Bruise") & ", " & _
             .Fields("B Position") & ", " & .Fields("B Angle") & ", " & .Fields("MaxStructure") & ", " & .Fields("MinStructure") & ", " & _
             .Fields("Ultrasound") & ", " & .Fields("U Position") & ", " & .Fields("U Angle") & ", " & .Fields("Wheel dia start") & ", " & _
             .Fields("Wheel dia end") & ", " & .Fields("Roughness head") & ", " & _
             .Fields("Roughness mid") & ", " & .Fields("Roughness tail") & ", " & .Fields("Hardness head") & ", " & _
             .Fields("Hardness mid") & ", " & .Fields("Hardness tail") & ", '" & .Fields("Remarks") & "', '" & _
             .Fields("Validate/Discard") & "', '" & .Fields("Operator code") & "', '" & .Fields("Wheel type") & "', '" & _
             .Fields("Wheel no") & "', " & .Fields("Last use mid dia") & ", " & .Fields("IntTime") & ", " & _
             .Fields("WeightRemoved") & ")"
    Debug.Print strSQL
    
    Set rs_TEMP = New Recordset
    'cnRSC.BeginTrans
    rs_TEMP.Open strSQL, cnRSC, adOpenStatic, adLockOptimistic
    'cnRSC.CommitTrans
    'rs_Key = .Fields("Start date/time")
    End With
    ErrorRS rsSource
    InsertIntoRSC = True

Exit_InsertIntoRSC:
    'disConnect_RS rs_TEMP
    disConnect_RS rsSource
    Wait (5)
    Exit Function

Error_InsertIntoRSC:
    InsertIntoRSC = False
    If Err.Number = -2147217833 Then
    testme rsSource
    End If

    'MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertIntoRSC of Module basDBFunctions"
    strLogEvent = "Error " & Err.Number & " (" & Err.Description & ") in procedure InsertIntoRSC of Module basDBFunctions"
    App.LogEvent strLogEvent, vbLogEventTypeError
    App.LogEvent strSQL, vbLogEventTypeError
    Debug.Print strLogEvent
    Debug.Print strSQL
    Wait (5)
        ErrorRS rs_TEMP
    Resume Exit_InsertIntoRSC

End Function

Public Function DelG3RG() As Boolean
    Dim strSQL As String
    Dim strLogEvent As String

    strSQL = "DELETE [History-Roll ground].*, [History-Roll ground].[Start date/time] " & _
             "From [History-Roll ground] " & _
             "WHERE ((([History-Roll ground].[Start date/time])= " & _
             "(SELECT MAX( [History-Roll Ground].[Start date/time]) FROM [History-Roll Ground])))"

    
    Set rs_TEMP = New Recordset
    'cnG3.BeginTrans
    rs_TEMP.Open strSQL, cnG3, adOpenDynamic, adLockOptimistic
    'cnG3.CommitTrans
    
End Function

Public Function CopyG3RG() As Boolean
    Dim strSQL As String
    Dim strLogEvent As String

   On Error GoTo Error_CopyG3RG

    strSQL = "INSERT INTO [History-Roll ground Backup] " & _
             "SELECT [History-Roll ground].* From [History-Roll ground] " & _
             "WHERE ((([History-Roll ground].[Start date/time])= " & _
             "(SELECT MAX( [History-Roll Ground].[Start date/time]) FROM [History-Roll Ground])))"
    
    Set rs_TEMP = New Recordset
    'cnG3.BeginTrans
    rs_TEMP.Open strSQL, cnG3, adOpenDynamic, adLockOptimistic
    'cnG3.CommitTrans

Exit_CopyG3RG:

    Exit Function

Error_CopyG3RG:
        If Err.Number = -2147217833 Then
    'testme rssource
    End If
    ErrorRS rs_TEMP
    strLogEvent = "Error " & Err.Number & " (" & Err.Description & ") in procedure CopyG3RG of Module basDBFunctions"
    App.LogEvent strLogEvent, vbLogEventTypeError
    App.LogEvent strSQL, vbLogEventTypeError
    Debug.Print strLogEvent
    Debug.Print strSQL
    Resume Exit_CopyG3RG
End Function

contined in next post........................

"Jeff Johnson [MVP: VB]" wrote:

>
> "Brian Shafer" <BrianShafer@discussions.microsoft.com> wrote in message
> news:18B576FD-688B-496E-8919-288F325385D2@microsoft.com...
>
> > A mixture of number and text... this one produces an error in vb, but not
> access....
> > INSERT INTO [History-Roll ground] ([Grinder no], [Start date/time],
> [Rolling mill], [Roll type], [Roll material], [Roll Nb], [Roll code],
> [Program code], [Profile code], [Profile height], [Dress code], [Dress
> height], [Diam before mid], [End date/time], [Regrind status], [Present diam
> max], [Present diam min], [Present diam head], [Present diam mid], [Present
> diam tail], Taper, Profile, Roundness, Runout, Crack, [C Position], [C
> Angle], Bruise, [B Position], [B Angle], MaxStructure, MinStructure,
> Ultrasound, [U Position], [U Angle], [Wheel dia start], [Wheel dia end],
> [Roughness head], [Roughness mid], [Roughness tail], [Hardness head],
> [Hardness mid], [Hardness tail], Remarks, [Validate/Discard], [Operator
> code], [Wheel type], [Wheel no], [Last use mid dia], IntTime, WeightRemoved)
> VALUES( '3', #6/5/2004 19:07:35#, '1', '1', 'NA', '10418', 'F1-F3', '1122
> F1-3', '2001', '-0.00039', 'test', '0', '30.31012', #6/6/2004 00:09:25#,
> '3', 30.29838, 30.29739, 30.29746, 30.29782, 30.29838, 0.00034, 0.00097,
> 0.00014,
> > 0.00022, 8, 21.65346, 1, 16, 21.65346, 1, 0, 0, 0, 0, 0, 26.2767,
> 25.95931, 0, 0, 0, 0, 0, 0, 'NA', 'V', 'operator', 'National41C3622M7-BD',
> 1, 0, 6249, 11.57736)
>
> I'll try hard not to make a comment like "shoot whoever chose field names
> with spaces" (oops, I just made one!) and ask this: is [Diam before mid]
> numeric? Because you have the value in apostrophes in the VALUES clause
> ('30.31012'). I doubt that would cause the error you are getting, but it's
> worth mentioning.
>
> Beyond that we REALLY NEED TO SEE THE CODE, not just the SQL statement.
> Given that the task is already past due, you'd be doing yourself a favor to
> give us all the info we need so that it doesn't drag out further as we ask
> you to supply more.
>
>
>

Loading