Solved

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

Posted on 2008-10-07
7
299 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 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…

705 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