By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,504 Members | 1,170 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,504 IT Pros & Developers. It's quick & easy.

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

P: 4

I want to link multiple .txt files of different columns in access via VBA. I have made link spec named as " GCELL_Specification" 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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub btn_dir_Click()
  2. Dim objFSO As FileSystemObject
  3. Dim objFile As File
  4. Dim ObjDirRoot As Folder
  6. Dim PageName As String
  8. Set objFSO = New FileSystemObject
  9.     Set ObjDirRoot = objFSO.GetFolder("D:\Compiled DB\North")
  10. For Each objFile In ObjDirRoot.Files
  11. PageName = objFile.Name
  12. DoCmd.TransferText acLinkDelim,"GCELL_Specification" , PageName, objFile.Name, True
  17.     Next
  19. End Sub
Mar 13 '14 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 5K+
P: 5,397
The field names in the table created in Access will be taken fromt the header row of the text file.

Expand|Select|Wrap|Line Numbers
  1. 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?
Mar 13 '14 #2

P: 4
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.transfertext 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.
Mar 13 '14 #3

Expert Mod 5K+
P: 5,397
Well let us take a momement to see what you are actually doing
The basic syntax is:
Expand|Select|Wrap|Line Numbers
  1. docmd.TransferText _
  2.    TransferType, _
  3.    SpecificationName, _
  4.    TableName, _
  5.    FileName, _
  6.    HasFieldNames, _
  7.    HTMLTableName, _
  8.    CodePage
what you have is:
Expand|Select|Wrap|Line Numbers
  1. docmd.TransferText _
  2.    TransferType:= acLinkDelim, _
  3.    SpecificationName:= "GCELL_Specification", _
  4.    TableName:= PageName, _
  5.    FileName:=objFile.Name, _
  6.    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
Expand|Select|Wrap|Line Numbers
  1.    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
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1.    FileName:=objFile.Name, _
is really
Expand|Select|Wrap|Line Numbers
  1.    FileName:=objFile.path, _
What I would tell you to do is insert between lines 10 and 11
Expand|Select|Wrap|Line Numbers
  1. 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
Mar 13 '14 #4

P: 4
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???
Mar 14 '14 #5

Expert Mod 5K+
P: 5,397
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.
Mar 15 '14 #6

Post your reply

Sign in to post your reply or Sign up for a free account.