Access Report Help

  • Thread starter Thread starter Shawn Johnson
  • Start date Start date
S

Shawn Johnson

Ok, this may be one HECK of a tall order I am looking for, but I figured I'd
give this question a shot to the populace...

I am currently stuck being a data coordinator for transforming my company
into the SAP ERP system. The ERP software my company currently uses is
rather basic, but I can easily connect to the data with Access and ODBC.

Here is what I am trying to do:

I've created an Access database and report that will grab needed information
from our current system using ODBC. If I wanted a printed out report, this
thing would be a done deal and look nice too. The catch is this:

1) This is a report for about 1400 products with information
2) We'd like to have each product have it's own separate FILE, preferrably
in EXCEL format, because at some point some editing of the file will be
required.

Is there a way to be able to create an output file after each change in
header information, preferrably in an EXCEL or WORD format? If I think of
how a report would look, instead of a new product starting on a new page, the
output would be dumped as a sequenced file (FILEXXX.XLS) and the next record
would create another new file (FILEYYY.XLS) and so on.

I hope this all doesn't sound confusing... it makes sense to me because I am
close to the data...

Can this be done? If I could figure out a way to do this, it will save me a
lot of time and anguish... but I am thinking this might be a task that's too
great without massive programming of some sort.

Any input would be greatly appreciated!
 
Although possible, it is not practical, and would not really be the way to do
it. There is no need to involve your report with dumping data to Excel or to
creating tables.

First, having a different table for each product violates good database
design. They should be all in one table with a field that identifies the
product.

Second, unless there is some other reason to do so, you don't really need to
create tables in the Access mdb. In fact, it would not be that good an idea.
If you want to create an Excel workbook for each product, you can do so
directly from your ERP database. Just use the TransferSpreadsheet method or
Action (if you use a macro) and use a parameterized query that will prompt
for the product code.
 
Hi Shawn,

First of all - 1400 files containing one product worth of data each (to be ?manually? edited at some point in the future) is out there in the twilight zone, it's like scrubbing floors with a toothbrush or digging ditches with a spoon. Maybe this provides a satisfying career for Larry the Cable guy's starving cyber-pygmies in New Guinea? <just kidding around - no disrespect to pygmies or New Guinea intended - if a Larry the Cable Guy fan is offended - get over it - get er done.>

I would recommend making a folder to catch these files - 1400 is a lot of little files, be good to keep them in a kennel where you can delete them en masse, or maybe process them using code. You really, really don't want to manually edit 1400 files. Assuming you can do the edit and save in 3 minutes per file, that's 70 hours of miserable, grinding away work.

However, what do I know? SAP is a master at reverse-speak - where limitations are declared features, rigid procedures that demand you work their way are declared to be flexibility and best practices means we are doing it to you for your own good, trust SAP - you are getting sleepy... sleepy... SAP knows best.

Yeah, I know, you are just the coordinator; I'm just venting.

Having suffered thru my venting, here's a solution (if you really gotta do this).

I presume that you have a recordset containing the information you wish to 'publish', right?

You will need to replace the Const declared values with the appropriate strings from your setup.

I call the recordset mytable here - but it could be a named query.

I presume it includes a key field, a unique row identifier? (I will call the variable mySKU below).

Since I don't know whether this is numeric or text (the key field) I will include the syntax for both in the function, you will need to UNCOMMENT the appropriate line depending on whether the product key field is a numeric value or a text /string value. Look for the ###### markers.

Making 1400 files this will take forever (well, at least several minutes) to run. I took the path of least resistance, not the most efficient path. For a one time task, efficiency isn't usually a first priority, as long as we get there. I inserted a DoEvents so that the system doesn't seem to be locked up, put an on the fly record count in the status bar, and a popup will tell you when it's done.

Just in case you haven't done much work with this kind of sub. Create a module and paste this code into that code module. (Not a class module, just plain code module.) If you want to run this from a macro - set the macro action to run code and type in this function name.

If you stop this function in mid-execution, it will break the next time you run it because it will find TheProduct query already exists. If this happens, just delete "The Product" query and it will be okay.

Hope this helps,
Gordon

Public Sub ExportSingles()

'insert your path and folder here - retain the trailing backslash
Const myFolder As String = "C:\MyFolderNameGoesHere\"

'insert the name of your table or query here
Const myTable As String = "myTableNameGoesHere"

'insert the name of your productid/SKU unique row identifier field here
Const mySKU As String = "mySKUFieldNameGoesHere"

Dim strSQL As String
Dim myCount As Long
Dim myProgress As Long
Dim rs As DAO.Recordset
Dim qd As DAO.QueryDef

strSQL = "Select * From " & myTable & ";"
Set rs = CurrentDb.OpenRecordset(myTable)
Set qd = CurrentDb.CreateQueryDef("TheProduct", strSQL)
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
myCount = rs.RecordCount
myProgress = 0
SysCmd acSysCmdInitMeter, "Writing Product Files...", myCount

Do While Not rs.EOF

'######## if row identifier is text - uncomment the next line
'strSQL = "Select * From " & myTable & " Where " & mySKU & "= '" & rs.Fields(mySKU) & "';"

'######## if row identifier is numeric - uncomment the next line
'strSQL = "Select * From " & myTable & " Where " & mySKU & "= " & rs.Fields(mySKU) & ";"

qd.SQL = strSQL
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, "TheProduct", myFolder & rs.Fields(mySKU) & ".xls", True
rs.MoveNext
myProgress = myProgress + 1
SysCmd acSysCmdUpdateMeter, myProgress
DoEvents
Loop
Else
MsgBox "No Records Found!", vbCritical, "NO DATA TO PROCESS"
End If
MsgBox myCount & " Files written to the " & myFolder & " folder.", vbInformation, "Export Completed"
SysCmd acSysCmdRemoveMeter
SysCmd acSysCmdClearStatus
Set rs = Nothing
CurrentDb.QueryDefs.Delete ("TheProduct")

End Sub
 
Back
Top