Solved

VB.NET Run Access Macro on Database on Network

Posted on 2014-01-29
15
489 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 27

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 84
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 45

Assisted Solution

by:aikimark
aikimark earned 250 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 45

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 45

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 45

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 27

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 27

Accepted Solution

by:
MacroShadow earned 250 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LINQ - C# to VB convertion 12 56
scanning dentists xray (the small ones) 3 54
Convert an ASPX page into PDF 7 32
Get number of Files in Directory and Sub Directories 2 41
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 …
A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

772 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