Running code on start up

[Macropheliac]

Hello all!

I'm in the midst of building a fairly extensive database for my company. I've split into front end and back end, but I've become concerned about the front end bloating as well as updating the front end as constant changes and additions will be required. In other words, forms, tables, queries, etc. will have to be added periodically.

In order to make the updating easier, I thought I would save the objects to text files and have the front end load the objects. Therefore, all that would have to be replaced are the text files. However, the constant removing and loading of objects produces quite a bit of bloat. Therefore, I thought I'd figure a way to create a new front end on each open. I saw a few sites about distributing and such, but none of them seemed quite right for me, so I came up with the following...

Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
 
Sub OpenDatabase()
Dim strDB As String
strDB = CurrentProject.Path & "/Timekeeper_FE.accdb"
ShellExecute 0, vbNullString, strDB, vbNullString, vbNullString, 3
End Sub
 
 
Sub CreateFE()
    Dim acApp As Access.Application
    CreateNewDatabase CurrentProject.Path, "Timekeeper_FE.accdb"
    DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentProject.Path & "/Timekeeper_FE.accdb", acModule, "Module3", "Module3"
    Set acApp = CreateObject("Access.Application")
    acApp.OpenCurrentDatabase CurrentProject.Path & "/Timekeeper_FE.accdb"
    acApp.Run "ImportForms"
    acApp.DoCmd.Quit
End Sub
 
 
Public Function CreateNewDatabase(strPath As String, strName As String) As Database
Dim WS As Workspace, db As Database, strFullName As String
    strFullName = strPath & "/" & strName
    Set WS = DBEngine.Workspaces(0)
    If Dir(strFullName) <> "" Then Kill strFullName
    Set db = WS.CreateDatabase(strFullName, dbLangGeneral)
End Function
 
 
Public Function RunCreateFE()
CreateFE
End Function
 
 
Public Function CloseFECreator()
DoCmd.Quit
End Function
 
 
Public Function RunOpenDatabase()
OpenDatabase
End Function
 
Sub ImportForms() ' Shortened version
Dim fName As String
fName = "frmCategory"
LoadForm fName
fName = "frmCraftCode"
LoadForm fName
fName = "frmCraftEntry"
LoadForm fName
fName = "frmDateSelector"
LoadForm fName
fName = "frmDiscipline"
LoadForm fName
End Sub

The CreateFE procedure creates a new database using the CreateNewDatabase function. Then, the OpenDatabase procedure opens the new database using the Shell command, followed by closing the current database using the CloseFECreator function.

This all works well. However, the problem arises when I try to run this when opening the FECreator database. I created an AutoExec macro to do this. It works fine when I open and run manually, but the application hangs and closes when run through AutoExec. I even tried running via a form that was set to start up when opening but the result was the same.

Any ideas as to why this is or how I can go about this differently would be greatly appreciated.

 

[MarkK]

Typically you just replace the whole front-end.

Just to review the typical system design, there is one back end on the network and each user has his/her own front end. During development you update your front-end, and then you simply replace each user's front-end with the next version.

 

[Macropheliac]

Thanks. That is what I intend to do, but the landscape here makes issuing new front-ends to each user virtually impossible. The idea is to keep the text file folder on the network. This way, everyone has an updated version every time they use it.

 

[mdlueck]

You would benefit from:

1) Software Distribution to deploy the FE DB down to the client machine and then execute the FE DB from there.

FE AutoUpdate with RoboCopy / XCopy
http://www.access-programmers.co.uk/forums/showthread.php?p=1214733#post1214733

2) Use this process to prep a new copy of the FE DB prior to deployment:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk...to_Decompile_/_Compact_/_Compile_an_Access_DB

Success deploying completely decomplied database to both Access 2007 and Access 2010
http://www.access-programmers.co.uk/forums/showthread.php?p=1217193#post1217193

 

[MarkK]

If you can consume text file off the network to re-write your FE, how is it impossible to just copy over a new FE? I don't understand.

 

[Macropheliac]

Where I work, I can not send Access files via e-mail. The place is two miles wide and everyone works different shifts; nights, days, etc. It will be physically impossible for me to install new front-ends on each users computer. Therefore, I have come up with this method...

