Mark A Matte
markamatte at hotmail.com
Wed Mar 26 13:54:27 CDT 2008
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_Mobile_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