Creating SpecificationName argument or Schema (1 Viewer)

kirkm

Registered User.
Local time
Tomorrow, 10:22
Joined
Oct 30, 2008
Messages
1,257
I wish to create a file from a record in my table. This is copied to a folder with ftp access where a cohort can get it and update his table.
I guess there's many ways to do this, but with my knowledge I've created the following. (With the help of https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transfertext
tblExport has been copied from tblMain in the GUI, structure only.
Code:
Private Sub Command190_Click()
    'Copy Record to tblEntry
    On Error GoTo err_Bad
    Dim r As DAO.Recordset, s As DAO.Recordset
    Dim Prefix As String
    Dim tr As String
    
    Prefix = Me!lblThisPrefix.Caption
    Set r = CurrentDb.OpenRecordset("Select * from tblMain where prefix = '" & Prefix & "';")
    
    'Delete tblExport
    DoCmd.SetWarnings False
    DoCmd.RunSQL ("Delete * From tblExport")
    DoCmd.SetWarnings True
    
    Set s = CurrentDb.OpenRecordset("tblExport")
    s.AddNew
    For i = 0 To r.Fields.Count - 1
        s(i) = r(i)
    Next
    s.Update
    s.Close
    r.Close
    
    'transfer the table
    tr = BrowseFolder("Destination for " & Prefix)
    If FolderExists(tr) = True Then
        DoCmd.TransferText acExportDelim, "Standard Output", "tblExport", tr & "\Prefix.doc"
    End If
    MsgBox "Done"
Leave:
    Set s = Nothing
    Set r = Nothing
    Exit Sub
err_Bad:
    MsgBox err.Description
    Resume Leave
End Sub

The problem is the "Specification name". "Standard Output" does not exist, it was used in the example. The link does sort of explain it,
For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file.
Not sure what I'd want fixed width, wouldn't its current width be better?
Then it goes on
To create a schema file, you can use the text import/export wizard to create the file.
Can someone tell me how to do this, or if I need to (or use am argument(?) instead.
Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:22
Joined
Oct 29, 2018
Messages
21,358
Hi. Just to clarify... Are you asking how to create a schema.ini file?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:22
Joined
May 7, 2009
Messages
19,169
you can use the alternative DoCmd.RunSavedImportExport
first export your table using External data->Export to text file, at the end of the process Save the Export Spec.
on succeeding exports, on same folder and file, use the above command.
 

kirkm

Registered User.
Local time
Tomorrow, 10:22
Joined
Oct 30, 2008
Messages
1,257
Yes, DBGuy, but I'll also look at arnes suggestion. which msay lead to the same thing ?
Thanks both.
 

kirkm

Registered User.
Local time
Tomorrow, 10:22
Joined
Oct 30, 2008
Messages
1,257
Thanks Arnelgp, that worked out very nicely.

I changed DoCmd.TransferText acExportDelim, "Standard Output", "tblExport", tr & "\Prefix.doc"
to
DoCmd.RunSavedImportExport "Export-tblExport2"

Where are the Export Spec details kept? Can I rename/edit them?
I also used @ as a delimiter but a non-printable char would be more robust. Can that be done?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:22
Joined
May 7, 2009
Messages
19,169
two tables:

MSysIMEXSpecs and MSysIMEXColumns
 

kirkm

Registered User.
Local time
Tomorrow, 10:22
Joined
Oct 30, 2008
Messages
1,257
I should have those tables? Not immediately visible under 'Tables'.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:22
Joined
May 7, 2009
Messages
19,169
2007-2010 have those tables.
i tried on 2019, but it does not show.

you can use VBA on it:

Dim objSpec As ImportExportSpecification
Dim strXML As String


Set objSpec = CurrentProject.ImportExportSpecifications.Item(SpecName)
' get the xml string
strXML = objSpec.xml
 

kirkm

Registered User.
Local time
Tomorrow, 10:22
Joined
Oct 30, 2008
Messages
1,257
Tricky. I'm just stuck at the moment importing the Exported file. It doesn't actually work very well.
The wizard reports it done, but with errors. These are
Error Field Row
Unparsable Record 2
Type Conversion Failure id 1
Null value in an auto-number field id 1

This field is an autonumber so problems not unexpected, but as it was "exported" by Access you'd expect it to handle it somehow.
It does add a record. but a completely blank one.
Do you think this is worth sticking with, or should I handle both inport/Export with my own code. Which would work LOL.

BTW still can't see those table sin my 2010
 

kirkm

Registered User.
Local time
Tomorrow, 10:22
Joined
Oct 30, 2008
Messages
1,257
Hi, I can see strXML using your code. But it's long.. LEN= 30473. It's one record in the table! Seems to contain lots of info about each column. e.g. Widths etc. None of this really needed. Possibly this method is not the best.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:22
Joined
May 7, 2009
Messages
19,169
import spec is Different from export spec.
so you initially create the import spec and use the spec name
on consequent imports.
 

kirkm

Registered User.
Local time
Tomorrow, 10:22
Joined
Oct 30, 2008
Messages
1,257
OK. Before I try that - it seems to be expecting a whole table. I'm exporting just one record to overwrite (if exists) in the target table or AddNew.
Is this do-able by the wizard?
Thanks.
 

isladogs

MVP / VIP
Local time
Today, 21:22
Joined
Jan 14, 2017
Messages
18,186
The import/export specifications are now saved as XML and are no longer stored in the two MSysImex... tables.
For more information on how to manage these, see
 

Users who are viewing this thread

Top Bottom