?
Solved

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

Posted on 2008-10-07
7
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

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 85
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

777 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