Re: Database Extract Automation

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: roger (divingroom6969_at_yahoo.com)
Date: 10/13/04 

Date: 12 Oct 2004 19:56:11 -0700

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 <j.mapSoN.nurick@dial.pipex.com> wrote in message news:<jg1gm0984bn8cktkgsoevo7snnct5pdcml@4ax.com>...
> 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
>
>
> On 7 Oct 2004 15:50:16 -0700, divingroom6969@yahoo.com (roger) wrote:
>
> >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

Relevant Pages

  • RE: How Do I Extract Data from my Form to load new table records?
    ... Steve: I have adapted your code as follows, but am ... Dim strSQL As String ... Open a "template table with a number of predefined "standard" rows ... file has 10 records in it, then every time I execute this, I would be adding ...
    (microsoft.public.access.formscoding)
  • Re: Execute Method for Find Object
    ... During each iteration of the While loop, if .Execute returns ... Dim strSearch As String, strTerm As String ... Dim intIndex As Integer, intCounter As Integer, intLast As Integer ...
    (microsoft.public.word.vba.beginners)
  • Re: Select several words
    ... .Execute findText:=oldPart, _ ... Word MVP web site http://word.mvps.org ... Dim ChangeDoc As Document, RefDoc As Document ...
    (microsoft.public.word.docmanagement)
  • Re: Looping!
    ... Well, McKirahan, this too takes about 30-35 seconds to execute! ... Now for the drop-downs - the no. ... the strSQL2 queries to populate the drop-downs. ... Dim strSQL1,strSQL2 ...
    (microsoft.public.inetserver.asp.db)