0

I installed an access add-incalled "Search Wizard" by Dev Ashish and Terry Kreft. But it doesn't work right. It is supposed to search across the entire access database, including forms, code, queries, reports, etc. for a given string. However it either hangs or crashes/exits before completing. I am looking for something that performs this function but actually works, whether it is a piece of code or another existing utility

This is against Access 2016. We have an Access application that has a sql server back end. the SQL server is an express edition that is running out of space.So we need to change all database connection references to a new sql server / new database. We found one place in the code where it "relinks" all linked tables based on a local access table that specifies what database to connect to. It would be great if that was the only place that the server and database were referenced. However, I did a search using a binary file / text editor that searches the raw data in the mdb - and that finds 173 references to the database - but the data is essentially unusable in the format provided by the binary editor. I suppose I could use the search and replace capability of the binary file editor and that would probably work, but I would be "flying blind" and not really know what I was changing.

itguy2015
  • 3
  • 3
  • So DB with relinked tables actually works? Because it sounds like you create problem out of nothing. Try to compact and repair your db and perform "binary" search again. – viilpe Jun 06 '19 at 17:25
  • I don't understand your answer. DB with relinked tables works for most operations. But there are hundreds of operations, menu choices, functions, etc. in this application. Some don't work - so they must have hard coded references to the old database. I have to systematically find all the references and change them. I don't understand how compact and repair would fix anything if these are references in code that the original programmer created. This is an application that I inherited, and the former programmer is no longer available. – itguy2015 Jun 06 '19 at 18:23
  • I used Rick Fishers Find and Replace. V-Tools is another and a freebie. I don't know if either is compatible with Access 2016. – June7 Jun 06 '19 at 19:59
  • @June7 Thanks. I installed Rick Fishers and am testing now. Was going strong until it came to a report and froze. It warns that it is untested on Access 2016. If this doesn't work I will try v-tools, or try it anyway. – itguy2015 Jun 06 '19 at 20:49
  • The Rick Fishers utility works well. I think the one report it hangs on is corrupt, otherwise it works well. @viilpe you are right - after performing a compact and repair on the database and then doing the search in the binary file editor, the references are gone. I inherited this app and don't do access much, so I don't know the internals - why would there be "leftover" references to the old database strings - what does compact and repair do ? – itguy2015 Jun 07 '19 at 19:27
  • The application worked on *most* operations but one in particular didn't. The error message showed that it was attempting to authenticate to the old database. I am going to have the user try again next week now that it has been "repaired". The Rick Fishers find and replace does NOT find the references, but the binary editor does. – itguy2015 Jun 07 '19 at 19:28
  • @itguy2015 when you delete or modify some object in db (VBA code, queries etc) it is not actually disappearing. Compact and repair removes old objects and data. It is something like purging recycle bin. If the well known utility can not find wrong reference so it is hard to solve without looking at db. Maybe you are using fullpath as a search string then try to limit searchphrase to foldername or filename only. – viilpe Jun 09 '19 at 14:07
  • With`Application.SaveAsText`you can export queries, forms, reports and modules . See [Export All Database Objects Into Text Files](https://access-programmers.co.uk/forums/showthread.php?t=99179) – ComputerVersteher Jun 14 '19 at 14:12
  • @ComputerVersteher thanks, I can look into that – itguy2015 Jun 26 '19 at 16:39
  • @June7 I tried to get a licensed copy of Rick Fisher find and replace. I filled out his form and supplied my credit card on Friday and it is now Wednesday. No replies, no license file, no replies to my emails. Did you have any trouble getting a license. The licensed versions has features I really would like to have – itguy2015 Jun 26 '19 at 16:42
  • @viilpe it turns out that I was looking for the wrong string to find hard coded sql authentications. it turns out that there are 80+ forms where the prior developer created code to read a config file and then constructs a sql authentication connection string from that - we have to change it to windows authentication. so I have to change 80 forms. – itguy2015 Jun 26 '19 at 16:45
  • Sorry, I think Fisher has stopped supporting his app. We did pay for the license but it was years ago. I did not realize features were blocked. – June7 Jun 26 '19 at 17:08
  • @June7 - yes some features are blocked - I can get by without them, but would be very helpful. If he's not supporting it he shouldn't have an order button and form and the ability to send a credit card order into the ether. It would be great if someone could help me with this? – itguy2015 Jun 26 '19 at 18:37
  • Apparently, he has just never modified site page. I am surprised the site still works at all. Only recourse may be another 3rd party. I mentioned V-Tools but have never used. Whatever you do, do it on a copy of db. Interesting references located by binary editor could not be seen by Fisher's tool. – June7 Jun 26 '19 at 19:04
  • @June7 I was able to make do with the free version. Made the changes on a copy and they worked. I don't know why the Fisher tool couldn't see the references (these were different strings that turned out to not be relevant to the problem), but they did disappear after I did the compact and repair - I have no idea what that's about. – itguy2015 Jun 27 '19 at 21:19
  • Apparently, things are not completely 'deleted' until C&R. I suppose Fisher tool just doesn't look for those items in limbo. – June7 Jun 27 '19 at 21:24

0 Answers0