Hi all,

I have the below code as a public function in Access 2000 .This is the
*only* thing this particular mdb does - autoexec macro calls this function
and at the end of the code Access exits. Just a quick conversion utility,
really. Now I'm told some users do not have access installed so the powers
that be would like to know how difficult it would be to bring this into a
self-contained executable.
The function references one stored query and one table (which is deleted at
start and then recreated) so I don't think there would be a lot to "bring
into" the code but don't know how to go about even starting such a task. I
have Visual Studio .Net 2002 for a dev invironment outside of Access.

Any suggestions or links would be welcome!

TIA,
Susan

Function Create_Pivot()

   MsgBox "This utility will take existing Maintenance Lists " & vbCrLf &
"(Excel file format please!)" _
   & vbCrLf & "from MML Export and convert to Pivot table." & vbCrLf & "To
be used in Generic Plan Development."

   Dim strFile As String
   Dim strFilter As String
   Dim tblName As String
   Dim db As DAO.Database
   Dim fldNew As DAO.Field
   Dim tbl As DAO.TableDef

   strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.xls)", "*.xls")
   strFile = ahtCommonFileOpenSave(Filter:=strFilter, OpenFile:=True,
DialogTitle:="Select import file")
   If strFile = "" Then Exit Function

   DoCmd.DeleteObject acTable, "tblTemp"

   DoCmd.TransferSpreadsheet acImport, , "tblTemp", strFile, True
   Set db = CurrentDb()
   Set tbl = db.TableDefs("tblTemp")
   Set fldNew = tbl.CreateField("Freq", dbText, 50)
   tbl.Fields.Append fldNew

   Dim sql As String
   DoCmd.SetWarnings False
   sql = "UPDATE tblTemp SET units = " & "'" & "Y" & "'" & _
       " WHERE tblTemp.units =" & "'" & "Year" & "'"
   DoCmd.RunSQL sql

   sql = "UPDATE tblTemp SET units = " & "'" & "M" & "'" & _
       " WHERE units =" & "'" & "Month" & "'"
   DoCmd.RunSQL sql
   sql = "UPDATE tblTemp SET units = " & "'" & "W" & "'" & _
       " WHERE units = " & "'" & "Week" & "'"
   DoCmd.RunSQL sql
   sql = "UPDATE tblTemp SET units = " & "'" & "HR" & "'" & _
       " WHERE units = " & "'" & "Running Hour" & "'"
   DoCmd.RunSQL sql
   sql = "UPDATE tblTemp SET Freq = units & interval"
   DoCmd.RunSQL sql
   DoCmd.SetWarnings True

   MsgBox "Please select filename and location to save to!"
   DoCmd.OutputTo acOutputQuery, "qryXtab_tblTemp", acFormatXLS, , True

   DoCmd.Quit
End Function