VB.NET Run Access Macro on Database on Network

I'm trying to run a macro on a database that's located on another computer on our network.
Here's the code I'm running:

        Dim Adb As New Access.Application
        Adb.OpenCurrentDatabase(thePath & theDBName)
        Adb.DoCmd.RunMacro("Final")
        Adb.CloseCurrentDatabase()
        Adb.Quit()

Open in new window


I get this error:

"COMException was unhandled"
"You canceled the previous operation."

Pretty sure I didn't cancel anything...

Any ideas?

Thanks!
LVL 1
slightlyoffAsked:
Who is Participating?
 
MacroShadowConnect With a Mentor Commented:
Dot Net programs will run from a remote computer! You will have to Assign full trust permissions.

Assign Full Trust permission

    Do the following, depending on your operating system:
        On Windows XP, click Start, and then click Control Panel.
        On Windows 2000, click Start, point to Settings, and then click Control Panel.
    Do the following, depending on the version of the .NET Framework that you are using:
        If you are using the .NET Framework 1.1, double-click Administrative Tools, and then double-click Microsoft .NET Framework 1.1 Wizards.
        If you are using the .NET Framework 1.0, double-click Administrative Tools, and then double-click Microsoft .NET Framework Wizards.
    The .NET Wizards window appears.
    In the .NET Wizards window, click Trust an Assembly. The Trust an Assembly dialog box appears.
    Click to select the Make changes to this computer option, and then click Next.
    Click Browse. The Choose Assembly dialog box appears.
    In the Choose Assembly dialog box, locate the executable file on the mapped network drive, and then click Open.
    In the Trust an Assembly dialog box, click Next.
    In the Trust an Assembly dialog box, move the slider to Full Trust, and then click Next.
    Click Finish.
http://support.microsoft.com/kb/832742
0
 
MacroShadowCommented:
Are you sure the macro exists? Are macros enabled on the remote machine?
0
 
MattiCommented:
running anything on network is a bit problematic on .net use ASP/ASPX to do that and browser or own build browser application. So IIS and use it.

You can open remote session but that log's out user so it's bad and use timing system to execute app in certain time automated.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What does your macro named "Final" do? Can you show the steps included in that macro, and give us a description of each?

I'm not sure what Matti is referring to ... Access is run on a network all the time, and there's really no reason to switch to .NET or ASP.
0
 
aikimarkConnect With a Mentor Commented:
1. Do you want to run a macro or VBA routine?
2. I suspect you aren't allowing the macro to complete, as you are immediately closing the database.  
Solution: Have the macro/code close the database.
0
 
slightlyoffAuthor Commented:
Thank you all for your replies.

The access database in question displays imported data from QuickBooks so that a report program I've written will run faster (querying QuickBooks directly can take some time).  

Since the reports don't need to be up-to-the-second current, I update the database using a Macro every morning at 5:30.  Every now and then the database doesn't update correctly - (items that were ordered yesterday might not show up as ordered in the Access database, etc).

When this happens, I normally run the macro again, or one of the other macros I have set up to update individual tables.  I run these directly on the access database.  I thought that it would be outstanding and wonderful if users could, upon detecting a problem with the data, run the update themselves.  

The macro Final does several things:

1.  Updates the status table to let programs connecting to the database know that it's in the process of updating.
2.  Removes 9 tables from the database
2.  Connects to another datasource (QuickBooks) and re-imports the 9 tables (with updated data - takes about 30 minutes)
3.  Duplicates 2 tables and removes older transactions from the 2 new tables.
4.  Updates a Status table so programs using the database know its updated.
5.  Closes the Database (set to compact on close)

I followed aikimark's suggestion and removed the close database from the code like this:

Dim Adb As New Access.Application
        Adb.OpenCurrentDatabase(thePath & theDBName)
        Adb.DoCmd.RunMacro("Final")
        'Adb.CloseCurrentDatabase()
        'Adb.Quit()

Open in new window


But I received the same error.

Here's the code for "Final"

Function Final()
On Error GoTo Final_Err

    Call run_it


Final_Exit:
    Exit Function

Final_Err:
    MsgBox Error$
    Resume Final_Exit

End Function

Open in new window


