Script to automatically export data

[talia679]

This is going to be a hard one to understand.

I'm not sure if it can be done with macros or modules or even at all so I'm categorizing it as General.

Here we go:

I have this HUGE database (Access 97) to store all of our web stats for every web page our company controls. In this database I have relationships up to wazoo and queries querying other queries. There's a lot going on.

We now hava a new member to our staff who will be working in another office. She needs access to only 3 queries. In order for me to give her this access I would have to give her access to everything (I've been playing with the security features and learned this) and we don't want to do this (political and practical reasons).

So here's what we thought - other suggestions always welcome - we want to set up a script that runs daily that exports the 3 queries from my copy of the database into another database ... but when it exports the queries it exports them as tables so that they are no longer dependent on the tables and queries in my version of the database.

Am I making sense? Does this sound possible? Or does anyone else have any better ideas?

 

[RichMorrison]

Talia,

First, some terminology.

One does not "export" a query. The data returned by a query is exported.

So you could export the data return of 3 queries to a spreadsheet, file, or other Access database.

Or, you could append the output of 3 queries to an existing table that the "new person" can use. If the new person is on your local area network, then this is probably the best option.

HTH,
RichM

 

[talia679]

Sorry ... wasn't sure how to word what we wanted.

Okay I think we want option 1

"export the data return of 3 queries to a spreadsheet, file, or other Access database"

But I want the data return of the 3 queries to exported into 3 different tables within another access database.

The user is not on our network ... this is the source of the problem (another political issue).

When you say append I picture us adding the data from the queries to an exisiting table. What we want is to overwrite the tables each day with the newly updated ones.

Hope I used the right terminology that time.

 

[RichMorrison]

You wrote
<<
But I want the data return of the 3 queries to exported into 3 different tables within another access database.

The user is not on our network ... this is the source of the problem (another political issue).

When you say append I picture us adding the data from the queries to an exisiting table. What we want is to overwrite the tables each day with the newly updated ones.
>>

You got it.

If you export into an existing Access data base, then you will replace any existing data with new data. Append will add the new stuff to the existing stuff.

RichM

 

[talia679]

Okay ... so is there a specific way I can do this? So that it's automated? or so that I just hit a button that runs the script and it takes care of it?

I know how to manually export the data of the queries into tables in another data base, but I know nothing of modules or macros.

 

[RichMorrison]

You wrote
<<
Okay ... so is there a specific way I can do this? So that it's automated? or so that I just hit a button that runs the script and it takes care of it?
>>

Yes. I would make a command button and write code to export the query result. Others would probably use macros. I don't use macros so can't help with that.

Try Access Help on you desktop and search for Export. You will find a VB example.

RichM

 

[talia679]

Okay I read ... and it all seems pretty simple except that I want to be able Import the Query (the data output of the query) as a Table. And I want it to overwrite any Table with the same name.

None of this was in the help ...

Here's the steps I need to complete

Open Database 2
Import Query As Table from Database 1 overwriting (not appending to) the Table with the same name

2 steps automated daily

Right now I manually do those 2 steps every day ... I'm sure there's an easier way.

 

[RichMorrison]

See Access Help for "TransferDatabase" method.

You can export data from a query to a table.

That seems to be exactly what you have stated in your earlier posts.

RichM

 

[Late4Supper]

Here's how we do it, ain't pretty but works.

If you can be linked to the 2nd data base when your script runs include a delete query to purge the old data and run an append query to add the new

 

[talia679]

... they can't be linked. One is on out Intranet and one is on our Extranet ... the people outside can't access our Intranet and if the databases are linked it causes all kinds of permission problems ... let me see if I can figure this out using that TransferDatabase option ...