Generating subroutine code automatically (Office/Access 2000 SP1A)

Topic

New Reply

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!

Reply | Quote

Replies

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”

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

> 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.

Reply | Quote

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%5B/url%5D
Thanks for your thoughts.

Reply | Quote

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%5B/url%5D
Thanks for your thoughts.

Reply | Quote

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?

Reply | Quote

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:

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()

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

They’re still working on the warning labels–something like, “This software is dangerous if taken internally.”

Reply | Quote

Or “Do not use near data”…

Reply | Quote

Or “Do not use near data”…

Reply | Quote

They’re still working on the warning labels–something like, “This software is dangerous if taken internally.”

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

Yes, the front end is a single user application at present.
I’m thinking of migrating the whole thing to PHP mySQL – I hope this will avoid many potential pitfalls
that the Access approach apparently has in a network environment including the disastrous possibility of an explosion.
Thanks for your comments.

Reply | Quote

Based on the fact that I can’t get to http://www.mysql.com[/url%5D this morning, you might want to reconsider . Seriously, it is an alternative, and is the technology behind lots of what happens here in the Lounge. On the other hand, if you develop a web-based solution, you still have to deal with the front-end issues when the network isn’t available. One alternative to consider is to use an Access front-end with ODBC links to a mySQL back-end. I know a couple of developers who use that approach. I’m not an expert in mySQL, but based on what I’ve seen, I don’t believe the replication capabilities and the dual commit features match those of SQL Server or the MSDE. Just more food for thought.

Reply | Quote

The trouble is, my current website service provider (Netregistry), and most other ISPs here in Australia,
don’t support ASP. So I seem to be compelled to write my future web applications in PHP which I gather
cannot be used with MS SQL server, or isn’t easy to use with it at any rate.
Surely every database management system meant for web use must have some sort of mirroring feature
(like the dual commit for SQL server) as standard? Otherwise you could never really rest easy in case
your entire business went down the virtual plughole.

Reply | Quote

Hmm… u might consider http://www.parcom.net[/url%5D as a new host. Great service, economy, features.

Reply | Quote

Thanks but I rather suspect they will not offer me the equivalent of the great merchant payment gateway
I currently enjoy. It is a service directly linked to my business account here at my local Australian bank.

Reply | Quote

Thanks but I rather suspect they will not offer me the equivalent of the great merchant payment gateway
I currently enjoy. It is a service directly linked to my business account here at my local Australian bank.

Reply | Quote

Hmm… u might consider http://www.parcom.net[/url%5D as a new host. Great service, economy, features.

Reply | Quote

The trouble is, my current website service provider (Netregistry), and most other ISPs here in Australia,
don’t support ASP. So I seem to be compelled to write my future web applications in PHP which I gather
cannot be used with MS SQL server, or isn’t easy to use with it at any rate.
Surely every database management system meant for web use must have some sort of mirroring feature
(like the dual commit for SQL server) as standard? Otherwise you could never really rest easy in case
your entire business went down the virtual plughole.

Reply | Quote

Based on the fact that I can’t get to http://www.mysql.com[/url%5D this morning, you might want to reconsider . Seriously, it is an alternative, and is the technology behind lots of what happens here in the Lounge. On the other hand, if you develop a web-based solution, you still have to deal with the front-end issues when the network isn’t available. One alternative to consider is to use an Access front-end with ODBC links to a mySQL back-end. I know a couple of developers who use that approach. I’m not an expert in mySQL, but based on what I’ve seen, I don’t believe the replication capabilities and the dual commit features match those of SQL Server or the MSDE. Just more food for thought.

Reply | Quote

Yes, the front end is a single user application at present.
I’m thinking of migrating the whole thing to PHP mySQL – I hope this will avoid many potential pitfalls
that the Access approach apparently has in a network environment including the disastrous possibility of an explosion.
Thanks for your comments.

Reply | Quote

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?

Reply | Quote

Another option would be to use local database exclusively, and then replicate to the server. If/when the network connection goes down, the replicaiton is just delayed for a while, but the local application continues to run as normal. Of course, replication is not without its own challenges.

Reply | Quote

Another option would be to use local database exclusively, and then replicate to the server. If/when the network connection goes down, the replicaiton is just delayed for a while, but the local application continues to run as normal. Of course, replication is not without its own challenges.

Reply | Quote

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

Reply | Quote

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:

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()

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

Reply | Quote

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?

Reply | Quote