Inherited 2003 database - need help!

[turgid.dahlia]

Hello there. For better or worse, I am “The Computer Guy” at the place where I work, and am therefore considered the defacto resident expert in all things that have digital readouts, interpret information in a binary fashion, bleep, bloop, flash or churn, or are otherwise made of plastic and metals.

Due to this unenviable state of affairs, I have generously been forced to inherit a quite poorly-maintained and not-at-all-documented Access 2003 MDB database and I don’t know what to do about it.

It’s about 150 tables and an equal-ish number of queries, forms and reports, and it contains stacks of information (a lot of it duplicated in multiple tables) and references a lot ofVB code. It has been added to and “enhanced” in fits and starts for close to a decade. The story is it was intended as an “interim” system while a different tailor-made application was being built, but that blew up messily and expensively after 5 years of development, and now we’re back to using this old DB. The plot thickens when one realises that all the developers, administrators and other experts are gone gone gone. There is nobody to turn to, and the very few sparse scraps of “doco” I have been able to source are essentially useless.

Happily I do have *some* knowledge of the database, I’m aware of its purpose and have a pretty good idea of what’s in most of the tables and why it’s there and what it’s used for. I had been doing low-level administration (data correction/updates/fixes) on it prior to now.

Problem is I have no coding knowledge so when I look at the modules I really don’t understand any of it. I don’t get how it’s all hanging together. I’m pretty confident that over 50% of the tables are junk and no longer used but at the same time it’s an historical database with information going back decades and I don’t want to break it, obviously. Same goes for the forms and reports – hell, there are a bunch ofthem that are actually named “OLD VERSION” but I still don’t want to nuke anything just yet.

I’ve run the built-in Performance Analyser and implemented a lot of the more harmless suggestions, and have Compacted the DB (which made an incredible difference, shedding something like 200MB). I’ve been bouncing around on the ‘net looking for info – found a lot at the FMS “Total Access Analyser” site but most of the answers there were “buy this neat bit of software we made”, and we have zero budget for anything – and have stumbled across a couple of old Add-Ins to help with some analysis, but nothing good enough.

I’ve been toying with the idea of running a “/decompile” but I won’t do that until I’m a bit surer of things.

Basically I need to figure this blasted thing out, how it’s all hanging together, so I can Band-Aid it and get it working a bit better than it is (which is to say, not very). I am therefore on the market for absolutely any and all advice that you fine Access experts may have to offer in response to this tl;dr question:

Q: What do I need to do with a less-than-optimal Access 2003 database that I have inherited, in order to get it in respectable shape? Extra difficulty: zero VB knowledge.

Thanks in advance, and apologies for the length of my spiel, and also the annoyingness of the question itself.

[R_Badger]

Well, you are in an unenviable position, however my first piece of advice is to learn vb.

With it you can make your database do things that otherwise would be impossible.

As to learn how it works, my first suggestion would be to build it from scratch, i.e replicate it manually, literally, that why you can see why x sub here is run to do what etc.

I would also suggest a naming convention, it doesn't have to be an established one, just one that works for you. For instance I prefix tables, queries, forms and reports, but not controls or variables.

On a similar note avoid using reserved words i.e. date, name and also avoid spaces and most special characters (use a _ instead of a space).

I started off in a similar but not quite as awkward position to you last November, since then I have learnt a lot (especially SQL and VB) as well as the importance of data normalization, something I wish I had fully understood before I started not fully understood when I was halfway through!)

So I guess the best thing is to post back if you have a question/problem, and welcome!

[avarusbrightfyre]

Well, you are in an unenviable position, however my first piece of advice is to learn vb.

With it you can make your database do things that otherwise would be impossible.

As to learn how it works, my first suggestion would be to build it from scratch, i.e replicate it manually, literally, that why you can see why x sub here is run to do what etc.

I would also suggest a naming convention, it doesn't have to be an established one, just one that works for you. For instance I prefix tables, queries, forms and reports, but not controls or variables.

On a similar note avoid using reserved words i.e. date, name and also avoid spaces and most special characters (use a _ instead of a space).

I started off in a similar but not quite as awkward position to you last November, since then I have learnt a lot (especially SQL and VB) as well as the importance of data normalization, something I wish I had fully understood before I started not fully understood when I was halfway through!)

