Export From Access

Is there a way to export a table to a text file from Access WITHOUT it being installed on the machine the VB6 program is running on???,
bja1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

bja1Author Commented:
Edited text of question.
0
watyCommented:
' #VBIDEUtils#************************************************************
' * Programmer Name  : Waty Thierry
' * Web Site         : www.geocities.com/ResearchTriangle/6311/
' * E-Mail           : waty.thierry@usa.net
' * Date             : 28/06/99
' * Time             : 12:46
' **********************************************************************
' * Comments         : Export sql data to a CSV File
' *
' *
' **********************************************************************

Public Function CSVExport(db As DAO.Database, sSQL As String, sDest As String) As Boolean
   ' #VBIDEUtils#************************************************************
   ' * Programmer Name  : Waty Thierry
   ' * Web Site         : www.geocities.com/ResearchTriangle/6311/
   ' * E-Mail           : waty.thierry@usa.net
   ' * Date             : 25/11/98
   ' * Time             : 15:06
   ' * Module Name      : Database_Module
   ' * Module Filename  : Database.bas
   ' * Procedure Name   : CSVExport
   ' * Parameters       :
   ' *                    db As DAO.Database
   ' *                    sSQL As String
   ' *                    sDest As String
   ' **********************************************************************
   ' * Comments         : Export sql data to a CSV File
   ' *
   ' *
   ' **********************************************************************

   Dim record        As Recordset
   Dim nI            As Long
   Dim nJ            As Long
   Dim nFile         As Integer
   Dim sTmp          As String
   
   On Error GoTo Err_Handler
   
   Set record = db.OpenRecordset(sSQL, DAO.dbOpenDynaset, DAO.dbReadOnly)
   
   ' *** Open output file
   nFile = FreeFile
   
   Open sDest For Output As #nFile
   
   ' *** Export fields name
   For nI = 0 To record.Fields.Count - 1
      sTmp = "" & (record.Fields(nI).Name)
      Write #nFile, sTmp;
   Next
   Write #nFile,

   If record.RecordCount > 0 Then
      record.MoveLast
      record.MoveFirst
   
      For nI = 1 To record.RecordCount
         For nJ = 0 To record.Fields.Count - 1
            sTmp = "" & (record.Fields(nJ))
            Write #nFile, sTmp;
         Next
         Write #nFile,
         record.MoveNext
      Next
   End If
   
   Close #nFile
   CSVExport = True
   
   Exit Function
   
Err_Handler:
   MsgBox ("Error: " & Err.Description)

   CSVExport = False
   
End Function

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
watyCommented:
Also some other links :

http://www.smithvoice.com/vb5expt.htm

This code :

' #VBIDEUtils#************************************************************
' * Programmer Name  : Waty Thierry
' * Web Site         : www.geocities.com/ResearchTriangle/6311/
' * E-Mail           : waty.thierry@usa.net
' * Date             : 05/11/1999
' * Time             : 11:13
' * Module Name      : FrmDataExportType
' * Module Filename  : FrmDataExportType.frm
' **********************************************************************
' * Comments         :
' *
' *
' **********************************************************************

