how to use Single link specification for multiple different headers tables in Access

[Farah Siraj]

Hi,

I want to link multiple .txt files of different columns in access via VBA. I have made link spec named as " GCELL_Specifica tion" and using the following code.
The problem is Access is making same headers in all linked files as the headers of Spec. Its messing all the linked files. Can anyone tell me how to use a single spec in linking multiple tables of different headers.
Or can anyone tell me how to link without spec so that access takes datatype by default for each table. I tried this but access giving the error of 3011.
The code that i am using is :

Private Sub btn_dir_Click()
Dim objFSO As FileSystemObject
Dim objFile As File
Dim ObjDirRoot As Folder

Dim PageName As String

Set objFSO = New FileSystemObject
    Set ObjDirRoot = objFSO.GetFolder("D:\Compiled DB\North")
For Each objFile In ObjDirRoot.Files
PageName = objFile.Name
DoCmd.TransferText acLinkDelim,"GCELL_Specification" , PageName, objFile.Name, True
           
              
   
    
    Next

End Sub

[zmbd]

The field names in the table created in Access will be taken fromt the header row of the text file.

PageName = objFile.Name

THis is being used to create new table line 12 using the file's name

". Its messing all the linked files. "
Makes absolutly no sense.


Can you try to clarify what it is that you are attempting to do?

[Farah Siraj]

Sorry by mistake, i posted objFile.Name instead of objFile.Path
My Question is that i want to link no. of txt files in access but all the txt files have different no. of headers. If i save a specification and use the spec in docmd.transfert ext command so code works but its messing all the linked tables in the way that all linked tables got the same headers as the file ( i linked for making specification) has.
I tried also to link leaving the spec argument empty so that access can take datatypes by default. But then the command gives error.

[zmbd]

Well let us take a momement to see what you are actually doing
The basic syntax is:

docmd.TransferText _
   TransferType, _
   SpecificationName, _
   TableName, _
   FileName, _
   HasFieldNames, _
   HTMLTableName, _
   CodePage

what you have is:

docmd.TransferText _
   TransferType:= acLinkDelim, _
   SpecificationName:= "GCELL_Specification", _
   TableName:= PageName, _
   FileName:=objFile.Name, _
   HasFieldNames:=True

Access is making same headers in all linked files as the headers of Spec

More than likely this is by design.
The specification file tells access what header from the file belongs to what field in the table and can potentially override the file's header row. As we have no idea what is in your specification file we're only guessing here, I would however, as a troubleshooting step remove the

   SpecificationName:= "GCELL_Specification", _

line and then run the code to allow the defaults to work.

With the correctin you've made, do you mean to say

PageName = objFile.Path

If So you are now potentially using illegal characters in the table name. You need to correct that.
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

However, what I think you intended to state was that

   FileName:=objFile.Name, _

is really

   FileName:=objFile.path, _

What I would tell you to do is insert between lines 10 and 11

debug.print objFile.path

Run your code, press <ctrl><g>, look at what is printed in the immediates window, make sure that the path and filename are properly formed.

Unless you can explain sitution more clearly,I don't see how we can help you more.4

[Farah Siraj]

Thanks, I have linked/imported tables as i desired but still the problem is whenever i import tables again and again, Access create create another tables with the same succeeding with 1,11 and so on while remaining old imported tables. I tried to delete records from old imported tables but no effect in creation of new table every time i click import.
Is it possible to replace the old imported table by the new table with the same name???

[zmbd]

now, you're not importing this text, you are linking to the text file; thus, each new link MUST have a unique name.
Just to be clear... the file's information has NOT been added to the database file, only a pointer to the text file has been created. The actual data is still in the origninal text file.

Now you can use VBA to append all of the data to a new text file, however, why do that when you can import the data to a table within the database file.

If you want to import the text to a table within the database file then you need to change the TransferType:= acLinkDelim to read TransferType:= acImportDelim you will also need to make sure that the table refered to in TableName:= PageName is correct for each file imported or you'll be creating new tables too.