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