Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Layout (A2K)

    Good Monday Morning Everyone!

    I have a table that is imported in. Each field that imports is text. 11 of the 31 fields contains a character on the right that is hex. The hex character needs to be converted to it's respective equivalent value then these 11 fields needs to be converted from TEXT to CURRENCY and place the converted values in a new column.

    I'm attaching code that works fine. I know it's poorly written and was hoping someone could provide suggestions on how I can modify this code to work more effeciently.

    Again, the code works, but should be and can be written better.

    Thanks,
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Layout (A2K)

    Good Afternoon!

    A more compact (and hence also less readable) version:
    <code>
    Private Sub cmdOutput_Click()
    Dim cn As ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim SQL_STR As String
    Const C = ","
    Const Q = "'"
    Dim i As Integer

    Set cn = CurrentProject.Connection
    rs.CursorLocation = adUseClient ' where the processing begins
    rs.Open "tPDE", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect

    With rs
    .MoveFirst
    Do Until .EOF
    ' First fields to include
    SQL_STR = C & C & Q & .Fields(2).Value & Q
    ' Concatenate the pairs
    For i = 4 To 24 Step 2
    SQL_STR = SQL_STR & C & Q & .Fields(i - 1).Value & Q
    SQL_STR = SQL_STR & C & FilterNumber(.Fields(i - 1).Value)
    Next i
    ' And the last single ones
    For i = 26 To 31
    SQL_STR = SQL_STR & C & Q & .Fields(i - 1).Value & Q
    Next i
    ' Assemble SQL command
    SQL_STR = "INSERT INTO tbl_Output VALUES (" & SQL_STR & ")"
    ' Execute it
    cn.Execute SQL_STR, , adCmdText
    .MoveNext
    Loop
    .Close
    End With
    Set rs = Nothing
    Set cn = Nothing
    End Sub
    </code>
    The downside of such code is that it is more work to modify it if the table structure changes.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Layout (A2K)

    BTW is it correct that .Fields(4), .Fields(6), ..., .Fields(24) aren't used?

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Layout (A2K)

    Thanks Hans,

    By your response, I assume it's probably best to leave it the way it is? Is that correct?

    Per your question about fields .4, .6 and .24. That's correct.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Layout (A2K)

    There are a few points from my version that you could incorporate while leaving the general structure as you had it:

    - In addition to your <code>C = ","</code>, I used <code>Q = "'"</code>, and I defined both as constants.
    - Instead of the two lines
    <code>
    Dim rs As ADODB.Recordset
    Set rs As New ADODB.Recordset
    </code>
    I used the equivalent single line
    <code>
    Dim rs As New ADODB.Recordset
    </code>
    - Instead of opening a recordset on "SELECT * FROM ...", I opened the table directly in
    <code>
    rs.Open "tPDE", cn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    </code>
    - Instead of using a second recordset, I used the Execute method of the connection object (you didn't really use the recordset IIS anyway)
    <code>
    cn.Execute SQL_STR, , adCmdText</code>

Posting Permissions

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