Database Extract Automation

[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

 

[Jeff Boyce]

Roger

One idea might be to keep an already-defined (unsecure) database and do
simple update queries or append queries from your secure db to the insecure
one. Is there any chance the two dbs can "see" each other (i.e., you could
create a linked table from one to the other)?

 

[John Nurick]

Hi Roger,

Usually it's simpler to automate the Jet database engine than to
automate Access itself. Situations where the latter is required include
working with forms and reports, or with queries that use functions that
are not available in Jet.

Here's some basic VBScript that opens a secured MDB and creates an
unsecured one. You can then use the Execute method of the secured
database to execute JET SQL statements to move the data across.

Depending on the rquirements for the unsecured databases all you may
need is to execute four make-table queries (SELECT ... INTO ...
statements). OTOH if you need to control field sizes and create indexes,
you'll have to execute a series of Jet SQL DDL statements to create each
table and each index - though this is still less trouble than creating
each field individually as you are doing in your existing code.

As Jeff says, there's a case for simplifying things by creating a
"template" unsecured mdb containing the empty tables. In that case you'd
just open the secured mdb, make a copy of the template, and execute
append queries to transfer the data. But it's probably a more robust
solution if your code creates the unsecured mdb from scratch whenever
needed.

Const SystemDB = "D:\Folder\blah.mdw"
Const SecuredDB = "D:\Folder\Secured.mdb"
Const UnsecuredDB = "D:\Folder\Unsecured.mdb"
Const UserName = "User"
Const Password = "Password"

Dim dbESec 'As DAO.DBEngine
Dim dbWSec 'As DAO.Workspace
Dim dbDSec 'As DAO.Database

Dim dbE 'As DAO.DBEngine
Dim dbDNew 'As DAO.Database

Set dbESec = CreateObject("DAO.DBEngine.36")
dbESec.SystemDB = SystemDB
Set dbWSec = dbESec.CreateWorkspace("Secured", _
UserName, Password, 2) '2 = dbUseJet
Set dbDSec = dbWSec.OpenDatabase(SecuredDB)

Set dbE = CreateObject("DAO.DBEngine.36")

'Add code here to make sure UnsecuredDB doesn't
'already exist

Set dbDNew = dbE.CreateDatabase(UnsecuredDB, _
";LANGID=0x0409;CP=1252;COUNTRY=0")
'above string = dbLangGeneral

'Token manipulation of DBs to prove they're available
Msgbox dbDSec.Name & ": " & dbESec.SystemDB _
& Chr(13) & Chr(10) & dbDNew.Name & ": " & dbE.SystemDB

'Add code here to execute SQL statements
'to create tables/transfer data

'Tidy up
dbDSec.Close
dbWSec.Close
dbDNew.Close

 

[roger]

Thanks for your help John, I am actually almost there thanks to your
help, my only outstanding issue now, is that for some reason, the
vbscript called Sql query cannot process, a number of Access specific
functions, for instance the Nz() function, can I declare a library in
the declaration section of the script to solve this issue, if so,
would you know which library would contain, these basic functions.

thanks for your help
roger

 

[John Nurick]

Hi Roger,

As I said in my first response, there are some functions that can be
used in queries executed from Access that are not available in Jet
itself. Unfortunately Nz() is one of them. SO is any custom VBA function
you may have written.

The general rule is that if a function is part of the standard VBA
library and makes sense in a query, Jet will be able to use it. I
haven't found an authoritative list, but this accounts for something
like 100 VBA functions. OTOH if the function is part of another library
- and Nz() is in Access.Application - it will only be available when the
query is executed from Access. (Use the Object Browser - hit F2 in the
VB Editor - to find out which functions belong where.)

As for Nz(), you can easily replicate it in Jet SQL with something like

IIf([Field] IS NULL, 0, [Field])

 

[Jamie Collins]

The general rule is that if a function is part of the standard VBA
library and makes sense in a query, Jet will be able to use it.

Make that the VBA5 library.

I haven't found an authoritative list, but this accounts for
something like 100 VBA functions.

It is work in progress for Brendan Reynolds MVP:

http://groups.google.com/[email protected]

Jamie.

--

 

[Brendan Reynolds]

Note to self - get that darn thing finished!

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[roger]

John, Jeff, Chris,

Thanks a lot for your help, it now works like a charm

roger

Hi Roger,

As I said in my first response, there are some functions that can be
used in queries executed from Access that are not available in Jet
itself. Unfortunately Nz() is one of them. SO is any custom VBA function
you may have written.

The general rule is that if a function is part of the standard VBA
library and makes sense in a query, Jet will be able to use it. I
haven't found an authoritative list, but this accounts for something
like 100 VBA functions. OTOH if the function is part of another library
- and Nz() is in Access.Application - it will only be available when the
query is executed from Access. (Use the Object Browser - hit F2 in the
VB Editor - to find out which functions belong where.)

As for Nz(), you can easily replicate it in Jet SQL with something like

IIf([Field] IS NULL, 0, [Field])

Thanks for your help John, I am actually almost there thanks to your
help, my only outstanding issue now, is that for some reason, the
vbscript called Sql query cannot process, a number of Access specific
functions, for instance the Nz() function, can I declare a library in
the declaration section of the script to solve this issue, if so,
would you know which library would contain, these basic functions.

thanks for your help
roger