Solved

Exporting a database

Posted on 1998-08-23
17
248 Views
Last Modified: 2010-04-30
Hi,

I want to export a database file to some other formats.
I have no real idea if this is easy or hard, but it must
be done from within VB (VB 5 Pro), using only what comes with VB (ie: no extra controls, or 'user must have xxx installed on their system).

Let's assume I have a database file called main.mdb (Access)
Then I have 4 tables : table1, table2, table3, table4

How would I export the information in these tables, to

1 - ASCII
2 - Excel
3 - Any spreadsheet (same stuff as Excel?, perhaps a Comma separated field?)

My program simply uses the Data control to add, view, delete, and update my database...and I want an option to export the contents to those formats.

Thanks for any help,
oreg

(ps: I will give bonus points for a great answer)
0
Comment
Question by:oreg
  • 11
  • 4
  • 2
17 Comments
 
LVL 4

Expert Comment

by:mcix
ID: 1430512
A couple of questions...

You have multiple tables, when you export do you want each table to be a separate

1. file in ASCII.
2. worksheet, workbook in Excel.
3. for the unknown export?

0
 
LVL 3

Expert Comment

by:SPECIALIST
ID: 1430513
On a command button on click event
 
Dim db As New Access.Application
Set db = GetObject("c:\my documents\db2.mdb")'change to your db name and path
'make sure you have access and excel in your vb references


with db
DoCmd.TransferText acExportDelim, "", "table or quey name to export to textfile", "the file you want it saved to.txt"


'if you want to send to an excel file:

DoCmd.TransferSpreadsheet acExport, 8, "tablenameorqueryname", "c:\my documents\filename.xls", False, ""

'8 means the version of excel 8 means version 97
end with

If you need further help, let me know!

Specialist

0
 
LVL 4

Expert Comment

by:mcix
ID: 1430514
The Specialist is correct if you have Access and Excel loaded on the client machines...

You can also accomplish this through DAO code to get the tables into an ASCII text file.
0
 

Author Comment

by:oreg
ID: 1430515
Wow, these responses were very fast, thank you for that.  I am not sure how the exported data should look, ie: separate files or not...but that would be fine for me...each table = each file.

SPECIALIST, thanks for your answer, but like mcix said, this means that Access and Excel _must_ be installed on the client machine, and right now that is not the way I want to go...I can hear the screams of Wordperfect users now as I tell them they must install office to run my puny program :)

Thanks,
oreg
0
 
LVL 4

Expert Comment

by:mcix
ID: 1430516
I have a vb class that can split it into CSV files...

Do you want it?
0
 
LVL 3

Expert Comment

by:SPECIALIST
ID: 1430517
I misread your question did not realize they did not have them installed.

Specialist

0
 

Author Comment

by:oreg
ID: 1430518
No prob Specialist.  Sure mcix, answer the question and you can post it, or send it to me via email : murphyb@uoguelph.ca
If that works out, I will give you the points.  CSV is probably all I want anyhow.

Thanks,
oreg
0
 
LVL 4

Expert Comment

by:mcix
ID: 1430519
You got it...
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 4

Accepted Solution

by:
mcix earned 320 total points
ID: 1430520
Below is the source function to send a specified table in specified database to a specified target path.

--------------------------- CUT HERE ------------------------