Private Sub Command1_Click()
   Dim ExportString, ExportedFields
   Select Case List1.ListIndex
      Case 0 ' ACCESS
         ExportString = "DATABASE="
      Case 1 '  dbase iii
         ExportString = "[dBASE III;Database="
      Case 2 '  dbase iv
         ExportString = "[dBASE IV;Database="
      Case 3 '  dbase 5
         ExportString = "[dBASE 5.0;Database="
      Case 4 '  paradox 3.x
         ExportString = "[Paradox 3.x;Database="
      Case 5 '  paradox 4.x
         ExportString = "[Paradox 4.x;Database="
      Case 6 '  paradox 5.x
         ExportString = "[Paradox 5.x;Database="
      Case 7 '  excel 3.0
         ExportString = "[Excel 3.0;Database="
      Case 8 '  excel 4.0
         ExportString = "[Excel 4.0;Database="
      Case 9 '  excel 5.0
         ExportString = "[Excel 5.0;Database="
      Case 10 ' excel 95
         ExportString = "[Excel 5.0;Database="
      Case 11 ' excel 97
         ExportString = "[Excel 8.0;Database="
      Case 12 ' lotus 123 wks wk1
         ExportString = "[Lotus WK1;Database="
      Case 13 ' lotus 123 wk3
         ExportString = "[Lotus WK3;Database="
      Case 14 ' lotus 123 wk4
         ExportString = "[Lotus WK4;Database="
      Case 15 ' HTML
         ExportString = "[HTML Export;Database="
      Case 16 ' Text
         ExportString = "[TEXT;Database="

      Case 17 ' OBDC
         MsgBox ("Currently not availiable")
         Exit Sub
      Case 18 ' Microsoft Exchange
         MsgBox ("Currently not availiable")
         Exit Sub

   End Select

   For x = 0 To FrmDataFields.ExportList.ListCount - 1
      If x < FrmDataFields.ExportList.ListCount - 1 Then
         ExportedFields = ExportedFields & "[" & FrmDataFields.ExportList.List(x) & "],"
      End If
      If x = FrmDataFields.ExportList.ListCount - 1 Then
         ExportedFields = ExportedFields & "[" & FrmDataFields.ExportList.List(x) & "]"
      End If
   Next x

   'Fix empty field at end of string.

   If Mid$(ExportedFields, (Len(ExportedFields) - 2)) = ",[]" Then
      ExportedFields = Mid$(ExportedFields, 1, (Len(ExportedFields) - 3))
   End If

   MDIForm1.CommonDialog.ShowSave

   On Error Resume Next
   Kill (StripFileName(MDIForm1.CommonDialog.filename) & "\schema.ini")
   On Error GoTo 0

   If Len(MDIForm1.CommonDialog.filename) > 0 Then

      Select Case List1.ListIndex
         Case 7, 8, 9, 10, 11
            ExportString = ExportString & MDIForm1.CommonDialog.filename & ExcelExport(MDIForm1.CommonDialog.filename)
         Case Else
            ExportString = ExportString & ExportFileName(MDIForm1.CommonDialog.filename)
      End Select

      On Error GoTo ExportError
      Dim db As Database
      Set db = Workspaces(0).OpenDatabase(DataLocation)
      Screen.MousePointer = 11
      Me.Enabled = False
      DoEvents
      db.Execute "SELECT " & ExportedFields & " INTO " & ExportString & " FROM [" & FrmDataExport.lstTables.Text & "]"
      db.Close
      Screen.MousePointer = 0
      Me.Enabled = True
      MsgBox ("Export of data to: " & MDIForm1.CommonDialog.filename & " is complete")
ExportError:
      If Err.Number = 3010 Then
         msg = MsgBox(MDIForm1.CommonDialog.filename & " already exists.  Overwrite it?", vbYesNo, "Overwrite File?")
         Select Case msg
            Case vbYes
               Kill MDIForm1.CommonDialog.filename
               Screen.MousePointer = 11
               Me.Enabled = False
               DoEvents
               db.Execute "SELECT " & ExportedFields & " INTO " & ExportString & " FROM [" & FrmDataExport.lstTables.Text & "]"
               db.Close
               Screen.MousePointer = 0
               Me.Enabled = True
               MsgBox ("Export of data to: " & MDIForm1.CommonDialog.filename & " is complete")

            Case vbNo
               Exit Sub
         End Select
      Else
         If Err.Number <> 0 Then
            MsgBox ("SQL Statement: " & vbCrLf & "SELECT " & ExportedFields & " INTO " & ExportString & " FROM [" & FrmDataExport.lstTables.Text & "]" & vbCrLf & vbCrLf & Err.Number & vbCrLf & Err.Description)
         End If
      End If
   End If
   Screen.MousePointer = 0
   Unload FrmDataExport
   Unload FrmDataFields
   Unload FrmDataExportType

End Sub

Private Sub Command2_Click()
   FrmDataFields.Enabled = True
   Unload Me
End Sub

