Solved

VB.NET Run Access Macro on Database on Network

Posted on 2014-01-29
15
462 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 26

Expert Comment

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

Expert Comment

by:Matti
Comment Utility
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 84
Comment Utility
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
 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 250 total points
Comment Utility
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
Comment Utility
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 45

Expert Comment

by:aikimark
Comment Utility
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 45

Expert Comment

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

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:slightlyoff
Comment Utility
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 45

Expert Comment

by:aikimark
Comment Utility
the application.run should have been Adb.Run
0
 
LVL 1

Author Comment

by:slightlyoff
Comment Utility
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 26

Expert Comment

by:MacroShadow
Comment Utility
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
Comment Utility
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
Comment Utility
roaming profiles is one what allows to run programs over network, but that needs gigabytes of band
0
 
LVL 26

Accepted Solution

by:
MacroShadow earned 250 total points
Comment Utility
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
Comment Utility
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now