Is there an Access genius out there that can solve this problem? (1 Viewer)

The_Doc_Man said:

Divide and conquer this one.

First, build an empty table with the required data types and column names. Erase its contents before each run. (More about this later.)

Then run a sequence of APPEND queries, each of which focuses on the correct transitions and computations and formatting for one and only one individual type of thing. Yes, having multiple queries is a pain in the toches, but it will also be simple to build, simple to maintain. No IIF needed anywhere if you instead focus via a narrowly selected WHERE clause. Yeah, you have to write a lot of queries. But you will be writing only very simple queries with simple WHERE clauses. You DON'T CARE if you have fifty of them once you have fifty of them done. (And you probably don't have as many as 50 to be written.)

Build a code module that activates each query in your sequence, with error checking and any other bells and whistles you need. In fact, since you import your data, you can easily include that import process (and any "data massaging") as part of the sequence.

When that code blip is finished, your previously empty table is good to go.

Now, as to erasing a table frequently: There is a down side to this approach that can be fixed by more work in the automation or by requiring occasional manual intervention. When you erase a table (or when you update it), you cause deleted records to start accumulating, which leads to database bloat. Bloat means that your db size grows even though you might have the same number of active records today as you had last week. This happens and is a part of every database system I've ever seen including Paradox, SQL Server, Access, Informix, and ORACLE.

To counteract this bloat, you need to occasionally perform a process of compacting and repairing the db. That gets rid of the obsoleted records marked as "deleted." If you do this manually, you need to make a backup copy of the db BEFORE you attempt the C&R because, well... stuff happens. The backup copy made beforehand lets you just toss the damaged copy and start another C&R.

However, there is another approach that programmatically is harder but which prevents the bloat from being an issue. This table used for gathering records is actually a scratch table that has only one purpose in its existence - a staging area from which a single export will occur. There IS such a thing as making an empty version of this table in a third file that you will eventually use as a back end. (Bear with me here...)

When launching the db, make a batch file to delete a working copy of that third file and make a new version of that file from the empty (and virgin) template file. The virgin copy has zero bloat. You CAN statically link to the working copy from the FE. You can erase this copy and replace it without harm because native access linked tables operation through file specs, not internal file ID numbers. Therefore, if you replace the file using the same name, the FE doesn't know the difference.

So... launch via a script. Have the script maintain this working third file, after which the same script can launch access when all files are ready. You'll see a momentary blip while the file stuff happens, but that will be quick because it is purely internal to your workstation, no network involved.

Click to expand...

MajP said:

Do not think this answers your question, but this could potentially have saved you hours and hours of work. Those queries are amazing that you could get them to work. Try this code and in a query. May need some tweeking, but appears to give the same results as your Body.

Code:Copy to clipboard

select GetHTML([ProductCode]) as Body from [Product Data]

Code:Copy to clipboard

Public Enum TagType
  P_BR = 1
  P_P = 2
  end_P = 3
  end_BR = 4
End Enum

Public Function GetHTML(ProductCode As String) As String
  Dim rs As DAO.Recordset
  Dim fld As DAO.Field
  Dim comp As String
  Dim i As Integer
  
  Set rs = CurrentDb.OpenRecordset("Select * from [Product Data] where ProductCode = '" & ProductCode & "'")
  
  GetHTML = Tag(rs!ShortDescription, P_BR) & Tag(rs!longDescription, end_P)
  
  For i = 11 To 66 'these are the COMP fields
    'Debug.Print rs.Fields(i).Name
    If Not IsNull(rs.Fields(i).Value) Then
      comp = rs.Fields(i).Name
      comp = Trim(Replace(comp, "comp", ""))
      comp = StrConv(comp, vbProperCase)
      comp = comp & " - "
      comp = comp & rs.Fields(i).Value
      comp = Tag(comp, end_BR)
      'Debug.Print comp
      GetHTML = GetHTML & comp
    End If
  Next i
  If Right(GetHTML, 4) <> "</p>" Then GetHTML = GetHTML & "</p>"
  For i = 67 To 73
    If Not IsNull(rs.Fields(i).Value) Then
      comp = rs.Fields(i).Value
      comp = Tag(comp, P_P)
      Debug.Print comp
      GetHTML = GetHTML & comp
    End If
  Next i
  If Right(GetHTML, 4) <> "</p>" Then GetHTML = GetHTML & "</p>"
End Function

Public Function Tag(ByVal Text As Variant, TagType As TagType) As String
  Const P = "<p>"
  Const endP = "</p>"
  Const BR = " <br />"
  Text = Nz(Text, "")
  Select Case TagType
  Case P_BR
    Tag = P & Text & BR
  Case P_P
    Tag = P & Text & endP
    Debug.Print Tag
  Case end_P
    Tag = Text & endP
  Case end_BR
    Tag = Text & BR
  End Select
End Function

Click to expand...

arnelgp said:

the link says the Body (HTML) can also be a plain text.

Click to expand...