Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1

    Unanswered: WHY DOESN'T ACCESS HAVE THIS?!?!11?! Code Exchange!

    The forums have been a-buzzin' with opinions on when it is and isn't a good idea to provide finished vba solutions for questions here. Well today I'm breaking all the rules! I thumb my nose in your general direction!!

    Most of us have a little nestegg of utilities that for some reason we've had to make for ourselves. I thought it might be kinda fun to share one of your favorite utilities just for the hell of it. It's always fun to see how other people tackle stuff and I invariably learn some new tricks even if I already have code that does the same thing.

    Basic groundrules:
    • Provide a usage disclaimer or just leave it blank.
    • Please comment the code so we know what it does. Finished code that "just works" is awesome, but it's even more awesome if we can see the thought process behind it.


    Ok, I wrote this little bit of code due to a slew of reports I needed to generate grouped by and ordered by week starting on various days of the week. I used this function to pull a common date to attach to all records falling within the same week:

    Code:
    Public Function DateByWeek(week As Integer, year As Integer, Optional day As Integer = 1) As Date
    Dim return_date As Date
    Dim first_day As Date
    Dim return_day As Integer
    Dim d As Date
    Dim i As Integer
    
    'Originally written by Teddy (teddy@jorun.net) for
    'public use on dbforums.com
    'This code is free to use in part or entirety
    'provide these comments are left in-tact
    
    'Returns date by week # for a given year.
    'Optional "day" parameter changes start of week, default is sunday.
    
    'Sets defaults for use in "dateadd()" function
    return_day = WeekDay("1/1/" & year, day)
    first_day = "1/1/" & year
    
    'Sets a different "start date" as seed for all weeks in the year
    If return_day <> 1 Then
        first_day = DateAdd("d", 8 - return_day, first_day)
    End If
    
    return_date = DateAdd("ww", week - 1, first_day)
    
    DateByWeek = return_date
    
    End Function
    Last edited by Teddy; 12-29-04 at 13:41.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    good idea, but are you sure this forum is the place to do it?

    the search here is somewhere between random and crap
    there doesn't seem to be a FAQ possibility
    there is no obvious moderator
    there is no "codebank" where decently categorised utilities could be reused
    there is no indexing/categorisation other then "Access" which is absurdly large for a codebank but very good for the question/answer style cos you don't have to hop from one subforum to another.

    ...it's a shame really - the same questions come up again and again and again: it would be neat to have a look-here-first department that addressed the all-too-common stuff.

    know anyone who runs this place???

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    I know it will be fleeting. I wouldn't expect this thread to stick around for long, but for those who contribute they get to tinker with other peoples code. If we got 5 cool code chunks that would be rad.

    If you want something more permanent, it's a trivial thing to host the code on someone else's webspace and reference it. I just thought this thread might be fun. if we get a lot of good stuff I'll throw it up on a site and let it hang out there.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by izyrider
    good idea, but are you sure this forum is the place to do it?

    the search here is somewhere between random and crap
    there doesn't seem to be a FAQ possibility
    there is no obvious moderator
    there is no "codebank" where decently categorised utilities could be reused
    there is no indexing/categorisation other then "Access" which is absurdly large for a codebank but very good for the question/answer style cos you don't have to hop from one subforum to another.

    ...it's a shame really - the same questions come up again and again and again: it would be neat to have a look-here-first department that addressed the all-too-common stuff.

    know anyone who runs this place???

    izy
    Well it's not "TheComputerGuy" ... I've not yet seen big brother slap anyone around ... Maybe they've left the store unattended ...
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Nov 2002
    Posts
    154
    If anyone wants my sketchy stuff I'll freely share it :P. I'm not sure what of the modules that I've made for myself that anyone would want anyways. Can you post non-coded solutions to this string? :P

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Yeah anything... the idea is just a code exchange, not a persistant library for usual questions.

    If you have something that was kinda neat and you're willing to share, throw it up here.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Dec 2004
    Location
    Sunshine Coast, Australia
    Posts
    52
    if anybody wants there code hosted online send it to me and I will upload to my website.

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Hey Teddy,

    I also have wondered about the non-existence of such a place in this forum and like yourself, I have no idea who runs this joint anyway. HOWEVER, since you are a well respected guy on here among many others, take a look at this other forum that I belong to and it is setup the way you have suggested. Maybe you or someone else can get that built into here someday once we figure out who the big boss is..... the site is for Access VBA and is found here:

    http://www.accessvba.com/

    Our site here is big and robust and should have your suggestions included...especially for the searching and Look Here First part as so many of the same exact questions are posted repeatedly and almost back to back. This is a good idea you have and high five to you......Also one other feature is a link you click on to give you a Printable Version of the text you're reading in the forum without all the Images clutter, just in plain text. I like that because I print some of the postings and that just makes it a bit easier.

    have a nice one,
    BUD
    Last edited by Bud; 12-30-04 at 05:47. Reason: added a line

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    keepalive

    following some great insight from "lansing", i was able to improve my standard user-date-input form 1000%

    the game is to use one single calendar control throughout an application for all user date inputs. lansing's insight was a simple method to capture the text-box object that called for the date input (this was a HUGE improvement on my original clumsy calling routine)

    (sorry Teddy if it is not as fully commented as you suggested, but it is SO SO simple it is almost beautiful)

    izy
    Last edited by izyrider; 01-03-05 at 14:04. Reason: one pointless line of code removed
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    well Teddy - this is my last attempt at a keepalive for your post.

    ...a primitive approach to finding the shortest route in a map

    izy


    it's a DEMO: there is ZERO error checking - if you dont have something in the problem location cbo and in the problem type options you will surely crash
    Last edited by izyrider; 01-21-05 at 13:53. Reason: another version
    currently using SS 2008R2

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Given that there is a general sense of optimism about the place I thought I would resurrect this thread.

    I thought the below was a right white elephant when I wrote it but had need of it again today and thought I'd post in case it helps anyone else out. It basically creates a very simple DDL statement for an existing table (no indexes, constrainst, rels etc) in Jet or T SQL that you can execute in Access or SQL Server to create a table.

    It is far from foolproof or comprehensive but has worked for me a couple of times now. Feel free to adapt or dismiss as you like.

    BTW - Izy - some of your comments look ripe for review

    Would some of the more experienced members (Mike, Teddy, Izy, Healdem, Hambakka etc.) be interested in contributing to an FAQ and a Guide-to-
    posting-like-in-the-SQL-Server-forum sticky? Perhaps something for when the archives are reopened....

    Code:
    Option Compare Database
    Option Explicit
    Public Enum eSQL_Syntax
        
        Access_SQL = 0
        T_SQL = 1
        
    End Enum
    'Script a simple Create Table statement based on an existing table
    Public Function fDeriveCreateTableSQL(adoRst As ADODB.Recordset, TName As String, SQL_Syntax As eSQL_Syntax) As String
        Dim strSQL As String
        Dim adoFld As ADODB.Field
        Dim i As Integer
        Dim j As Integer
        Dim sDataType As String
        
    'start it off...
        strSQL = "CREATE TABLE [" & TName & "] ("
        
    'get the length of the string (in bytes - not necessary but it is more efficient. Only really matters in loops though...)
        i = LenB(strSQL)
        
    'loop through the fields
        For Each adoFld In adoRst.Fields
            
            With adoFld
    'Call the function that returns the string datatype to concatenate into the DDL statement
                sDataType = fConvertADODataTypeToAccess(.Type, T_SQL)
                
    'Check it didn't go wrong
                If Not sDataType = "error" Then
    'Get rid of spaces in column names (my own bug bear - optional) and concatentate the datatype string
                    strSQL = strSQL & Replace(.Name, " ", "_") & " " & sDataType
                    
    'If the data type is one of these, you need the size\ scale
                    If sDataType = "Text" Or sDataType = "Char" Or sDataType = "VarChar" Or sDataType = "Numeric" Or sDataType = "NVarChar" Then
                    
                        strSQL = strSQL & "(" & .DefinedSize & ")"
                        
                    End If
                    
    'um... stick a comma on the end
                    strSQL = strSQL & ", "
                
                Else
                    
    'uh oh
                    Debug.Print "fConvertADODataTypeToAccess could not convert this ado data type: " & .Type
                End If
            
            End With
    'Loop
        Next adoFld
        
    'get the final legnth of the string
        j = LenB(strSQL)
    'Check there has been at least one field added....
        If i = j Then
            
            strSQL = "Error"
        
        Else
            
    'Get rid of final comma
            strSQL = Mid(strSQL, 1, Len(strSQL) - 2) & ")"
            
        End If
        
    'return the SQL
        fDeriveCreateTableSQL = strSQL
        
        Set adoFld = Nothing
    End Function
     
    'Convert the field data type constant to text (TSQL or Jet SQL)
    Private Function fConvertADODataTypeToAccess(adoDataType As ADODB.DataTypeEnum, AccessData0MASSQLData1 As eSQL_Syntax) As String
        
    'Self explanatory?
        Select Case adoDataType
            
            Case adVarWChar 'Strictly this translates to NVarChar - changed to VarChar here
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "text"
                Else
                    fConvertADODataTypeToAccess = "VarChar"
                End If
            Case adBoolean
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Boolean"
                Else
                    fConvertADODataTypeToAccess = Mid("adBit", 3)
                End If
            Case adTinyInt
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Byte"
                Else
                    fConvertADODataTypeToAccess = Mid("adTinyInt", 3)
                End If
            Case adUnsignedTinyInt
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Byte"
                Else
                    fConvertADODataTypeToAccess = Mid("adTinyInt", 3)
                End If
            Case adSmallInt
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Integer"
                Else
                    fConvertADODataTypeToAccess = Mid("adSmallInt", 3)
                End If
            Case adInteger
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Long"
                Else
                    fConvertADODataTypeToAccess = Mid("adInteger", 3)
                End If
            Case adSingle
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Single"
                Else
                    fConvertADODataTypeToAccess = Mid("adReal", 3)
                End If
            Case adDouble
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Double"
                Else
                    fConvertADODataTypeToAccess = Mid("asReal", 3)
                End If
            Case adCurrency
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Currency"
                Else
                    fConvertADODataTypeToAccess = Mid("adCurrency", 3)
                End If
            Case adDecimal
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Decimal"
                Else
                    fConvertADODataTypeToAccess = Mid("adDecimal", 3)
                End If
                    
            Case adNumeric
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Double"
                Else
                    fConvertADODataTypeToAccess = Mid("adNumeric", 3)
                End If
            Case adDate
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Date"
                Else
                    fConvertADODataTypeToAccess = "DateTime"
                End If
            Case adDBTimeStamp
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Date"
                Else
                    fConvertADODataTypeToAccess = Mid("adDateTime", 3)
                End If
            Case adDBDate
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Date"
                Else
                    fConvertADODataTypeToAccess = Mid("adDBDate", 3)
                End If
            Case adVarChar
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Text"
                Else
                    fConvertADODataTypeToAccess = Mid("adVarChar", 3)
                End If
            Case adLongVarWChar
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Memo"
                Else
                    fConvertADODataTypeToAccess = Mid("adLongVarWChar", 3)
                End If
            Case adChar
                If AccessData0MASSQLData1 = 0 Then
                    fConvertADODataTypeToAccess = "Text"
                Else
                    fConvertADODataTypeToAccess = Mid("adChar", 3)
                End If
            Case Else
                fConvertADODataTypeToAccess = "Error"
        End Select
        
    End Function
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Interesting function...


    There is some discussion happening about creating category FAQ's/rules etc. Now that we have the ability to do so, I'm thinking a "code exchange" sticky thread might not be to shabby either. Perhaps the thread would be read only and code could be submitted. If we stuck to something like that, we could create a nice, tidy thread of code samples that could be referenced along side the FAQ.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Teddy
    I'm thinking a "code exchange" sticky thread might not be to shabby either. Perhaps the thread would be read only and code could be submitted. If we stuck to something like that, we could create a nice, tidy thread of code samples that could be referenced along side the FAQ.
    That sounds excellent - I think some minimum enforced standard of annotation, effectiveness, bug free(ish), efficiency is a wise decision rather than a free for all.

    Once the search facility is up again I am happy to help put together resources\ links on FAQs (why does my autonumber have gaps in it?. I am wrapping my date variable in single quotes in VB why doesn't it work? etc.). I have considered helping the moderation cause but would prefer to commit myself on a more informal basis - this sort of thing would be something I would be more than happy to help with to develop the forum.

    Good lord - you know what this means - no more links to Rudy's site in posts!

    Quote Originally Posted by Teddy
    Interesting Function...
    Lol - I think you think white elephant too.
    Last edited by pootle flump; 01-18-06 at 11:54.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Good lord - you know what this means - no more links to Rudy's site in posts!
    We may just keep the basic relation question as a link to Rudy's site, you know, for posterity...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  15. #15
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    Teddy n Healdem n Pat: do it!

    if nothing else, it's faster to type "rtfFAQ" than to post yet again on the same old subject.

    i agree with pootle that anything like a FAQ or "codebank" needs to have submissions vetted by our new real-people moderators (at least until one of you rejects one of my submissions!)

    izy
    currently using SS 2008R2

Posting Permissions

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