Solved

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

Posted on 2008-10-07
7
268 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access 2016 Debugging 7 41
ms/access ftp / SFTP 3 32
Search Form with Run Time Error 3075 1 21
Run Time Error 3071 26 34
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

943 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

7 Experts available now in Live!

Get 1:1 Help Now