Solved

To Call a Macro or Not Call a Macro that is the question?

Posted on 2008-10-07
7
276 Views
Last Modified: 2012-05-05
Hello everyone,
I have two databases, one that is a Access database located on the webserver and another that is a dbase database on a remote machine. I have an ODBC to both.  I have created a query that will copy the data from a table in the dbase database and copy the contents to a Access table. I would like to to trigger this copy from a webpage, now I know I can have the webpage copy the data over but as the table gets bigger it will take longer to copy the data over. The query seems pretty quick at doing this. Should I have the query called from a Macro and call the Macro from a ASP page or is there an easier way? Ideally I would like it so when a person goes to the page I have a query that checks both tables and if they are not the same to have it delete the data from the Access table and Copy over the data from the DBase table for it will be accurate. I have tried:

Dim acs
Dim acCmd
   
Set acs = CreateObject("access.Application")
acs.OpenCurrentDatabase "D:\datapath\database.mdb"
   
Set acCmd = acs.DoCmd
acCmd.RunMacro "Macroname"

But I get:
Microsoft VBScript runtime error '800a0046'

Permission denied: 'CreateObject'

0
Comment
Question by:awinstead
  • 4
  • 2
7 Comments
 
LVL 75
ID: 22664414
You must have 100% FULL permissions on the Folder containing the MDB.  Do you have that ?

mx
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22664446
You can't automate Access on a webserver. Access is a "clientside" application, and cannot be run reliably on an unattended machine (like a webserver). Besides, it's a violation of the licensing agreement (i.e. there is no server license for Access, at least none that I've ever heard of) - in effect, you're running a single copy of Access for every web user ...

If you have a query, then you have the SQL for that query ... you can run that SQL directly from the ASP page, just build and ADO connection and use the .Execute method of that connection.
0
 

Author Comment

by:awinstead
ID: 22664451
Everyone Full Control
0
Backup Your Microsoft Windows Server®

Backup 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.

 

Author Comment

by:awinstead
ID: 22664483
So in other words I have to have ASP load all 6000 some records and loop through them until it copies the data to the table? This is going to slow the page quite a bit. And there is no other way?
0
 
LVL 84
ID: 22664501
I'm not sure ... are you copying records from the DBase database to the Access database? If you are, then you'll have to have some method to "connect" them ... ADO has GetRows and such, which can load data into an array quickly, and you could then just process the array to load records into another location. But again, it's not really clear what you're trying to do.
0
 

Author Comment

by:awinstead
ID: 22664584
Ok I have a DBase database that has a table called calls what I would like to do is mirror that table in Access. The reason I want to do this is because We have some reporting we would like to do and I do not want to mess with the DBase table because there is some financials in it and I don't want to mess up anything. So Ideally if I could mirror the table then I can run whatever I wish and not worry about the data get damaged. Access has a linked table to the DBase table and then I created a second table in Access called calls_list Then I created a query that will grab the data from the Linked table and copy it to the calls_list table to create the mirror. This all works fine. Now I need to be able to automate this process so when I go to a webpage it updates the records. Ideally I would like it so it updated every time the page loads. Kind of like a running tally.

Does this help?
0
 

Author Comment

by:awinstead
ID: 22664614
Oh and I can't use the SQL from the Query because it uses a linked table. ASP doesn't see the linked table.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

776 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