VB6 code to read mdb table and convert to csv file

Tylendal1
Tylendal1 used Ask the Experts™
on
I'm looking for code syntax that reads an mdb table and outputs it to a CSV file using VB6.  I have been using Access and the docmd.TransferText to create a delimited file for export but am stumbling with the correct code to do the same in VB6.  I'm thinking the process is simple but I'm a newbie to VB6.

Appreciat any assistance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello Tylendal1,

You can simply do this:


Dim acApp As Object

Set acApp = CreateObject("Access.Application")
With acApp
    .OpenCurrentDatabase "path of MDB file here"
    .DoCmdTransferText
    .CloseCUrrentDatabase
    .Quit
End With
Set acApp = Nothing



Regards,

Patrick

Author

Commented:
Hi matthewspatrick:
This is the code I have created in VB6 using your solution.  I use "Call ExportVI_CSVData".

Sub ExportVI_CSVData()
    Dim acApp As Object
    mPathExport = "V:\MAS90\Home\TextOut\"
    mFileExport = "UPS_MAS.CSV"
    mPathMove = "v:\MAS90\"
    Set acApp = CreateObject("Access.Application")
        With acApp
            .OpenCurrentDatabase "V:\FRED\UPS_VInfo\UPS_VInfoVB.mdb"
            .DoCmdTransferText acExportDelim, "UPS_Export", "UPSExport", _
                        mPathExport & mFileExport, False
            .CloseCurrentDatabase
            .Quit
        End With
    Set acApp = Nothing
End Sub
 
When executing the .DoCmdTransferText I get a run-time error '438':
Object doesn't support this property or method.  Have i incorrectly applied your solution?
Thanks for your assistance.
Top Expert 2010
Commented:
Tylendal1,

If this is late bound, then you cannot rely on VB6 to resolve Access's named constants. So, you either need
to do this:


Sub ExportVI_CSVData()
Dim acApp As Object
mPathExport = "V:\MAS90\Home\TextOut\"
mFileExport = "UPS_MAS.CSV"
mPathMove = "v:\MAS90\"
Set acApp = CreateObject("Access.Application")
With acApp
.OpenCurrentDatabase "V:\FRED\UPS_VInfo\UPS_VInfoVB.mdb"
.DoCmdTransferText 2, "UPS_Export", "UPSExport", _
mPathExport & mFileExport, False
.CloseCurrentDatabase
.Quit
End With
Set acApp = Nothing
End Sub



or this:


Sub ExportVI_CSVData()
Dim acApp As Object
Const acExportDelim As Long = 2
mPathExport = "V:\MAS90\Home\TextOut\"
mFileExport = "UPS_MAS.CSV"
mPathMove = "v:\MAS90\"
Set acApp = CreateObject("Access.Application")
With acApp
.OpenCurrentDatabase "V:\FRED\UPS_VInfo\UPS_VInfoVB.mdb"
.DoCmdTransferText acExportDelim, "UPS_Export", "UPSExport", _
mPathExport & mFileExport, False
.CloseCurrentDatabase
.Quit
End With
Set acApp = Nothing
End Sub


Patrick
Post-closure Note:
   .DoCmdTransferText should be .DoCmd.TransferText

Open in new window

How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Hi...
The suggested modifications to your solution gaves me the same run-time error message '438':  Object doesn't support this property or method
Top Expert 2010

Commented:
Are you positive you have a specification in that database named UPS_Export?

Are you positive that your table is named UPSExport?

Author

Commented:
Hi again...
The USP_Export is the name of the export specification file used to output data from the table USPExport.  I created the original application in Access2003 and just ported it to VB where I ran into the problem with the Docmd.TransferText.command.
I just executed the Access application and it works fine. Sorry for the headache this might be creating.
Top Expert 2010

Commented:
Tylendal1 said:
>>Sorry for the headache this might be creating.

No worries--sorry I haven't figured it out :)

I think you should use the 'request attention' link to ask the Mods to add the Access zone and send out alerts, because
I am running out of ideas.

Author

Commented:
Not sure where the "request attention" link is located but one of my tags was Access 2003.
Fred
Top Expert 2010

Commented:
Fred,

Scroll to the top of the page.  It should be in the block with your original question.

Patrick
Top Expert 2014

Commented:
I think that
.DoCmdTransferText

should be
.DoCmd.TransferText

=====
TransferText is a DoCmd method
Well spotted.
Top Expert 2010

Commented:
Thanks, aikimark.  Darn typos :)

Author

Commented:
Sorry we stumbled around on this Patrick.  But once I added the "." and ran your solution again, it worked perfectly.  Thanks for the help.

Fred

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial