Call access vb from another program

[Maarten Zaagman]

Hello all,

I have an accessdb and it can create a report from scratch with a button and a lot of vb. I want to automise the reporting. Therefore I want to call the vb sub making the report from another program. (another vbprogram or java or any programminglanguage) When I have that program I can call it in a scheduler and thereby have automised the reporting system.

I have searched for this for a while but I couldn't find anything. Does anyone know if this is possible or not??

[nico5038]

You can make the Access .mdb self executing by using the Autoexec macro or a form that's used as the startup form and executes the code and closes the .mdb.

Such a .mdb can be scheduled, no other language needed.

Nic;o)

[NeoPa]

Application Automation may help you to run things from outside of Access.
Bear Nico's reply in mind though. Automation is more complicated and is probably not necessary in your case.

[ADezii]

Hello all,

I have an accessdb and it can create a report from scratch with a button and a lot of vb. I want to automise the reporting. Therefore I want to call the vb sub making the report from another program. (another vbprogram or java or any programminglanguage) When I have that program I can call it in a scheduler and thereby have automised the reporting system.

I have searched for this for a while but I couldn't find anything. Does anyone know if this is possible or not??

I do believe that I have the answer to your question. The following code examples will execute a Public Function within an Access Database (C:\Test Directory\Test.mdb) via a Command Button on an Excel Spreadsheet. This is a simple example of Automation with Access acting as an Automation Server.

1. Within Excel set a Reference to the specific Access Object Library. In this case the Reference will be to the Microsoft Access 9.0 Object Library.

'Place in Declarations Section of Excel Module
Dim objAccAuto As Access.Application

'In the Click() Event of a Command Button in an Excel Spreadsheet
Private Sub CommandButton1_Click()
Dim strProjectName As String
Dim strProcedureToRun As String
 
'Create an Instance of an Access Aplpication 2000 Object
Set objAccAuto = CreateObject("Access.Application.9")
 
'Open Test.mdb in a Microsoft Access Window in Exclusive Mode
objAccAuto.OpenCurrentDatabase "C:\Test Directory\Test.mdb", True
 
'Get the Project Name referring to the Database
strProjectName = objAccAuto.GetOption("Project Name")
 
'The name of the "Public" Function to Run in Test.mdb consists of
'ProjectName.FunctionName
strProcedureToRun = strProjectName & ".fTestAutomation"
 
'Run the Function
objAccAuto.Run strProcedureToRun
 
Set objAccAuto = Nothing
End Sub

'Demo Function in Access Database (Test.mdb)
Public Function fTestAutomation()
Dim T As Long
 
Open "C:\Test Directory\Auto.txt" For Append As #1
 
For T = 1 To 100
  Write #1, "Line #" & Str(T) & " ==> " & T ^ 2
Next T
 
Close #1
End Function

'Sample Output
"Line # 1 ==> 1"
"Line # 2 ==> 4"
"Line # 3 ==> 9"
"Line # 4 ==> 16"
"Line # 5 ==> 25"
"Line # 6 ==> 36"
"Line # 7 ==> 49"
"Line # 8 ==> 64"
"Line # 9 ==> 81"
"Line # 10 ==> 100"
"Line # 11 ==> 121"
"Line # 12 ==> 144"
"Line # 13 ==> 169"
"Line # 14 ==> 196"
"Line # 15 ==> 225"
"Line # 16 ==> 256"
"Line # 17 ==> 289"
"Line # 18 ==> 324"
"Line # 19 ==> 361"
"Line # 20 ==> 400"
"Line # 21 ==> 441"
"Line # 22 ==> 484"
"Line # 23 ==> 529"
"Line # 24 ==> 576"
"Line # 25 ==> 625"
"Line # 26 ==> 676"
"Line # 27 ==> 729"
"Line # 28 ==> 784"
"Line # 29 ==> 841"
"Line # 30 ==> 900"
"Line # 31 ==> 961"
"Line # 32 ==> 1024"
"Line # 33 ==> 1089"
"Line # 34 ==> 1156"
"Line # 35 ==> 1225"
"Line # 36 ==> 1296"
"Line # 37 ==> 1369"
"Line # 38 ==> 1444"
"Line # 39 ==> 1521"
"Line # 40 ==> 1600"
"Line # 41 ==> 1681"
"Line # 42 ==> 1764"
"Line # 43 ==> 1849"
"Line # 44 ==> 1936"
"Line # 45 ==> 2025"
"Line # 46 ==> 2116"

NOTE: You can use this same functionality in any Client Application that supports Automation. Too tired - going to sleep now. Hope this helped.

[Maarten Zaagman]

Hello,

Thank you all for your replies. I havn't tested any of your solutions yet but i'm sure that they will help me greatly.

[NeoPa]

Maarten,
If you do manage to find a solution that fits your needs (from anywhere not just the solutions posted here), and you get the time, we'd be interested to hear how you get on.
That helps others who come searching to know whether or not the solutions are reliable.