Private Sub Form_Load()
   Me.Top = FrmDataFields.Top + 400
   Me.Left = FrmDataFields.Left + 400

   List1.AddItem "Microsoft Jet (Access)"
   List1.AddItem "dBASE III"
   List1.AddItem "dBASE IV"
   List1.AddItem "dBASE 5"
   List1.AddItem "Paradox 3.x"
   List1.AddItem "Paradox 4.x"
   List1.AddItem "Paradox 5.x"
   List1.AddItem "Excel 3.0"
   List1.AddItem "Excel 4.0"
   List1.AddItem "Excel 5.0"
   List1.AddItem "Excel 95"
   List1.AddItem "Excel 97"
   List1.AddItem "Lotus 123 WKS and WK1"
   List1.AddItem "Lotus 123 WK3"
   List1.AddItem "Lotus 123 WK4"
   List1.AddItem "HTML"
   List1.AddItem "Text (Comma Delimited)"
   List1.AddItem "OBDC"
   List1.AddItem "Microsoft Exchange"
End Sub

Private Sub List1_Click()
   Command1.Enabled = True
End Sub
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

bja1Author Commented:
Waty answer works great. for another 50 pts how would I do the same but export with a xls format I have tried the code in your comment but I cannot get it to to work.
0
watyCommented:
' #VBIDEUtils#************************************************************
' * Programmer Name  : Melvin Tucker
' * Web Site         : www.geocities.com/ResearchTriangle/6311/
' * Date             : 23/08/99
' * Time             : 07:24
' **********************************************************************
' * Comments         : Class for working with Microsoft Excel 97 through Automation
' *
' *
' **********************************************************************

Private m_objExcel As Excel.Application
Private m_objWorkbook As Excel.Workbook

Public Property Get AppExcel() As Excel.Application
   ' Returns: A handle to the current instance of Excel
   Set AppExcel = m_objExcel

End Property

Public Property Get CurWorkbook() As Excel.Workbook
   ' Returns: A handle to the currently open workbook

   Set CurWorkbook = m_objWorkbook

End Property

Public Sub CloseExcel()
   ' Comments  : Closes Excel
   ' Parameters: None
   ' Returns   : Nothing
   '
   On Error GoTo PROC_ERR

   m_objExcel.Quit

   Set m_objExcel = Nothing

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "CloseExcel"
   Resume PROC_EXIT

End Sub

Public Sub CloseWorkbook( _
   fSave As Boolean)
   ' Comments  : Closes the current workbook
   ' Parameters: fSave - True to save changes, False to discard changes
   ' Returns   : Nothing
   '
   On Error GoTo PROC_ERR

   m_objWorkbook.Close SaveChanges:=fSave

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "CloseWorkbook"
   Resume PROC_EXIT

End Sub

Public Sub CreateTableFromAccess( _
   strDatabase As String, _
   strDataSource As String, _
   fFieldNames As Boolean, _
   Optional varMaxRecs As Variant)
   ' Comments  : Gets the contents of an Access table or query into
   '             the current document
   ' Parameters: strDatabse - full path and name of the Access database
   '             you want to read from
   '             strDataSource - name of a table or query in the database
   '             to read records from
   '             fFieldNames - True to put the field names in the first
   '             row, false otherwise.
   '             varMaxRecs - optional: set to the maximum number of
   '             records you want to retrieve. To include all records,
   '             don't specify this argument.
   ' Returns   : Nothing
   '
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim intCounter As Integer
   Dim intFieldCount As Integer
   Dim lngRowCount As Long
   Dim varField As Variant
   Dim intRow As Integer
   Dim intCol As Integer

   On Error GoTo PROC_ERR

   ' Open the database objects
   Set dbs = DAO.DBEngine.OpenDatabase(strDatabase)
   Set rst = dbs.OpenRecordset(strDataSource)
   intFieldCount = rst.Fields.Count

   intRow = 1
   intCol = 1

   ' Add the field names if specified
   If fFieldNames Then
      For intCounter = 1 To intFieldCount
         m_objWorkbook.ActiveSheet.Cells(1, intCounter).Value = _
            rst.Fields(intCounter - 1).Name
      Next intCounter
   End If

   ' Start inserting data on the second row of the table
   lngRowCount = 2

   With rst
      ' Loop through all records
      Do Until .EOF

         For intCounter = 1 To intFieldCount
            ' Add each fields value
            varField = .Fields(intCounter - 1).Value

            ' Handle null field values
            If IsNull(varField) Then
               varField = "<null>"
            End If

            m_objWorkbook.ActiveSheet.Cells(lngRowCount, intCounter).Value = _
               varField

         Next intCounter

         lngRowCount = lngRowCount + 1

         ' See if we are still in range
         If Not IsMissing(varMaxRecs) Then
            If lngRowCount > varMaxRecs Then
               Exit Do
            End If
         End If

         ' Move to the next record
         .MoveNext

      Loop
   End With

   ' Cleanup
   rst.Close
   dbs.Close

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "CreateTableFromAccess"
   Resume PROC_EXIT