So I guess the best thing is to post back if you have a question/problem, and welcome!

I completely agree with this. Starting from scratch is the best way to make sure you know how everything works. I've had to do that several times, actually...

[orange]

I am not against starting from scratch, but I would strongly recommend that you do not delete anything.
If you feel you want to remove something, then rename it ie Oldname_xx (or something you can easily identify).

Identify a small useful project in order to learn some vba. Look at a few vba tutorials.
http://www.functionx.com/vbaccess2003/index.htm

One thing that might be useful is to write a small routine that identifies each module when it is executed.
Even if all it does is write the name of the procedure to the immediate window.

Another is a Logger routine. Such a routine allows you to identify a Log file, then call the Logger routine to write a time stamped record to the log file whenever you need to do so.


A utility (free) that I highly recommend is MZTools. It can help with error handling, what modules call what procedures... IN the code snippets below, the formatted comments at the start of the procedure is done via MZTools.
see http://www.mztools.com/v3/mztools3.aspx

If, as you say you know what the data base is and why it is there, then maybe you should work to build a data model.

Here is a small logger routine I wrote a while back

'---------------------------------------------------------------------------------------
' Procedure : Logger
' Author    : user
' Date      : 1/21/2009
' Purpose   : To write records to a LOG file using FileSystemObject.
'
'Parameters
' sLogName As String -- full path and file name of the log file
' sLogRec As String  -- record to be written to the log
'
' NOTE: Each log record has a timestamp appended
'
' Special Note/restriction:
'***** Must set a reference to MICROSOFT SCRIPTING RUNTIME ***
'---------------------------------------------------------------------------------------
'
Sub Logger(sLogName As String, sLogRec As String)
Dim tslog As TextStream
Dim fileLog As file
Dim i As Integer
Dim fso As FileSystemObject
   On Error GoTo Logger_Error

Set fso = New FileSystemObject
Set fileLog = fso.GetFile(sLogName) '"I:\wordtest\output\Results.log")
Set tslog = fileLog.OpenAsTextStream(ForAppending)
tslog.WriteLine Now() & vbTab & sLogRec
tslog.Close

   On Error GoTo 0
   Exit Sub

Logger_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure Logger"
End Sub

And here is a small routine I used to test that the Logger works.

'---------------------------------------------------------------------------------------
' Procedure : testLogger
' Author    : Jack
' Created   : 4/18/2011
' Purpose   : Sample procedure to use the Logger function
' Log file must exist before using Logger
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testLogger()
Dim mlog As String
Dim res As String
   On Error GoTo testLogger_Error

res = "*Pass "
mlog = "I:/wordtest/output/result_apr2011.txt"
Dim i As Integer
For i = 1 To 20
 Call Logger(mlog, res & i)
Next i

   On Error GoTo 0
   Exit Sub

testLogger_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testLogger "

End Sub

[turgid.dahlia]

Thank you everybody for your expert advice and insight. For me this is a pretty massively overwhelming headache of a thing and I'm tempted to just let it die a slow death, but that will hardly earn me any pats on the back, so I'm going to do what little things I can and take it very softly-softly and slowly implement the suggestions above as I get more confident with it all. Thanks again, and I'm quite sure I'll be back before long! :-)

[Rawb]

I'd like to make 1 additional suggestion: BACKUP YOUR DB FREQUENTLY!

I make daily "data" backups of all my DBs as well as an additional backup each time I make anything more than a minor change in the VBA/Forms/etc. I can't tell you how many times those backups have saved my tush!

[Yarbz]

Hey Turgid,
One more thing that helped me in my exact same problem, was to copy the old database to my files and then rename it so I didn't get them mixed up. Then play with it all I wanted, to try and figure it out. This way the original was never in harms way.
And believe it or not, recreating it piece by piece into a newer version, and having to figure out why it worked that way, and how to make the new one do the same, taught me almost everything I now know.
But it took months before I finally got it all worked out.
Then I replaced the table data in the new version with what was in the Old version (because it was still being used), and placed the new version on the shared drive, removing the old one to storage.
I've made 4 updates to the original since then. Just keep learning, it's a long road but it's worth it!
Yarbz (semi-novice)