VBA Code to import Module (1 Viewer)

[tucker61]

OK, my database is split into front and back end, In the back end overnight from various sources I import 10 tables that the front end needs to pull data from.

The code i use to do this works like a dream with no issues..

Function GetLatestData()
Dim Index As Recordset
Dim i As Integer
Dim TableSet As Integer
   If CurrentDb.Name = "Backend.mdb" Then
   Set Index = CurrentDb.OpenRecordset("SELECT * FROM tblImportTables;")
        If Not Index.EOF Then
            Index.MoveLast
            TableSet = Index.RecordCount
            Index.MoveFirst
            While Not Index.EOF
                i = i + 1
                WaitOn i & " of " & TableSet, "Getting Latest Data . . ."
                If ObjectExists(Nz(Index("Table_Name"), "")) Then
                    DoCmd.DeleteObject acTable, Nz(Index("Table_Name"), "")
                End If
                DoEvents
                DoCmd.TransferDatabase acImport, "Microsoft Access", LiveDB, acTable, Nz(Index("Table_Name"), ""), Nz(Index("Table_Name"), ""), False
                DoEvents
                Index.MoveNext
            Wend
        End If
        Index.Close
        Set Index = Nothing
        Waitoff
    End If
    DoEvents
End Function

I seem to be constantly updating VBA code in the back end, but unfortunately i cant do this whilst someone else is in the database, so i was wondering how easy is it to create the module in the front end and then import the updated module when i import the data tables overnight ?

 

[MarkK]

I seem to be constantly updating VBA code in the back end, ...

I don't understand this. There should not be any VBA code in the back end. Code is in the FE, and the most common approach is to completely replace the FE with the next version.
hth
Mark

 

[tucker61]

I don't understand this. There should not be any VBA code in the back end. Code is in the FE, and the most common approach is to completely replace the FE with the next version.
hth
Mark

I agree with what you are saying, but...
We have automated tasks that run every night, these could be on any of plus 70 machines (this is outside my control) if these tasks looked at the front end of the database, they would fail because they would need to logon to the database.

So the tasks point directly to the back and run the macro / code straight from there. (The back end does not check if you have permissions).

This works fine until I want to add another query to the code that runs. Which is when I don't have exclusive use,,

T61

Sent from my SM-T715 using Tapatalk

 

[isladogs]

I agree with what you are saying, but...
We have automated tasks that run every night, these could be on any of plus 70 machines (this is outside my control) if these tasks looked at the front end of the database, they would fail because they would need to logon to the database.

So the tasks point directly to the back and run the macro / code straight from there. (The back end does not check if you have permissions).

This works fine until I want to add another query to the code that runs. Which is when I don't have exclusive use,,

T61

Sent from my SM-T715 using Tapatalk

I might as well say the blindingly obvious ...
Why not change it so all automated tasks are run automatically at scheduled times from just one computer?

 

[tucker61]

I might as well say the blindingly obvious ...
Why not change it so all automated tasks are run automatically at scheduled times from just one computer?

It did work that way until recently, (Task scheduler). I have a slave pc under my desk, but the slave fails quite regularly, loss of network, switched off etc. The solution was to pass over to our it team for them to carry out the imports etc. Which leads me to where I am today..

Sent from my SM-T715 using Tapatalk

 

[isladogs]

Then you have 2 approaches:

1. EASY - Fix the issues with the slave PC so its always on & has a reliable connection (or run the task on the server)

2, DIFFICULT - persevere with what you were asking about

 

[MarkK]

... add another query to the code that runs ...

This process should be parameterized and not require you to modify your code. If you find yourself needing to modify your code in this way, you have likely misconceptualized this solution.
I invite you to show the code that needs to change, and we can fix it so your VBA is not dependent on your data.
hth
Mark

 

[Pat Hartman]

Since code does not belong in the BE for exactly the reason you are running into, make a separate FE that runs only this process. There is no law that says an application has to have only ONE FE. I have several applications that have batch processes that are run overnight on a server or a slave PC. In one case I started out using the standard FE but added a macro to kick off the process. The batch job opened the database using the /macro argument to run the specified macro. This bypasses the normal AutoExec or named form open and goes directly to the macro. Just make sure that the macro closes the app at the end. Eventually I decided that the batch process was sufficiently large that it was making the FE larger than it needed to be. There was no cross over code so I just separated the batch process and put it into its own FE so now the process is easier to manage and the two FE's don't interfere with each other.