End Sub

Public Sub CreateWorkbook( _
   strName As String, _
   fSave As Boolean)
   ' Comments  : Creates a new workbook and saves it
   ' Parameters: strName - name for the new workbook
   '             fSave - True to save, False to leave unsaved
   ' Returns   : Nothing
   '
   On Error GoTo PROC_ERR

   Set m_objWorkbook = m_objExcel.Workbooks.Add

   m_objWorkbook.SaveAs filename:=strName

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "CreateWorkbook"
   Resume PROC_EXIT

End Sub

Public Sub InsertValue( _
   strRange As String, _
   varValue As Variant)
   ' Comments  : Inserts values into cells
   ' Parameters: strRange - string defining the range to insert into
   '             varValue - value to insert
   ' Returns   : Nothing
   '
   On Error GoTo PROC_ERR

   m_objWorkbook.ActiveSheet.Range(strRange).Value = varValue

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "InsertValue"
   Resume PROC_EXIT

End Sub

Public Sub OpenWorkbook( _
   strFileName As String, _
   fReadOnly As Boolean, _
   Optional varPassword As Variant)
   ' Comments  : Opens the named file and associates it with the class
   ' Parameters: strFileName - full path and name of the file to open
   '             fReadOnly - True to open readonly
   '             varPassword - Optional: specify the password if the
   '             workbook file is password protected.
   ' Returns   : Nothing
   '
   On Error GoTo PROC_ERR

   If Not IsMissing(varPassword) Then
      Set m_objWorkbook = m_objExcel.Workbooks.Open( _
         strFileName, _
         , _
         fReadOnly, _
         , _
         varPassword)
   Else
      Set m_objWorkbook = m_objExcel.Workbooks.Open( _
         strFileName, _
         , _
         fReadOnly)
   End If

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "OpenWorkbook"
   Resume PROC_EXIT

End Sub

Public Sub OpenWorkbookFromLib( _
   strFileName As String, _
   fReadOnly As Boolean, _
   Optional varPassword As Variant)
   ' Comments  : Opens the named file and associates it with the class.
   '             This version looks in the Excel library folder.
   ' Parameters: strFileName - name of the file to open
   '             fReadOnly - True to open readonly
   '             varPassword - Optional: specify the password if the
   '             workbook file is password protected.
   ' Returns   : Nothing
   '
   Dim strLibPath As String

   On Error GoTo PROC_ERR

   strLibPath = m_objExcel.LibraryPath & _
      m_objExcel.PathSeparator & _
      strFileName

   If Not IsMissing(varPassword) Then
      Set m_objWorkbook = m_objExcel.Workbooks.Open( _
         strLibPath, _
         , _
         fReadOnly, _
         , _
         varPassword)
   Else
      Set m_objWorkbook = m_objExcel.Workbooks.Open( _
         strLibPath, _
         , _
         fReadOnly)
   End If

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "OpenWorkbookFromLib"
   Resume PROC_EXIT

End Sub