Private Function TableToASCII(vstrDBName As String, vstrTargetPath As String, vstrTableName As String) As Boolean
   
    On Error GoTo err_TableToASCII
   
    ' DAO Work Variables
   
    Dim mdaoWorkSpace As DAO.Workspace
    Dim mdaoDatabase As DAO.Database
    Dim mdaoCurrentRecordSet As DAO.Recordset
    Dim mdaoFieldDef As DAO.Field
   
    ' String Variables
   
    Dim mstrCurrentLine As String
    Dim mstrCurrentTableASCIIFile As String
    Dim mstrFileExtention As String
   
    ' Long Variables
   
    Dim mlngFreeFile As Long

    ' Set up a Jet WorkSpace
   
    Set mdaoWorkSpace = CreateWorkspace("TEMP_WS", "Admin", "", dbUseJet) '

    ' Open the passed database as non-exclusive
   
    Set mdaoDatabase = mdaoWorkSpace.OpenDatabase(vstrDBName, False)
   
    ' Derive CSV Text file Name
   
    mstrCurrentTableASCIIFile = vstrTargetPath & vstrTableName & FILE_TYPE_DELIMITED
   
   
    ' Open Text File
   
    mlngFreeFile = FreeFile
    Open mstrCurrentTableASCIIFile For Output As #mlngFreeFile
   
    ' Open RecordSet based on passed table name
   
    Set mdaoCurrentRecordSet = mdaoDatabase.OpenRecordset(vstrTableName)
   
    mstrCurrentLine = ""
   
    ' Construct out Header Cells
   
    For Each mdaoFieldDef In mdaoCurrentRecordSet.Fields
        mstrCurrentLine = mstrCurrentLine & Chr$(34) & mdaoFieldDef.Name & Chr$(34) & ","
    Next

    ' Strip off last comma
   
    mstrCurrentLine = Left$(mstrCurrentLine, Len(mstrCurrentLine) - 1)
   
    ' Print Headers
   
    Print #mlngFreeFile, mstrCurrentLine
   
    ' Construct Output Line for each record in the recordset
   
    Do Until mdaoCurrentRecordSet.EOF
        mstrCurrentLine = ""
       
        For Each mdaoFieldDef In mdaoCurrentRecordSet.Fields
           
            ' Check for a Null Field Value
            ' and construct appropriate print line
                   
            If Not IsNull(mdaoFieldDef.Value) Then
                mstrCurrentLine = mstrCurrentLine & Chr$(34) & mdaoFieldDef.Value & Chr$(34) & ","
            Else
                mstrCurrentLine = mstrCurrentLine & Chr$(34) & Chr$(34) & ","
            End If
           
       
        Next

        ' Strip off last comma
       
        mstrCurrentLine = Left$(mstrCurrentLine, Len(mstrCurrentLine) - 1)
       
        ' Print Data Line
        Print #mlngFreeFile, mstrCurrentLine
       
        ' Move to next record
        mdaoCurrentRecordSet.MoveNext
    Loop
     
    ' Close RecordSet and File
   
    mdaoCurrentRecordSet.Close
    Set mdaoCurrentRecordSet = Nothing
    Close #mlngFreeFile

    ' Close Database and WorkSpace
   
    mdaoDatabase.Close
    mdaoWorkSpace.Close
    TableToASCII = True
   
exit_TableToASCII:
   
    ' Clean-up and return
   
    Set mdaoDatabase = Nothing
    Set mdaoWorkSpace = Nothing
    Exit Function
   
err_TableToASCII:
   
    Select Case Err.Number
   
        Case 0
            ' There are a ton of things that could go wrong here
            ' Most notably a bad database, table, or target path
           
        Case Else
            MsgBox "Error : " & Err.Number & " - " & Err.Description, vbInformation & "Debug Error Info"
            TableToASCII = False
            Resume exit_TableToASCII
    End Select
   
End Function

' This is a sample call

Private Sub TestCall()

    mstrDBName = "C:\YOURDATABASE.MDB"
    mstrTargetPath = "C:\YOURDIRECTORY\"
    mstrTableName = "YOUR TABLE NAME"
   

    mboolPerform = TableToASCII(mstrDBName, mstrTargetPath, mstrTableName)
    If Not mboolPerform Then
        MsgBox "Something bad happened"
    Else
        MsgBox "Worked out"
    End If

End Sub

----------------- END CUT --------------------

To use make sure you have a reference to MS DAO 3.5 in Project References.

You should be able to paste into a form and go...

I have more code and a generic class, but my POP3 server is down right now and I can't send...
0
 
LVL 4

Expert Comment

by:mcix
ID: 1430521
I forgot that FILE_TYPE_DELIMITED is a defined constant in my class, it should be declared as:

Private Const FILE_TYPE_DELIMITED As String = ".CSV"


0
 
LVL 4

Expert Comment

by:mcix
ID: 1430522
oreg,

There are some errors in the code I posted...

I was cutting out my class lib and added some code to make it work from just a form...

You should get the good code in e-mail...
0
 
LVL 4

Expert Comment

by:mcix
ID: 1430523
Did you get the email?
0
 

Author Comment

by:oreg
ID: 1430524
Hi,

Yeah I got the email, I will try the code tommorrow when I have time...I skimmed it and it looks like it should work, although I will admit to not having much experience in this matter.

Thanks, I will let you know soon how it goes,
oreg
0
 
LVL 4

Expert Comment

by:mcix
ID: 1430525
Fair enough...
0
 
LVL 4

Expert Comment

by:mcix
ID: 1430526
Still waiting....
0
 

Author Comment

by:oreg
ID: 1430527
Hi there,

I am really sorry this took so long, your code worked really well.  As promised, I am adding some bonus points.

Take care, and thanks,
oreg
0
 
LVL 4

Expert Comment

by:mcix
ID: 1430528
Glad it worked out for you....
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now