As I make additions and such to my copy of the front-end, I will export the objects as text files to the proper folder(s) on the network. There will be a shortcut on each user's desktop to the FECreator database that will have the code to create the new database, export the module containing the code to the new database and call the code from the newly created database to load the objects.

I hope that makes sense! Perhaps I'm way off base here.


I'm currently reading through your links, mdlueck. Thanks!

Edit: Also, users don't have the same access to the network that I do. Therefore, they can not copy Access files from the network. They can, however, copy text files. I know it's strange, but it is what it is.

 

[Rx_]

Welcome to the Forum.
In my case, a Citrix server was set up for national and remote clients.
Just as described above, as each client logs in, a copy of the FE is created in a folder named after the client.
This way for distribution, it is only necessary to replace the FE that resides in a network folder.
My back-end is SQL Server.
For access 2010, one FE is approaching 60 Mb. It is a compliance application where forms have huge interaction with values as they are entered and interaction with values found across other tables. So, the code is a significant role in the size.
Citrix doesn't require Access to be installed on the client. It runs on Mac too. The bandwidth for field users can drop to 16K to 56K and seem to run just fine.

Can appreciate what you are doing. Many years ago, we had an application for Mac Tools individual owners. The front-end was VB 3 with Access and a SQL Server pre-release and transactions to AS 400. We set up 2 bays of RAS (remote dial up) servers that were 56K.
We distributed code rather than an enter application. Will spare the details, but would suggest that adjusting the landscape would be worth the effort. That said, sometimes the network side of the world can be a challenge.

As to the halt of the startup, does the application have any security? I seem to remember that a workspace creation should keep the same security context unless the parameters are specified.

Just wondering how many total / average concurrent users your application supports?

 

[Macropheliac]

Thanks for the understanding, RX. There is no security yet, if I understand the question correctly. I'm new to Access, so I don't quite understand everything yet.

In the beginning, there will bw 5 to 10 users. As it expands, there could be well over 100.

Citrix is a no-go for me. The people in charge have come to HATE it. I can't explain why, but trust me on this.

 

[Rx_]

Can you explain the consuming a file over the network over just copying the FE?

 

[Macropheliac]

I think lagbolt was wondering, if textfiles can be imported from the network, then why can't the new front-end just be put on the network and they replace their current one. That's how I took it anyway.

 

[Macropheliac]

Just to explain that a little further... it's basically a size constraint. Text files are small enough in size that they can access them without special permissions. Anything over a couple of MBs is too large.

 

[Rx_]

Since you are somewhat new, do you know how to run the debugger, Immediate Window, use the Debug.Print statement
Option Explicit (requres variable declaration in menu)
Error Trapping
guess that will do for a start.
This is to locate exactly what line of code causes the error.

 

[Macropheliac]

Yes. I'm not new to VBA. Just to Access. That's what's so confusing. There is no error. When I run the code manually from a button, it runs like a charm. However, when it runs on start up, the current DB just hangs and eventually closes.

 

[Rx_]

Tools Option Editor check Require object variables
Debug - compile <your db name>

Lets try to run the code from a form.
Add the code to the OnOpen or OnLoad events of the default form. For more information, see the article Set the default form that appears when you open a database

Control+G show the Immediate window
Lets see where the code get to:
On top add
On error Resume Next

then in your code add these

debug.print "Point 1 " & err.number & " " & Err.description
debug.print "Point 2 " & err.number & " " & Err.description
debug.print "Point 3 " & err.number & " " & Err.description
... and so on

Sprinkle this through your code in order - When you run, it should indicate where the code is running OK and where the error happens.

 

[Macropheliac]

I certainly will, RX. It'll take me a little bit to "put it back together" as I have it "torn apart" at the moment. lol

 

[Macropheliac]

Okay. I added the code to the Load event of the form and set the form to load on open. The new database is actually created but the current Db hangs and the new DB doesn't open.

Maybe I'm more ignorant than I thought, but how can I access the immediate window when code is running on start up?

 

[Macropheliac]

I just added some stop commands and stepped through the code. It hangs on the Shell command for a few minutes, doesn't open the new DB and continues to the CloseFECreator function. So, the issue is with the Shell command.