Someone advise me please

[BTU_needs_assistance_43]

A few years ago a very skilled programmer started working on a database for
my section of the office, doing it on his own free time to help the company
out. He worked on it over the course of 5 years. Its pretty big and complex
and its obvious in every line of code he knew what he was doing. But before
he could finish the last section he left. Now 3 years later, I'm tasked with
trying to resurect his work. I'm at best a novice at VBA programming and in
the work I've accomplished so far have had to teach myself many many things.
What I need advice on is this... there is a rough skeleton of code for this
last section of the database but even this patchwork he left behind is far
beyond my level of coding. I've got the general code that will accomplish
what the database is supposed to do, however I need to either A: try and
write my code into the skeleton he left and form my code to fit into his
forms, subforms, queries, tables, and reports OR B: I can cut out his work on
this section of the database and build my own monster. Below I will explain
the general purpose of the database and how it works to help clarify...
Please give me advice on how I should proceed from here.

The database is a huge library of information on the blasting we do.
Originally he had it draw in information from a single Excel file with
hundreds of worksheets into the database, now there are hundreds of Excel
files that need to be imported because the reporting proceedure has changed.
The basic forms that hold the information are simple enough and can be left
but he uses forms and tables to set up the file and folder names of the Excel
file to draw it in and then write the data to a table. He uses complex
intergrated queries to organize the information before it is displayed as
well. I've got the basic beginner/novice level coding that I've tested out in
a practice database that DOES perfom the tasks I need the program to do.
However using it on a basic DB I made in half an hr and intergrating it into
this already huge system are 2 different things. What is my best course of
action here?

 

[ryguy7272]

One thing at a time. Let's get the low hanging fruit and just start with
importing those 'hundreds of Excel
files'. Check this out:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

You will (most likely) want to import those different Worksheets into
different Tables. The code below will do just that.
Backup your DB before running the code below:

Sub ImportAllSheetsIntoMultipleTables()
'Import Data from All Worksheets in a single EXCEL File into Separate Tables
via TransferSpreadsheet
'Generic code to import the data from all worksheets in a single EXCEL file.
Each worksheet's data will be imported into a separate table whose name is
'tbl' plus the worksheet name (e.g., "tblSheet1").
Dim blnHasFieldNames As Boolean, blnEXCEL As Boolean, blnReadOnly As Boolean
Dim lngCount As Long
Dim objExcel As Object, objWorkbook As Object
Dim colWorksheets As Collection
Dim strPathFile As String
Dim strPassword As String

' Establish an EXCEL application object
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set objExcel = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
On Error GoTo 0

' Change this next line to True if the first row in EXCEL worksheet
' has field names
blnHasFieldNames = False

' Replace C:\Filename.xls with the actual path and filename
strPathFile = "C:\Path\filename.xls"

' Replace passwordtext with the real password;
' if there is no password, replace it with vbNullString constant
' (e.g., strPassword = vbNullString)
strPassword = "passwordtext"

blnReadOnly = True

' Open the EXCEL file and read the worksheet names into a collection
Set colWorksheets = New Collection
Set objWorkbook = objExcel.Workbooks.Open(strPathFile, , blnReadOnly, , _
strPassword)
For lngCount = 1 To objWorkbook.Worksheets.Count
colWorksheets.Add objWorkbook.Worksheets(lngCount).Name
Next lngCount

' Close the EXCEL file without saving the file, and clean up the EXCEL objects
objWorkbook.Close False
Set objWorkbook = Nothing
If blnEXCEL = True Then objExcel.Quit
Set objExcel = Nothing

' Import the data from each worksheet into a separate table
For lngCount = colWorksheets.Count To 1 Step -1
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
"tbl" & colWorksheets(lngCount), strPathFile, blnHasFieldNames, _
colWorksheets(lngCount) & "$"
Next lngCount

' Delete the collection
Set colWorksheets = Nothing

' Uncomment out the next code step if you want to delete the
' EXCEL file after it's been imported
' Kill strPathFile

End Sub


HTH,
Ryan---

 

[John W. Vinson]

What is my best course of
action here?

Panic. <bg>

Well, not really... but in my experience restructuring somebody else's big
complex project is AT LEAST twice as difficult as recreating the same
functionality from scratch. You've got five steep hills to climb:

1. Getting up to speed on the relational paradigm and table normalization.
2. Learning about user interface design.
3. Learning Access' VBA and SQL dialects.
4. Understanding the complexities of your former coworker's structure.
5. Restructuring his database.

Good luck... don't hesitate to ask here for help but don't expect a new
database immediately!!

 

[tina]

suggest you proceed with EXTREME caution. you might want to make a copy of
the "live" database, and make your changes in the *copy only*. then run both
dbs for awhile, letting the copy "mirror" what the working db is doing; you
want to 1) make sure your code works correctly, and 2) make sure it doesn't
mess up something else that was working okay - *before* you implement any
changes in your employers' live database.

hth

 

[Jack Leach]

This may not be quite as bad as it seems. If the existing db already works
correctly except what you need to add (you don't have a requirement to go
through and proof this other person's work), you may be able to more or less
disregard everything already there and concentrate only on what you need to
do.

Rather than thinking in terms of "finishing the monster", think in terms of
"making an addin for the monster". Chances are, there's probably a good
amount of data already in tables from the previous project.

Without knowing the details, my approach would be something like this:
1) Pretend the existing project doesn't exist for the moment...
2) Know what you need for your users. Figure out everything that the end
user needs, and come up with some form/report concepts to handle the interface
3) Find out what data you need to support your form/report concepts.
Disregarding anything in place, just make a list of stuff required for your
'addin'
4) After you know what you need, find out what's available. This will
probably be the most difficult part... you will need at aleast a basic
understanding of where the data is kept, but luckily all his
forms/reports/modules etc can probably be disregarded completely.
5) Get the general ideal of the previous naming scheme and possibly change
it around a little to help segregate your app from his app... this may make
the mental challenge of it a bit easier.

At this point you should be left with some decent workable information:
what you want, what you need to get what you want, what you have already
availble to get what you need, and what you will have to create to complete
it.

So basically what you would be working with is a half-existing datasource
(his tables), with your application that will just happen to merge with the
existing (a slightly different naming scheme may be a lifesaver here as well).

Admittedly, the end product is a project that has been "patched" together,
major part A and minor part B that could probably work better if it was all
one part, but this seriously beats rewriting an entire app. If done well,
the users may never know the difference (unfortunately I've had to do this
more than once...)

Of course, this also assumes that the segment you need to add doesn't rely
on heavy integration with what's already there (other than data, obviously).
But in this case, there's usually a workaround thats still easier than doing
the entire thing over.

Hopefully what I'm trying to explain is somewhat along the lines of what I'm
actually thinking here.

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)