Jack and Pat
drawbridgej at sympatico.ca
Wed Mar 26 14:29:52 CDT 2008
Mark,
I don't have any insight into the Passwords or ShellWait. However, if you do
the Dir first and get a list of Database names in a table, then when you do
the fjgetDbObjects
it will process the databases serially. You won't have async Shell going on
and thus no Wait issues..
This routine creates a catalog from multiple drives, directories/folders in
a single database. The catalog (Table) is very query-able.
Did you see my last post?
Also, I'm looking at a couple of routines at the moment to do the getting of
the *.mdb names into a Table without using the DOS Dir or the current
import.
Jack
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
Sent: Wednesday, March 26, 2008 2:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Search MDBs for specific table name
Jack,
Ok...turns out That was my fault...I forgot a CLOSEHANDLE in a different
module. But then I ran into a few other issues:
Before the issues...here is what I'm doing. I've created a form...it has a
lookup so you can choose your directory/folder...then it takes what you were
using in the command line...and uses ShellWait to execute.
***********
'Need to use Char34 to pass folder/file names with spaces
If InStr(CurrentDBDir, " ") = 0 Then
TempPath = CurrentDBDir & "TempLog.txt"
ShellCMD = "cmd /c dir " & Me!txtDir & "\*.mdb /s/b>" & TempPath
Else
TempPath = Chr(34) & CurrentDBDir & "TempLog.txt" & Chr(34)
ShellCMD = "cmd /c dir " & Chr(34) & Me!txtDir & "\*.mdb" & Chr(34) & "
/s/b>" & TempPath
End If
'Create text file with mdb names
ShellWait (ShellCMD)
***********
It creates the temp file in the same location as the db running it...them
it imports the file.
Next it runs your code to get the objects from each db...
then it runs my code to get the db size,created date, last updated date.
So...click the button...and you get all of the MDB info in the selected
directory.
Ok Issues:
1. ShellWait is not working...no errors...but it is trying to import the
file before it is created.
2. Ran into files with passwords...this is where I was getting caught in a
loop...bypassed with an "If Len(rz!Name) = 0 Then...create single row in
DBObjectsList stating PASSWORD...I can get the file info...just not the
Objects.
3. Instead of deleting records in DBObjectsList ...I select only the
records that don't have a created date ( yet)...
So I end up with a catalog from multiple directories/folders in a single
database.
Any thoughts on ShellWait or passwords? Currently I just bypass the ones
with passwords(get the file just not objects)...as for the
ShellWait...temporary fix...msgbox after each function...
Again...thanks for all of the help,
Mark A. Matte
----------------------------------------
> From: markamatte at hotmail.com
> To: accessd at databaseadvisors.com
> Date: Wed, 26 Mar 2008 16:08:08 +0000
> Subject: Re: [AccessD] Search MDBs for specific table name
>
>
> Jack,
>
> Thanks for all of your help.
>
> Here is a scenario. I run the code for folder "C:\Temp\"...it runs
fine...all expected records in both tables. I then delete all records in
both tables and run it again...this time it gets stuck in a loop as
described below.
>
> If I close/reopen the mdb...it will run fine. I have stepped
through...and the second time running...these lines
> step through....but the OtherDB and rz = 'Nothing' and the rz.EOF shows
"Object variable or With block variable not set"...it does not give an
error.
>
> Any thoughts?
>
> Thanks,
>
> Mark
>
> > Do While Not rs.EOF
> > Debug.Print Now & " - Processing " & rs!fullMDBCoord
> > strDBPath = rs!fullMDBCoord
> > ---Set OtherDB = OpenDatabase(strDBPath)---
> > ---Set rz = OtherDB.OpenRecordset(qsql)---
> > With DBObjList
> > Do While Not rz.EOF
>
>
> > From: drawbridgej at sympatico.ca
> > To: accessd at databaseadvisors.com
> > Date: Wed, 26 Mar 2008 11:51:08 -0400
> > Subject: Re: [AccessD] Search MDBs for specific table name
> >
> > Mark,
> >
> > The procedure works from a list of database names. When you say it only
runs
> > once, it is set up to work from a list of drive:\folder\xxxxxx.mdb
> > names.
> > It opens each of these databases in sequence, grabs all the relevant
info,
> > and plops it into the dbObjectsList Table.
> >
> > Did you get a list of databases via the DOS command(s)
> >
> > ' This DOS command, when run against the F:\ drive,
> > ' dir *.mdb /s/b>c:\MDBLog_FDrive.txt
> > ' will find all mdb files on F: drive and output fullpath and db name to
> > ' c:\MDBLog_FDrive.txt
> >
> > Jack
> >
> > -----Original Message-----
> > From: accessd-bounces at databaseadvisors.com
> > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A Matte
> > Sent: Wednesday, March 26, 2008 11:19 AM
> > To: Access Developers discussion and problem solving
> > Subject: Re: [AccessD] Search MDBs for specific table name
> >
> >
> > Jack, or anyone...
> >
> > This is a piece of the code you sent...works like a charm...but I can
only
> > run it once...then have to close the db...and I'm not sure why. I have
> > isloated a few lines where I believe the problem is. I don't get any
> > errors...it just gets stuck in a continuous loop. I have marked the
lines
> > below---I have stepped through...and the second time running...these
lines
> > step through....but the value = 'Nothing' and the rz.EOF shows
> >
> > Any ideas?
> >
> > Thanks,
> >
> > Mark A. Matte
> >
> > Do While Not rs.EOF
> > Debug.Print Now & " - Processing " & rs!fullMDBCoord
> > strDBPath = rs!fullMDBCoord
> > ---Set OtherDB = OpenDatabase(strDBPath)---
> > ---Set rz = OtherDB.OpenRecordset(qsql)---
> > With DBObjList
> > Do While Not rz.EOF
> >
> >
> >
> > ----------------------------------------
> >> From: markamatte at hotmail.com
> >> To: accessd at databaseadvisors.com
> >> Date: Tue, 25 Mar 2008 20:59:15 +0000
> >> Subject: Re: [AccessD] Search MDBs for specific table name
> >>
> >>
> >> Thanks for All of the feedback. Jack was nice enough to send a little
> > code...and then I added some I had from other projects. So far here is
what
> > I have.
> >>
> >> 1. Select your folder/directory
> >> 2. Puch the button...(just humor)
> >> Anyway...it collects the following: DB Name, DB location ,DB size ,DB
> > Created Date, DB LastUpdate, and all objects in DB.
> >>
> >> Just about meets my needs, but I was wondering as an Info gathering
> > tool...can anyone think of any other attribute that might be handy to
add
> > in?
> >>
> >> Thanks,
> >>
> >> Mark A. Matte
> >>
> >>>Mark,
> >>>
> >>>Mark,I am resending. The original email was held at
databaseadvisors.com
> > because of size.(seeattached email)>Anyway, others may have responded,
but
> > the attached DBObjects.zip is myapproach. Hope it is helpful and>timely.
> >>>
> >>>Jack
> >>
> >>
> >>> From: dwaters at usinternet.com
> >>> To: accessd at databaseadvisors.com
> >>> Date: Mon, 24 Mar 2008 20:34:53 -0500
> >>> Subject: Re: [AccessD] Search MDBs for specific table name
> >>>
> >>> Hi Mark,
> >>>
> >>> 1) Walk through the files in the folder using a collection of files
> >>>
> >>> 2) Try to import the object(s) you're looking for from each mdb. If
you
> >>> don't get an error, the object exists. (I've never tried this)
> >>>
> >>> You can probably use this example from Help:
> >>> DoCmd.TransferDatabase acImport, "Microsoft Access", _
> >>> "C:\My Documents\NWSales.mdb", acReport, "NW Sales for April", _
> >>> "Corporate Sales for April"
> >>>
> >>> 3) Store the name of the mdb's and objects in a table for later
> > reference.
> >>>
> >>> I want to know if you try this and if it works!
> >>>
> >>> Good Luck,
> >>> Dan
> >>>
> >>> -----Original Message-----
> >>> From: accessd-bounces at databaseadvisors.com
> >>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark A
Matte
> >>> Sent: Monday, March 24, 2008 3:29 PM
> >>> To: Access Developers discussion and problem solving
> >>> Subject: [AccessD] Search MDBs for specific table name
> >>>
> >>>
> >>> Hello All,
> >>>
> >>> I need to search a folder with 100's of Access DBs...and find the ones
> > with
> >>> a certain table,form,qry, or module name. Any ideas?
> >>>
> >>> Thanks,
> >>>
> >>> Mark A. Matte
> >>>
> >>>
> >>>
> >>> _________________________________________________________________
> >>> Watch "Cause Effect," a show about real people making a real
difference.
> >>> Learn more.
> >>> http://im.live.com/Messenger/IM/MTV/?source=text_watchcause
> >>> --
> >>> AccessD mailing list
> >>> AccessD at databaseadvisors.com
> >>> http://databaseadvisors.com/mailman/listinfo/accessd
> >>> Website: http://www.databaseadvisors.com
> >>>
> >>> --
> >>> AccessD mailing list
> >>> AccessD at databaseadvisors.com
> >>> http://databaseadvisors.com/mailman/listinfo/accessd
> >>> Website: http://www.databaseadvisors.com
> >>
> >> _________________________________________________________________
> >> Watch "Cause Effect," a show about real people making a real
difference.
> > Learn more.
> >> http://im.live.com/Messenger/IM/MTV/?source=text_watchcause
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> http://databaseadvisors.com/mailman/listinfo/accessd
> >> Website: http://www.databaseadvisors.com
> >
> > _________________________________________________________________
> > How well do you know your celebrity gossip?
> > http://originals.msn.com/thebigdebate?ocid=T002MSN03N0707A
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
>
> _________________________________________________________________
> Windows Live Hotmail is giving away Zunes.
>
http://www.windowslive-hotmail.com/ZuneADay/?locale=en-US&ocid=TXT_TAGLM_Mob
ile_Zune_V3
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
_________________________________________________________________
Watch "Cause Effect," a show about real people making a real difference.
Learn more.
http://im.live.com/Messenger/IM/MTV/?source=text_watchcause
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com