Exporting an Access table from VB6

[SherriT]

I am attempting to write a simple VB6 application that will create an instance of Access, open a database, and export a table using export specifications within the same database. The code works if I am in Access (97), but I get an error saying the spec does not exist if I try to run from the VB6 app. I've checked references, tried hard-coding the names of the specs, but nothing seems to work. Has anyone encountered something like this before, or have any ideas for me to try?

Thanks, and Happy Holidays!

------------------
Sherri T.

[JHausmann]

Don't be shy, post up the code that's faling...

[Clunietp]

Are you using a macro from within Access to perform this function?

[SherriT]

The line of code is rather long, so it might be harder to read -- I apologize....

objAccess.DoCmd.TransferText acExportFixed, txtExportSpec.Text, txtTableName.Text, txtFileName.Text, False, ""

The object "objAccess" is an instance of an Access application, and the parameters above are all strings, though I'm sure you figured that one out 8-)

I'm not using a macro within Access (but this line of code works within the database). In order to make this app more versatile, it needs to be run from VB6....

I've used similar code in a previous VB6 app I wrote, so I know it is possible. We are actually thinking that we could be missing a piece of the full Access install, or that I am missing a key reference. (Note: I have chosen both the Office 8.0 and Access 8.0 libraries....)

Thanks for your help!


------------------
Sherri T.

[SherriT]

You're right...I have tried that about 100 times (literally). I've even hard-coded the text properties equal to the test data so I could double-check it before execution.

I didn't really think it had anything to do with the Access installation, but a co-worker has experienced lots of weird problem with the export functionality, so I was grasping at straws....

Have you ever done an export similar to this? Is there another way I can do one without using the DoCmd object? I couldn't find anything that allowed me to keep those inputs flexible AND control it from VB6.

If you have any other ideas, I'm all ears, and I'll post the answer if I figure it out.




------------------
Sherri T.

[Gerald]

Sherri,

I would think if you were missing a piece of the full Access install or missing a key reference, that the export spec would fail within Access '97 too.

You've probably already done this one hundred times, but set a breakpoint on the offending line of code and make absolutely certain txtExportSpec.text contains the correct spelling of the export spec in your access database--make sure there are no leading spaces in txtExportSpec.text.


Gerald

[Clunietp]

I agree with Gerald. Make sure the text boxes contain the correct reference to the export spec

[Clunietp]

Does the hard coding of the parameters function properly?

[Gerald]

Sherri,

I have done what you are doing before and it does work, but I don't know why in your particular case it does not. However, there are other ways to accomplish what you want without using the Access 8.0 object library and an Access export specification. You could instead write VB code to loop through a recordset while writing the contents out to a fixed length text file. The following example can be used as a generic export routine for just about any table in your database. It's not complete (not all data types are accounted for), but it gives you a general idea of what you could do in place of MS Access.

Private Sub Command1_Click()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    
    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=yourdata.mdb;"
    cn.Open
    rs.CursorLocation = adUseServer
    rs.Open "SELECT * FROM yourtable", cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    
    ExportFixed rs, "c:\fixed.txt"

End Sub


Public Sub ExportFixed(rs As ADODB.Recordset, sPath As String)

    On Error GoTo Error_ExportFixed

    Dim sText As String
    Dim nCol As Integer, nMax As Integer, nWidth As Integer

    nMax = rs.Fields.Count - 1
    
    Open sPath For Output As #1

    While Not rs.EOF
        sText = ""

        For nCol = 0 To nMax

            With rs.Fields(nCol)

                Select Case .Type

                    Case adVarChar
                        nWidth = .DefinedSize
                        sText = sText & Left(.Value & Space(nWidth), nWidth)
                        
                    Case adDate
                        sText = sText & Format(.Value, "mm/dd/yyyy")
    
                    Case adInteger
                        sText = sText & Right(Space(10) & Str(.Value), 10)

                    Case adBoolean
                        sText = sText & IIf(.Value, "T", "F")
    
                End Select

            End With

        Next

        Print #1, sText
        rs.MoveNext

    Wend

Error_ExportFixed:
    Close #1

End Sub

[This message has been edited by Gerald (edited 12-26-1999).]

[SherriT]

Thanks Clueintp and Gerald for trying to help me out on this one. Unfortunately, the idea of looping to create the fixed width file is not a viable one for me. I actually already have code written that does just that. What we are trying to accomplish with this mini-app is to allow non-technical people to be able to run these exports without programmer intervention. Since we are dealing with customer data, there isn't one agreed upon format -- there are several, and we add new ones all the time. If this were in code, a programmer would have to make modifications every time we added a unique customer....

I'm like you, Gerald....this has worked for me before, and I just can't figure out why it's not working now. I'm even more suspicious about the Access install though, because I ported everything over to Access 97, and the line of code still doesn't work. I can run it manually, but not in code, and especially not with parameters like I need to implement.

I'll keep working with it, and will post anything I discover.....

Again, I really appreciate all the suggestions!



------------------
Sherri T.