Solved

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

Posted on 2008-10-07
7
258 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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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
Comment Utility
Everyone Full Control
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

763 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

6 Experts available now in Live!

Get 1:1 Help Now