Full Version: Dim Statements For Numbers W/ Sql Server
UtterAccess Forums > Microsoft® Access > Access Forms
schreinman
My database keeps crashing with code that has previously worked, so I'm trying to clean up potential problem areas.
On this endeavor I have the following question.
Odimension (DIM) many of my numbers as "Long" whereas the equivalent in SQL Server is "Int". Does this matter? Should I change all of my code from Long to Int, along with other similar datatype changes?
Thanks
Doug Steele
Int in SQL Server is 32 bit, the same as Long in VBA. However, even if there was an issue, it shouldn't cause crashes: it should simply cause overflow errors when you try to store too large a value in the variable.
schreinman
What's the better practice: to Dim as Int or Dim as Long?
My "crashes" are actually errors occuring that didn't occur before. The latest is "Invalid Operation" (3219) when I try to duplicate records in a table with .addnew:
CODE
Private Sub DuplicateRecord_Click()
On Error GoTo Err_Handler
    Dim dblQty As Double
    Dim dblNewQty As Double
    Dim strPartNo As String
    Dim strMsg As String
    Dim Response
    Dim intCount As Integer
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    'Make sure there is a record to duplicate
    If Me.NewRecord Then
        strMsg = "Select the record to duplicate."
        MsgBox strMsg, 0, "PARTS Database Message"
    Else
        'Capture the Qty and PartNo values
        dblQty = Me.Qty.Value
        strPartNo = Me.PartNo.Value
        If dblQty < 2 Then
            strMsg = "The quantity has to be greater than 1" _
                & Chr(13) & "before you can duplicate a record." _
                & Chr(13) & Chr(13) & "Increase the Qty in the form and" _
                & Chr(13) & "then try again."
            MsgBox strMsg, 0, "PARTS Database Message"
        ElseIf dblQty = 2 Then
            Me.Qty.Value = 1
            dblNewQty = 1
            intCount = dblQty - 1
        ElseIf dblQty > 2 Then
            strMsg = "If you want to create " & dblQty - 1 & " duplicates for a total of " & dblQty & " records, click 'Yes'." _
                    & Chr(13) & "Otherwise click 'No' to create 1 duplicate with the same Qty of " & dblQty & "."
            Response = MsgBox(strMsg, 3, "Create Multiple Duplicates (Y/N)?")
            If Response = vbNo Then
                dblNewQty = Me.Qty.Value
                intCount = 1
            ElseIf Response = vbYes Then
                Me.Qty.Value = 1
                dblNewQty = 1
                intCount = dblQty - 1
            End If
        End If
    End If
        
    If intCount > 0 Then
        'Clear the Received check so record isn't discarded in the requery below
        If Me.Received_Tag = True Then
            Me.Received_Tag = False
            Me.Received_TagCheck = Null
        End If
        
        'Save any edits before running SQL
        If Me.Dirty Then Me.Dirty = False
        Do While intCount > 0
        
            'Duplicate the record: add to form's clone
            With Me.RecordsetClone
                .AddNew                                                    '<<<<<<<<<<<<<<< PROBLEM HERE
                    !ProjectTitle = Me.ProjectTitle.Value
                    !Site = Me.Site.Value
                    !ReqID = Me.ReqID.Value
                    !PartNo = Me.PartNo.Value
                    !Qty = dblNewQty
                    !Price = Me.Price.Value
                    !AssetNotes = Me.AssetNotes.Value
                .Update
                
                If Me.Dirty Then Me.Dirty = False
                
                intCount = intCount - 1
            End With
        Loop
        DoCmd.Requery
        Me.Recordset.FindFirst "[PartNo] = " & "'" & strPartNo & "'"
    End If
    
    rs.Close
    Set rs = Nothing
Exit_Here:
    Exit Sub
Err_Handler:
    MsgBox Err.Description & " [" & Err.Number & "]"
    Resume Exit_Here
End Sub
datAdrenaline
Long ...
generally use Long for almost every circumstance that requires whole numbers. I do this because VBA is optimized to work with 32 bit values. I do use the other datatypes as well, but Long is definately dominant.
---
With respect to your code, you have intCount being assigned a value with dblQuantity - 1, with intCount being an Integer this line can easily create an overflow. As a matter of fact, an overflow can occur with intCount being declared as a Long too, but less likely, unless the value of dblQuantity is REALLY HUGE!!
Roger_Carlson
There's almost no reason to use the Integer data type in code.
schreinman
Thanks for the feedback. I will stick with Long and update other numbers that should be integers and only as large as necessary.
o to reiterate the advice: I should stick with MS Access Dim types and NOT use the SQL Server equivalents; correct?
Doug Steele
Getting an invalid operation when you try to duplicate a record makes sense...and that should have nothing to do with whether you've declared the variables as Int or Long. In my opinion, the best practice is to declare them as the appropriate data type for their usage!
datAdrenaline
>> With Me.RecordsetClone
.AddNew '<<<<<<<<<<<<<<< PROBLEM HERE <<

Can you add new records through the Form -- with out VBA?
datAdrenaline
>> I should stick with MS Access Dim types and NOT use the SQL Server equivalents; correct? <<
/i>What do you mean by this question?
HAs Doug says, declare according the value you are storing in that variable -- I personally use Long a lot, even to hold data that will NEVER consume the range I will be working with. My advice was no meant to confuse you --- Dougs advice is really more appropriate.

Plus ...
VBA's variable datatype of Long IS THE EQUIVALENT of SQL Server's int ... so ... that IS the equivalent. Please don't base equivalency on similar identifiers. SQL Server and VBA are two separate environments and the identifiers in each, while possibly the same or similar, does NOT mean the identifiers represent the same thing.
Cases in point ...
int in SQL Server is a 4 byte signed whole number
Long in VBA is a 4 byte signed whole number
smallint in SQL Server is a 2 byte signed whole number
Integer in VBA is a 2 byte signed whole number
money in SQL Server is an 8 byte signed fixed point number (4 decimal places)
Currency in VBA is an 8 byte signed fixed point number (4 decimal places)
schreinman
I'm sorry for not quite understanding your question. My form does not have an add new record option (see attached). The Duplicate button worked a week or so ago but has since stopped working. I have been making changes to the tables on the SQL server and have paid careful attention to the following:
All tables have a timestamp type field
All bit fields have a default value and do not allow nulls
Ocan duplicate records in the associated table with cut and paste without error.
Sure appreciate your help with this!
datAdrenaline
>> My form does not have an add new record option (see attached). The Duplicate button worked a week or so ago but has since stopped working. I have been making changes to the tables on the SQL server <<
Is this an ADP project file or an MDB/ACCDB database file?
- What is the record source of your Form?
- What is the setting of the Allow Additions of your Form?
- Do you want people to be able to Add records through the Form, outside of your VBA?
schreinman
Please allow me to follow up with you later, if this problem persists...
any thanks!
datAdrenaline
No problem ... UA is open 24/
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.