Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Generating subroutine code automatically (Office/Access 2000 SP1A)

    I'm just wondering if it is possible to create (in Access) a function whose output is the code for another subroutine in the same project,
    and then run the resulting code that has just been created? Or am I going up the wrong path here?
    I want to do this because I have a number of very similar routines to run in a batch and it would be more extensible and elegant
    to generate the code from a table containing the variable strings - call this MasterTable.
    That is, 90% of the code body is the same, only varying in lines like:
    rstTemps.Open "Some_table", cnnCurrent, , , adCmdTable
    where the actual table "Some_table" varies from one (instance of the) subroutine to the next.
    Then, for example, you could just add another line to the table "MasterTable." and this would increase
    the number of subroutines to be run in the batch by one subroutine.
    The alternative would be to keep adding more and more subroutines to the module - I'd rather just add
    a record to "MasterTable".
    Hope this doesn't sound too nutty.
    I really hope this makes sense!

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

    Re: Generating subroutine code automatically (Office/Access 2000 SP1A)

    Why don't you make "Some_table" into a string argument for the procedure:

    Sub DoSomethingWith(TableName As String)
    ...
    rstTemps.Open TableName, cnnCurrent, , , adCmdTable
    ...
    End Sub

    Call it like this:

    DoSomethingWith "Some_Table"

    and next time

    DoSomethingWith "Other_Table"

  3. #3
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts

    Re: Generating subroutine code automatically (Office/Access 2000 SP1A)

    Isn't this exactly what the switchboard manager does?

    Have a look at the code and table that get inserted when you add a switchboard to an access database. You do this by going into Tools > Database Utilities > Swtichboard Manager
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating subroutine code automatically (Office/Access 2000 SP1A)

    Yes, I had thought of that but for some reason was convinced it wouldn't work.
    The idea is to develop a general routine that will copy certain records from one table to another
    for a list of pairs of tables. This is where the string argument method might not work as there will be a variable number of lines like:
    rstTemps.AddNew
    rstTemps![Field1] = rstTemps2.[Field1a]
    rstTemps![Field2] = rstTemps2.[Field2a]
    rstTemps![Field3] = rstTemps2.[Field3a]
    ....................... (actual number of lines here will vary from one routine to the next)

    rstTemps![Fieldn] = rstTemps2.[Fieldna]

    rstTemps.Update

    You could do use some sort of do...loop here to iterate through the columns to be copied but I'm not sure how without making it really messy.

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

    Re: Generating subroutine code automatically (Office/Access 2000 SP1A)

    Even if we could come up with a solution for this, you'd undoubtedly then want it to do something else in addition, or handle exceptional cases, etc. You will have to specify completely and in detail what you want the code to do and what it need not do.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating subroutine code automatically (Office/Access 2000 SP1A)

    Depending on the number of situations that you need to program (i.e., is it worth the trouble to do this...) I would look at a table-based solution. Two tables would be required in a one-to-many relationship (this is air code):

    SituationID
    SituationName
    SourceTable
    DestinationTable

    SituationID
    SourceFieldName
    DestinationFieldName

    Choose the situation and pass its ID to a subroutine. The subroutine builds an SQL statement that includes all the sourcefields and destination fields for the particular situation. The SQL statement would be an APPEND query from the source table to the destination table. Then execute the SQL query.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating subroutine code automatically (Office/Access 2000 SP1A)

    Yes, thank you - this is a great idea. (using "Insert into" sql statement).
    There are enough situations to warrant the effort too.
    Thanks

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating subroutine code automatically (Offi

    > output is the code for another subroutine in the same project

    I think that you are well on your way to a solution for your immediate problem with the tables approach; I prefer table-driven solutions over code-driven solutions because (1) the tables are easier to maintain and (2) they are closer to the user's level.

    That said, the answer to your question (above) ought to be "yes". I've written VBA code that self-modifes itself, and can see no reason why one shouldn't be able to write self-modifying code, if the occasion warrants it.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating subroutine code automatically (Offi

    Hear hear!
    Yes I'm going to use Do..Loops and data in tables to construct the INSERT INTO SQL string to transfer the record to the target table.
    But I am still interested in dynamically generated subroutines for possible future applications.
    I found this link illuminating, although it is centred on Excel, not access, but the approach is clear.
    http://www.cpearson.com/excel/vbe.htm
    Thanks for your thoughts.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating subroutine code automatically (Offi

    I just thought of another possible situation where you might want self modifying code.
    With an Access front end and an Access back end - I believe it would be more efficient
    in some circumstances to populate various controls and subforms by using ADO to retrieve
    only the data necessary for the purpose. So, instead of using linked tables to one or more back end
    databases, you could use an ADO call for each table, fully specifying in the connection string where the table is located.
    There might be 1,2 or 10 locations, for example.
    But, rather than hard code this in, I have created a table of the back end tables in the FRONT end showing the full network path
    to each table. This can be easily updated or modified and you can move tables without having to edit the code.
    In this case you may need to open an unspecified number of connections, depending on what tables are used in the routine...
    cnn1.Open "blah blah"
    cnn2. Open "blah2, blah2"
    ...... etc for x times
    Or is there a better way? ...URLS?

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating subroutine code automatically (Offi

    Why would you open multiple connections instead of opening a single connection to the database and using a recordset object to return the records? The single connection could be used by however many recordsets you might need or you could do something like this: <pre>Private Sub GetRecords()
    Dim strSQL As String 'holds SQL for recordset

    If [cboCustomer].Column(0) <> "" Then
    'create the SQL for the recordset
    strSQL = "SELECT * FROM tblOrders AS O " _
    & "WHERE O.CustomerID ='" & [cboCustomer].Column(0) & "'"
    'close the recordset if open
    On Error Resume Next
    mrst.Close
    'set the recordset properties
    With mrst
    .CursorLocation = adUseClient
    .LockType = adLockBatchOptimistic
    .CursorType = adOpenKeyset
    .Source = strSQL
    .ActiveConnection = mcnn
    .Open , , , , adCmdText
    'get the record count
    .MoveLast
    mlngRecCount = .RecordCount
    .MoveFirst
    'populate the # of # display
    Call SetRecordNum
    End With 'mrst
    'disconnect the recordset
    Set mrst.ActiveConnection = Nothing
    'fill the form controls
    'with the data from the first record
    Call FillRecord
    Else '[cboCustomer].Column(0) <> ""
    MsgBox "Please select a valid customer"
    End If '[cboCustomer].Column(0) <> ""

    End Sub 'GetRecords()</pre>

    I have a sample app called NoTables that uses that approach if you're interested.
    Charlotte

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating subroutine code automatically (Offi

    Thanks Charlotte - I will look into your suggestion.
    But to answer your question: the reason I might want several connections is that I have a front end which links to tables
    in several back end databases. Ideally I would have all my back end tables in the one database on a server computer
    (in fact I do have them like this). But the front end is on a computer used in a retail situation and the problem is that, every so often,
    the network connection fails for reasons best know to the vagaries of the windows operating system (for the most part).
    To prevent this from being an annoying interruption to business, I would then want the front end to work with "mirror" tables
    located on the same client computer in the retail area until such time as the network connection is restored. Then I want the
    data recorded in the local mirror tables during the blackout period sent to the server tables to bring them back into sync.
    This requires some code that registers entries on pairs of similar tables - one on the server and one local table on the client.
    For this reason I would need two ADO connections for each operation.
    But it occurred to me that one might want to take this further and join tables in an SQL statement that are on different connections
    (ie : in different back end databases). Is such a thing possible? (in ado)
    Thanks

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating subroutine code automatically (Offi

    I think you're pushing Access to the bleeding edge of its capabilities ... and beyond, so beware. If you have an iffy server connection, ADO notwithstanding, Access is likely to blow up and I don't think having a "mirror" will save it. I believe it is possible to do cross-database SQL joins using ADO but I haven't experimented with it and wouldn't know where to start.
    Charlotte

  14. #14
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Generating subroutine code automatically (Offi

    I second Charlotte's comments - Access databases become pretty fragile when you start having network problems. I would be inclined to look at an MSDE solution with either replication or dual commits to a full SQL Server back-end if you really want to make sure the data doesn't go away. You wouldn't get to have near as much fun with VBA in that structure, but that might make your front-end simpler. Your description sounds as if the front-end at least is a single user application - am I correct on that?
    Wendell

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    314
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Generating subroutine code automatically (Offi

    BLOW UP??
    Nobody warned me about this possibility.
    How can such dangerous software be allowed on the market?

Page 1 of 2 12 LastLast

Posting Permissions

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