Public Sub PrintSheet( _
   intFrom As Integer, _
   intTo As Integer, _
   intCopies As Integer, _
   fPreview As Boolean, _
   fPrintToFile As Boolean, _
   fCollate As Boolean)
   ' Comments  : Prints the active workbook
   ' Parameters: intFrom - starting page number
   '             intTo - ending page number
   '             intCopies - number of copies
   '             fPreview - True for print preview
   '             fPrintToFile - True to print to a file. Excel will prompt
   '             for the filename when this is set to True.
   '             fCollate - True to collate copies
   ' Returns   : Nothing
   '
   On Error GoTo PROC_ERR

   m_objWorkbook.PrintOut _
      intFrom, _
      intTo, _
      intCopies, _
      fPreview, _
      , _
      fPrintToFile, _
      fCollate

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "PrintSheet"
   Resume PROC_EXIT

End Sub

Public Sub SortRange( _
   strRange As String, _
   strKey As String, _
   Optional fAscending As Boolean = False)
   ' Comments  : Sorts the specified range
   ' Parameters: strRange - range to sort
   '             strKey - range to use as the key for sorting
   '             fAscending - True for ascending, False for descending
   ' Returns   : Nothing
   '
   Dim lngSort As Integer

   If fAscending Then
      lngSort = xlAscending
   Else
      lngSort = xlDescending
   End If

   m_objWorkbook.ActiveSheet.Range(strRange).Sort _
      Key1:=ActiveSheet.Range(strKey), order1:=lngSort

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "SortRange"
   Resume PROC_EXIT

End Sub

Public Sub StartExcel(fVisible As Boolean)
   ' Comments  : Starts an instance of Excel
   ' Parameters: fVisible - True to make Excel visible
   ' Returns   : Nothing

   On Error GoTo PROC_ERR

   Set m_objExcel = New Excel.Application
   m_objExcel.Visible = fVisible

PROC_EXIT:
   Exit Sub

PROC_ERR:
   MsgBox "Error: " & Err.Number & ". " & Err.Description, , _
      "StartExcel"
   Resume PROC_EXIT

End Sub

0
watyCommented:
http://www.vb-helper.com/HowTo/doexcel.zip

      Purpose
Write Excel files directly without using Excel

      Method
See the code and other text files.

Thanks to Dan Gardner (nambit@hotmail.com).

      Disclaimer
This example program is provided "as is" with no warranty of any kind. It is
intended for demonstration purposes only. In particular, it does no error
handling. You can use the example in any form, but please mention
www.vb-helper.com.
-----------

* What does this do?

This VB sample demonstrates one way to write Excel files nativly from VB.


* Why would I want to do that?

Because sometimes you don't want the hassle of loading Excel as an
automation object, playing with the values and writing the file.
Sometimes you want to run some sort of daemon process which creates Excel
files on a box that doesn't have Excel.


* Why would *you* want to do that?

I needed to send information to my customers requlary, and they were asking
for Excel files far too much. I could get away with sending CSV text for only
so long. I don't have Excel on the box running my daemon, so I decided to write
the files directly as binary, rather than paying for Excel.


* Which version of Excel does it use?

The class creates Excel 2 format files


* How do I use it?

Add ExcelFile.cls to your project

Dim EX1 as New ExcelFile
EX1.OpenFile "test.xls"
EX1.EWriteString 1,1,"test"
EX1.EWriteInteger 1,2,100
EX1.CloseFile


The parameters to EWriteString and EWriteInteger are row, column and data. Row
and Col start at 1,1 for cell A1.


* What about Bold, Italic and all that Jazz?

I didn't need them

I've only done text and integer fields, because that's all I needed. If
someone fancies fixing my code and adding support for these things then please
feel free.


* Where can I learn more?

I've included the two documents which I used as reference in this archive


* Your code is horrible

Yes, I know, but it started as a playing around kludge, and didn't get much better.
Again, if someone wants to fix the code so it looks better then feel free. It's so
short that it doesn't really matter to me.


* Contacts?

You can mail me at nambit@hotmail.com
There's no web address

* Licence?

Far too much trouble for 30 lines of code. Do with it what you will, but if you
use it for anything really neat then let me know.


This document was written 17 Jun 1999

0
bja1Author Commented:
Sorry I meant export a table from Access to a Excel formatted file without Access being installed locally.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.