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