Solved

Exporting with TransferText and schema.ini

Posted on 2004-08-25
15
1,910 Views
Last Modified: 2012-06-21
I am sure this problem has been raised before.  I am trying to do an export of a table using transferText.  I need it to be tab delimited so I am creating a schema.ini just before the transfertext call.  

DoCmd.TransferText acExportFixed, , "tempSubTestScoresExp", fileName, True, "", 437

I get an error when I leave out a spec argument and also when I put the path and name of schema.ini....what gives?  I read that this is a known problem, but can't understand any of the workarounds given.   Can someone help me out???
0
Comment
Question by:requisiteSystem
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 11897780
acExportFixed is a fixed-width export, as opposed to acExportDelim which as delimeted.

You'll need to create and save an import spec that states tab delimeted, then refer to it in your TransferText line.

DoCmd.TransferText acExportDelim , {MyImportSpecName}, "tempSubTestScoresExp", fileName, True, "", 437

Hope this helps.
-Jim
0
 
LVL 34

Expert Comment

by:flavo
ID: 11897814
I use the specification.

Do a "dummy" import of your data using the wizard.  Then on the last screen before you hit "Finish" go Advanced, then "Save" or Save As r something.

GIve it a name (Ill use mySpec for this example) then you can use

DoCmd.TransferText acExportFixed, "mySpec", "tempSubTestScoresExp", fileName, True

Dave
0
 
LVL 34

Expert Comment

by:flavo
ID: 11897823
What's this 437 all about????  (maybe A97 is different???)

Dave
0
 

Author Comment

by:requisiteSystem
ID: 11897843
I'm not interested in using specs because the export will have different field names each time its run.   This is why I am creating the schema.ini on the fly from the table and then exporting it.   Sorry I meant tab-delimited.  Really looking for a solution that involves the schema.ini.  Thanks.
0
 

Author Comment

by:requisiteSystem
ID: 11898550
or is it possible to use vba to write directly to the system tables that have the spec info, in order to create a spec on the fly???
0
 
LVL 34

Expert Comment

by:flavo
ID: 11898572
0
 
LVL 34

Expert Comment

by:flavo
ID: 11898578
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 34

Expert Comment

by:flavo
ID: 11898587
0
 

Author Comment

by:requisiteSystem
ID: 11906472
I am using the function that is available on msdn to create a schema.ini.
here it is:

Private Function CreateSchemaFile(bIncFldNames As Boolean, _
                                       sPath As String, _
                                       sSectionName As String, _
                                       sTblQryName As String) As Boolean
         Dim Msg As String ' For error handling.
         On Local Error GoTo CreateSchemaFile_Err
         Dim ws As Workspace, db As Database
         Dim tblDef As TableDef, fldDef As field
         Dim i As Integer, Handle As Integer
         Dim fldName As String, fldDataInfo As String
         ' -----------------------------------------------
         ' Set DAO objects.
         ' -----------------------------------------------
         Set db = CurrentDb()
         ' -----------------------------------------------
         ' Open schema file for append.
         ' -----------------------------------------------
         Handle = FreeFile
         Open sPath & "schema.ini" For Output Access Write As #Handle
         ' -----------------------------------------------
         ' Write schema header.
         ' -----------------------------------------------
         Print #Handle, "[" & sSectionName & "]"
         Print #Handle, "ColNameHeader = " & _
                         IIf(bIncFldNames, "True", "False")
         Print #Handle, "CharacterSet = ANSI"
         Print #Handle, "Format = TabDelimited"
         ' -----------------------------------------------
         ' Get data concerning schema file.
         ' -----------------------------------------------
         Set tblDef = db.TableDefs(sTblQryName)
         With tblDef
            For i = 0 To .Fields.Count - 1
               Set fldDef = .Fields(i)
               With fldDef
                  fldName = .Name
                  Select Case .Type
                     Case dbBoolean
                        fldDataInfo = "Bit"
                     Case dbByte
                        fldDataInfo = "Byte"
                     Case dbInteger
                        fldDataInfo = "Short"
                     Case dbLong
                        fldDataInfo = "Integer"
                     Case dbCurrency
                        fldDataInfo = "Currency"
                     Case dbSingle
                        fldDataInfo = "Single"
                     Case dbDouble
                        fldDataInfo = "Double"
                     Case dbDate
                        fldDataInfo = "Date"
                     Case dbText
                        fldDataInfo = "Char Width " & Format$(.Size)
                     Case dbLongBinary
                        fldDataInfo = "OLE"
                     Case dbMemo
                        fldDataInfo = "LongChar"
                     Case dbGUID
                        fldDataInfo = "Char Width 16"
                  End Select
                  Print #Handle, "Col" & Format$(i + 1) _
                                  & "=" & fldName & Space$(1) _
                                  & fldDataInfo
               End With
            Next i
         End With
         MsgBox sPath & "SCHEMA.INI has been created."
         CreateSchemaFile = True
CreateSchemaFile_End:
         Close Handle
         Exit Function
CreateSchemaFile_Err:
         Msg = "Error #: " & Format$(Err.Number) & vbCrLf
         Msg = Msg & Err.Description
         MsgBox Msg
         Resume CreateSchemaFile_End
End Function

---------------------

this is my code:

If CreateSchemaFile(True, Mid(fileName, 1, InStrRev(fileName, "\")), Mid(fileName, InStrRev(fileName, "\") + 1), "tempSubTestScoresExp") Then
            DoCmd.TransferText acExportDelim, , "tempSubTestScoresExp", fileName, True, ""
            CurrentDb.TableDefs.Delete "tempSubTestScoresExp"
            MsgBox ("File export complete")
End If

fileName is a global string variable with the path to the export file.  
The code works, but when I look at my export file the first line(field names) is tab delimited with quotes around the field names, which is what I want, except for the quotes.  however, all the data on subsequent lines is embedded in quotes and comma delimited.  I don't know what is going on....I thought using the schema.ini would have given me flexibility to have a tab delimited export file without quotes.  Can anyone shed light on this for me.  I am increasing the points!
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12166962
requisiteSystem - Looks like you've received some solid advice from multiple experts.  Since this question appears to be getting old, please consider awarding points for the effort and closing it.  Thanks in advance.  -Jim
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12821112
Routinet:  You are an cleanup volunteer machine today...  :)
0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 12823350
Yeah, I actually time to spare!  I keep looking around for something else I SHOULD be doing, remember there isn't anything, then go to the next question.  :)  I knew there was a reason for holidays besides more commercials...
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12823434
I think that every once in awhile, but there's always billable hours to be had...  
0
 

Accepted Solution

by:
modulo earned 0 total points
ID: 12857530
PAQed with points refunded (100)

modulo
Community Support Moderator
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access Tables Linking 6 40
Search Form not Querying 2 10
MS-Access 2002 error (Win XP on Win7Pro) 19 35
Export Query data to excel file 14 28
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

930 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

16 Experts available now in Live!

Get 1:1 Help Now