Mark A Matte
markamatte at hotmail.com
Wed Mar 26 15:47:03 CDT 2008
Jack,
I did not see you last post...I sent you a sample MDB offline.
In a nutshell...I took your process...shelled out the command line part...kept ALL data in the tables instead of deleting for each directory...added some file attributes(size and dates)...and ended up with a cataolog of all mdb's on our network...with a list of objects...that I can easily reference and search.
Passwords...I'm working on.
ShellWait...I put a crude 'pause'/timer...turns out that after the ShellWait...windows still had a grip on the text file for a few seconds.
********
ShellWait (ShellCMD)
'MsgBox "File is being created."
MyWaitLine:
If MyWait = 1 Then GoTo MyWaitLine
Dim MP_Now
MP_Now = Now
MyPause:
If Now() < DateAdd("s", 3, MP_Now) Then GoTo MyPause
**********
Thanks again for all of your help.
I would like to add some comments as to the author on the module you sent. Please let me know if/what you would like there.
Thanks,
Mark A. Matte
> From: drawbridgej at sympatico.ca
> To: accessd at databaseadvisors.com
> Date: Wed, 26 Mar 2008 15:29:52 -0400
> Subject: Re: [AccessD] Search MDBs for specific table name
>
> 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
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
_________________________________________________________________
Test your Star IQ
http://club.live.com/red_carpet_reveal.aspx?icid=redcarpet_HMTAGMAR