Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB.NET Run Access Macro on Database on Network

Posted on 2014-01-29
15
Medium Priority
?
601 Views
Last Modified: 2014-02-13
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!
0
Comment
Question by:slightlyoff
  • 4
  • 4
  • 3
  • +2
15 Comments
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39820169
Are you sure the macro exists? Are macros enabled on the remote machine?
0
 
LVL 14

Expert Comment

by:Matti
ID: 39820197
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
 
LVL 85
ID: 39820319
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 1000 total points
ID: 39820860
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
 
LVL 1

Author Comment

by:slightlyoff
ID: 39820993
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
 
LVL 46

Expert Comment

by:aikimark
ID: 39821064
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
 
LVL 46

Expert Comment

by:aikimark
ID: 39821082
alternatively, you can do this in place of the docmd statement:
application.run "run_it"

Open in new window

0
 
LVL 1

Author Comment

by:slightlyoff
ID: 39821196
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
 
LVL 46

Expert Comment

by:aikimark
ID: 39821770
the application.run should have been Adb.Run
0
 
LVL 1

Author Comment

by:slightlyoff
ID: 39821838
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
 
LVL 28

Expert Comment

by:MacroShadow
ID: 39821878
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
 
LVL 14

Expert Comment

by:Matti
ID: 39822474
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
 
LVL 14

Expert Comment

by:Matti
ID: 39822483
roaming profiles is one what allows to run programs over network, but that needs gigabytes of band
0
 
LVL 28

Accepted Solution

by:
MacroShadow earned 1000 total points
ID: 39822501
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
 
LVL 1

Author Closing Comment

by:slightlyoff
ID: 39856379
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question