Finding .transfertext [specificationname]

[pplstuff]

Access 2007.



I'm going through someone else's database, and I'm seeing they import a few .txt and.csv files (some delimited, some fixed). They use the line:

docmd.transfertext acimportdelimited, "xxx", ...

Where "xxx" is the [specificationname]. I have looked in the saved imports and cannot find "xxx". Where else might this instruction be? There are about a dozen imports and they all have their own [specificationname]. I'm rewriting their code in a new db and I need my process to match theirs.

My code so far below:

Sub test()
    
    Dim rst As DAO.Recordset
    Dim sFileName As String
    
    'Set variables
    Set rst = CurrentDb.OpenRecordset("SELECT * FROM t_BklgFileLocation")
    
    'Check if table contains records
    If Not (rst.BOF And rst.EOF) Then
        
        'Move to first record
        rst.MoveFirst
        
        'Loop through each record
        Do Until rst.EOF = True
            
            'If the table currently exists, delete it
            On Error Resume Next
            DoCmd.RunSQL "DROP TABLE " & rst("TableName")
            On Error GoTo 0
            
            'Set variable
            sFileName = rst("FileLocation") & "\" & rst("FileName")
            
            'Import new table
            'On Error Resume Next
            Debug.Print rst("TransferType")
            If rst("TransferType") = "acImportFixed" Then
                DoCmd.TransferText acImportFixed, , rst("TableName"), sFileName 'THIS IS WHERE I"M TRYING TO FIX
            End If
            'On Error GoTo 0
            
            'Delete ImportErrors table
            On Error Resume Next
            DoCmd.DeleteObject acTable, rst("TableName") & "_ImportErrors"
            On Error GoTo 0
            
            'Move to the next record
            'Without this line, there would be an endless loop
            rst.MoveNext
            
        Loop
        
    Else:
        
        'Tell user there are no records
        MsgBox "There are no records in this recordset", vbOKOnly, "Error"
        
    End If
    
    'Close the recordset
    rst.Close
    
    'Clear memory
    Set rst = Nothing
    
End Sub

[rpeare]

Specifications are harder to get to in Access 2007, for some reason MS doesn't want you calling them by name, they want you to record the import steps then call the import saved procedure rather than calling it by a specification name.

Is your goal to import their specifications so you do not have to redefine them? If so, in your current database select EXTERNAL DATA>ACCESS> Select the database you want to import the specs from > make sure IMPORT is selected > Click OPTIONS at the lower right corner of the import dialog box > SELECT IMPORT/EXPORT SPECS

It'll import all of the available specs so you can just use what's already there.

[pplstuff]

Specifications are harder to get to in Access 2007, for some reason MS doesn't want you calling them by name, they want you to record the import steps then call the import saved procedure rather than calling it by a specification name.

Is your goal to import their specifications so you do not have to redefine them? If so, in your current database select EXTERNAL DATA>ACCESS> Select the database you want to import the specs from > make sure IMPORT is selected > Click OPTIONS at the lower right corner of the import dialog box > SELECT IMPORT/EXPORT SPECS

It'll import all of the available specs so you can just use what's already there.

I've tried this method, of importing the other db import specs... but there are none! He has one export specification saved.

Here is his line:
DoCmd.TransferText acImportFixed, "BklgImport", "BklgUS", BL_US_S_File_Location, False
------------------------------------------^^^^^^
Arrows point to the peice I'm trying to figure out.

Thanks for any feedback.

[rpeare]

is the source database split into a front end/back end? if it is are you trying to import specs from the front end?

nevermind the second part. So we're looking for an import spec called bklgus in either the front end or the back end of the original database can you check both and see what you find (if it is split)

[gary78rpm]

Give this a try:

Single click on a table or query.
Click the "External Data" menu item then click "-> Text File".
The "Export - Text File" dialog box pops up. It doesn't matter what you put here - just press "OK".
The "Export Text Wizard" dialog box replaces the last dialog box. Press the "Advanced" button.
A new pop-up dialog box appears. Click the "Specs..." button.

That should display a list of Import/Export Specifications that are stored in your database. Hope this helps.