Here's the code for the run_it function:
On Error GoTo Macro1_Err

    
DoCmd.SetWarnings (WarningsOff)
DoCmd.OpenQuery ("UpdateStatus")

If tableExists("Invoice") Then
    DoCmd.DeleteObject acTable, "Invoice"
End If

If tableExists("InvoiceLine") Then
    DoCmd.DeleteObject acTable, "InvoiceLine"
End If

If tableExists("Customer") Then
    DoCmd.DeleteObject acTable, "Customer"
End If

If tableExists("Vendor") Then
    DoCmd.DeleteObject acTable, "Vendor"
End If

If tableExists("ItemInventory") Then
    DoCmd.DeleteObject acTable, "ItemInventory"
End If

If tableExists("CreditMemo") Then
    DoCmd.DeleteObject acTable, "CreditMemo"
End If

If tableExists("CreditMemoLine") Then
    DoCmd.DeleteObject acTable, "CreditMemoLine"
End If

If tableExists("CreditMemoLinkedTxn") Then
    DoCmd.DeleteObject acTable, "CreditMemoLinkedTxn"
End If

If tableExists("SalesOrderLine") Then
   DoCmd.DeleteObject acTable, "SalesOrderLine"
End If

dsn = "QuickBooks Data"

ODBCConnectStr = "ODBC;DSN=" & dsn & ";"

DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Customer", "Customer", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Invoice", "Invoice", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "ItemInventory", "ItemInventory", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "Vendor", "Vendor", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "InvoiceLine", "InvoiceLine", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemo", "CreditMemo", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemoLine", "CreditMemoLine", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "CreditMemoLinkedTxn", "CreditMemoLinkedTxn", False
DoCmd.TransferDatabase acImport, "ODBC", ODBCConnectStr, acTable, "SalesOrderLine", "SalesOrderLine", False

DoCmd.OpenQuery ("DeleteInvoiceTrim")
DoCmd.OpenQuery ("DeleteInvoiceLineTrim")
DoCmd.OpenQuery ("FillInvoiceTrim")
DoCmd.OpenQuery ("FillInvoiceLineTrim")

DoCmd.OpenQuery ("FinishStatus")


DoCmd.CloseDatabase

Macro1_Exit:
    
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit

Open in new window


Thanks again for your help!!!
0
 
aikimarkCommented:
You are not running a macro, you are running code.

You should transform your Final function into a macro that invokes the run_it() function with the runcode method and then closes the database.
0
 
aikimarkCommented:
alternatively, you can do this in place of the docmd statement:
application.run "run_it"

Open in new window

0
 
slightlyoffAuthor Commented:
Screenshot of Macro
I probably posted my response to soon, I have a module called Final, I shouldn't have posted that, I created it when I was trying to figure out how to set this whole thing up.  But I also have a macro called Final.  The macro Final is what I call.

I also tried this:

Dim Adb As New Access.Application
        Adb.OpenCurrentDatabase(thePath & theDBName)
        Adb.Application.Run("run_it")

Open in new window


I get a different Error Code:  HRESULT: 0x800A9D9F
I'm searching Google about it now, looks like it's something to do with security.  I'll research it some more.
0
 
aikimarkCommented:
the application.run should have been Adb.Run
0
 
slightlyoffAuthor Commented:
I get the same error:  HRESULT: 0x800A9D9F
If I move the database to my computer, the code works fine.

I'm still looking into where what the code means.
0
 
MacroShadowCommented:
Read my article on overcoming Microsoft Office's annoying security features  http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/A_10805-Overcome-the-Trust-Center-Nuisance.html

The part relevant to your problem is the "AllowNetworkLocations" key.
0
 
MattiCommented:
Hi  make a small prog what runs the macro install it on server and make a bat or shell program to run that, but do not make that shell program using .net

NET programs does not run overnetwork, they can connect to a database over network.

matti
0
 
MattiCommented:
roaming profiles is one what allows to run programs over network, but that needs gigabytes of band
0
 
slightlyoffAuthor Commented:
Thank you for your help.  I'm closing the question because I've had to set the project aside for a while.  After reviewing it, I believe the issue is as MacroShadow is describing it.  I'm hoping I can test it soon.   Thank you for all of your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.