Export Query Automation

R

roger

Hi all,

Here is what I am trying to do, hope someone can help me implement
this.

Every hour, I would like a script that does the following (in vbscript
cause that's the only one I am even remotely comfortable with)

1- Extract from a secure database 4 query result tables and copy them
into a new unsecure database

2- Connect to the internet

3- Send the new unsecure database to our servers online

4- Close the internet connection


I think I have figured out how to do step number 3 through an ftp -s:
script and ftp command .txt file.

My problem is really step 1, so far, I have only been able to create a
new blank database, with the structures of the 4 tables receiving the
query results built. I am stuck here, I can't even transfer the query
results to the database, and I haven't even started looking at how to
unsecure the new database, and/or tables.

Does anyone know of any efficient, quick way of doing, the above?

I think I can manage with steps 2 and 4, Step 1 is really my problem
here.


I have so far the following code.




----BEGINNING OF CODE




'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************

' DECLARE VARIABLES


'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************





Dim appAccess, appAccess2, filesys, filedelete, db
Dim t1, t2, t3, t4, f1, f2, f3, f4, f5, f6, f7, f8, f9, f10, f11, f12,
f13, f14, i1, i2, i3, i4

Const DB_TEXT = 10
Const DB_LONG = 4
Const DB_DATE = 8
Const DB_DOUBLE = 7
Const AcFormatXLS = "Microsoft Excel (*.xls)"
Const AcOutputTable = 0
Const AcOutputQuery = 1
Const et1 = "FastTrackOperation"


Const OldPath = "D:\Main\Design\FlashTest\Bck-Jun-10-04.mdb"
Const InterPath = "D:\db2.xls"
Const NewPath = "D:\db2.mdb"








'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************

' DELETE DB2.MDB IF PRESENT


'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************




Set filesys = CreateObject("Scripting.FileSystemObject")

If filesys.FileExists(NewPath) Then
Set filedelete = filesys.GetFile(NewPath)
filedelete.Delete
Set filedelete = Nothing
End If

Set filesys = Nothing




'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************

' CREATE BRAND NEW DATABASE


'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************




Set appAccess = CreateObject("Access.Application.10")
appAccess.NewCurrentDatabase NewPath


Set db = appAccess.CurrentDb


'**********************************************************************************************
'**********************************************************************************************

' CREATE TABLE 1

'**********************************************************************************************
'**********************************************************************************************


Set t1 = db.CreateTableDef("t1")

Set f1 = t1.CreateField("f1", DB_LONG, 40)
f1.Required = -1
t1.Fields.Append f1

Set f2 = t1.CreateField("f2", DB_LONG, 40)
f2.Required = -1
t1.Fields.Append f2

Set f3 = t1.CreateField("f3", DB_TEXT, 40)
f3.Required = -1
t1.Fields.Append f3

Set f4 = t1.CreateField("f4", DB_TEXT, 40)
f4.Required = -1
t1.Fields.Append f4

Set f5 = t1.CreateField("f5", DB_DATE, 40)
f5.Required = -1
t1.Fields.Append f5

Set f6 = t1.CreateField("f6", DB_TEXT, 40)
f6.Required = -1
t1.Fields.Append f6

Set f7 = t1.CreateField("f7", DB_DATE, 40)
t1.Fields.Append f7

Set f8 = t1.CreateField("f8", DB_TEXT, 40)
t1.Fields.Append f8

Set f9 = t1.CreateField("f9", DB_TEXT, 40)
t1.Fields.Append f9

Set i1 = t1.CreateIndex("i1")

Set f1 = i1.CreateField("f1", DB_LONG, 40)
i1.Fields.Append f1
i1.Primary = -1
i1.Unique = -1

t1.Indexes.Append i1

Set i2 = t1.CreateIndex("i2")

Set f2 = i2.CreateField("f2", DB_LONG, 40)
i2.Fields.Append f2
i2.Primary = 0
i2.Unique = 0

t1.Indexes.Append i2

db.TableDefs.Append t1



'**********************************************************************************************
'**********************************************************************************************

' CREATE TABLE 2

'**********************************************************************************************
'**********************************************************************************************



Set t2 = db.CreateTableDef("t2")

Set f1 = t2.CreateField("f1", DB_LONG, 40)
f1.Required = -1
t2.Fields.Append f1

Set f2 = t2.CreateField("f2", DB_TEXT, 40)
f2.Required = -1
t2.Fields.Append f2

Set f3 = t2.CreateField("f3", DB_LONG, 40)
f3.Required = -1
t2.Fields.Append f3

Set f4 = t2.CreateField("f4", DB_TEXT, 40)
f4.Required = -1
t2.Fields.Append f4

Set f5 = t2.CreateField("f5", DB_TEXT, 40)
t2.Fields.Append f5

Set f6 = t2.CreateField("f6", DB_LONG, 40)
t2.Fields.Append f6

Set f7 = t2.CreateField("f7", DB_LONG, 40)
t2.Fields.Append f7

Set f8 = t2.CreateField("f8", DB_LONG, 40)
f8.Required = -1
t2.Fields.Append f8

Set i1 = t2.CreateIndex("i1")

Set f1 = i1.CreateField("f1", DB_LONG, 40)
i1.Fields.Append f1
i1.Primary = 0
i1.Unique = 0

t2.Indexes.Append i1

Set i2 = t2.CreateIndex("i2")

Set f2 = i2.CreateField("f2", DB_TEXT, 40)
i2.Fields.Append f2
i2.Primary = 0
i2.Unique = 0

t2.Indexes.Append i2

Set i3 = t2.CreateIndex("i3")

Set f8 = i3.CreateField("f8", DB_LONG, 40)
i3.Fields.Append f8
i3.Primary = -1
i3.Unique = -1

t2.Indexes.Append i3

db.TableDefs.Append t2



'**********************************************************************************************
'**********************************************************************************************

' CREATE TABLE 3

'**********************************************************************************************
'**********************************************************************************************



Set t3 = db.CreateTableDef("t3")

Set f1 = t3.CreateField("f1", DB_LONG, 40)
f1.Required = -1
t3.Fields.Append f1

Set f2 = t3.CreateField("f2", DB_DATE, 40)
f2.Required = -1
t3.Fields.Append f2

Set f3 = t3.CreateField("f3", DB_DATE, 40)
f3.Required = -1
t3.Fields.Append f3

Set f4 = t3.CreateField("f4", DB_LONG, 40)
t3.Fields.Append f4

Set f5 = t3.CreateField("f5", DB_TEXT, 40)
f5.Required = -1
t3.Fields.Append f5

Set f6 = t3.CreateField("f6", DB_TEXT, 40)
t3.Fields.Append f6

Set f7 = t3.CreateField("f7", DB_TEXT, 40)
t3.Fields.Append f7

Set i1 = t3.CreateIndex("i1")

Set f1 = i1.CreateField("f1", DB_LONG, 40)
i1.Fields.Append f1

Set f2 = i1.CreateField("f2", DB_DATE, 40)
i1.Fields.Append f2

Set f3 = i1.CreateField("f3", DB_DATE, 40)
i1.Fields.Append f3

i1.Primary = -1
i1.Unique = -1

t3.Indexes.Append i1

Set i2 = t3.CreateIndex("i2")

Set f5 = i2.CreateField("f5", DB_TEXT, 40)
i2.Fields.Append f5
i2.Primary = 0
i2.Unique = 0

t3.Indexes.Append i2

db.TableDefs.Append t3



'**********************************************************************************************
'**********************************************************************************************

' CREATE TABLE 4

'**********************************************************************************************
'**********************************************************************************************



Set t4 = db.CreateTableDef("t4")
Set f1 = t4.CreateField("f1", DB_LONG, 40)
t4.Fields.Append f1
Set f2 = t4.CreateField("f2", DB_TEXT, 40)
t4.Fields.Append f2
Set f3 = t4.CreateField("f3", DB_DATE, 40)
t4.Fields.Append f3
Set f4 = t4.CreateField("f4", DB_DOUBLE, 40)
t4.Fields.Append f4
Set f5 = t4.CreateField("f5", DB_DOUBLE, 40)
t4.Fields.Append f5
Set f6 = t4.CreateField("f6", DB_DOUBLE, 40)
t4.Fields.Append f6
Set f7 = t4.CreateField("f7", DB_DOUBLE, 40)
t4.Fields.Append f7
Set f8 = t4.CreateField("f8", DB_DOUBLE, 40)
t4.Fields.Append f8
Set f9 = t4.CreateField("f9", DB_DOUBLE, 40)
t4.Fields.Append f9
Set f10 = t4.CreateField("f10", DB_LONG, 40)
t4.Fields.Append f10
Set f11 = t4.CreateField("f11", DB_LONG, 40)
t4.Fields.Append f11
Set f12 = t4.CreateField("f12", DB_DOUBLE, 40)
t4.Fields.Append f12
Set f13 = t4.CreateField("f13", DB_DOUBLE, 40)
t4.Fields.Append f13
Set f14 = t4.CreateField("f14", DB_TEXT, 40)
t4.Fields.Append f14
db.TableDefs.Append t4



'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************

' EXPORT ACCESS QUERIES TO EXCEL


'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************

Set appAccess = Nothing
Set db = Nothing


Set appAccess2 = CreateObject("Access.Application.10")
appAccess2.OpenCurrentDatabase OldPath


'appAccess2.DoCmd.OutputTo acOutputTable, et1, acFormatXLS, InterPath
'appAccess2.DoCmd.TransferDatabase acExport, "Microsoft Access",
NewPath, AcOutputTable, et1, "[Test1]", False
appAccess2.DoCmd.CopyObject NewPath, , AcTable, et1

'NOTHING SEEMS TO WORK HERE _ GET VARIOUS ERRORS ACCROSS ALL THREE
TECHNIQUES



appAccess2.CloseCurrentDatabase
appAccess2.Quit
Set appAccess2 = Nothing


'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************

' CLEAR ALL OBJECTS


'**********************************************************************************************
'**********************************************************************************************
'**********************************************************************************************





Set t1 = Nothing
Set t2 = Nothing
Set t3 = Nothing
Set t4 = Nothing
Set f1 = Nothing
Set f2 = Nothing
Set f3 = Nothing
Set f4 = Nothing
Set f5 = Nothing
Set f6 = Nothing
Set f7 = Nothing
Set f8 = Nothing
Set f9 = Nothing
Set f10 = Nothing
Set f11 = Nothing
Set f12 = Nothing
Set f13 = Nothing
Set f14 = Nothing
Set i1 = Nothing
Set i2 = Nothing
Set i3 = Nothing
Set i4 = Nothing

Set appAccess2 = Nothing
Set appAccess = Nothing
Set db = Nothing
 
C

ChrisJ

I assume that the code you have is running in the secured
database. If so, I would suggest the following procedure

1. Create a blank unsecured database from within the usual
MSAccess app.
2. Create the tables you need in the unsecured database.
3. Keep this database as a "source template"
4. From within the secured database, instead of creating
the database and tables in code, just copy the template
database to a "user" location, link to the tables and copy
the data using queries.

This should ensure that the unsecured database stays
unsecured.
 
R

roger

Well actually Chris,
the code would run from a script, is what you mentioned still
possible?
Considering the different steps of your answer, the code, I posted
would seem to get me your steps 1 (although I am not sure how to
insure that it is unsecure, is there a special switch I need to add)
and 2.

Could you give me some hints on the methods, I would need to call to
achieve steps 3 and 4, the ones I posted before, I guess are not the
right ones.

thanks for your help

roger
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Database Extract Automation 7
Quickest method to take data from a table, modify it and put it in another table 5
Quickest way to modify data and insert into new table 1
Help repeating formulas with skips 1
error problem 2
Using a variable to decide which field to get a value from 9
What's wrong with this SQL for Access? 4
need query results showing Table 1 records both w/ and w/o matches in Table 2. 6

Top