[AccessD] Search MDBs for specific table name

Jack and Pat drawbridgej at sympatico.ca
Wed Mar 26 11:20:22 CDT 2008


Mark,
Not sure what "all expected records in BOTH tables" means.
Do you mean in dbObjectsList and A2Kmdbs?

When I run it I use the DOS Dir command

ie for all of C drive
c:\> dir *.mdb /s/b>c:\MDBLog_C_Drive.txt

then D drive
d:\> dir *.mdb /s/b>c:\MDBLog_D_Drive.txt

then H drive
h:\> dir *.mdb /s/b>c:\MDBLog_H_Drive.txt

Then
In access I 
 Delete the data from A2Kmdbs;
Then use the Get External Data, Import

And import these 3 .txt files into the existing Table  A2Kmdbs

Then run the proc  fJGetDBObjects

The proc will empty the dbObjectsList automatically.

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 12:08 PM
To: Access Developers discussion and problem solving
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



More information about the AccessD mailing list