How to use a single specification for linking multiple tables in Access 2010 via VBA

Question

2.50/5 (2 votes)

See more:

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 :

VB

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

Thanks in advanced.

Posted 13-Mar-14 1:04am

Farah Siraj

Updated 13-Mar-14 11:16am

Maciej Los

v2

Add a Solution

Comments

Farah Siraj 13-Mar-14 7:49am

Sorry by mistake, i posted objFile.Name instead of objFile.Path
My Question is the same as above.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-03-13T11:25:00

Solution 1

Have a look here: DoCmd.TransferText Method (Access)[^]

SpecificationName:
(...) For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.

Posted 13-Mar-14 11:25am

Maciej Los

Comments

Farah Siraj 14-Mar-14 4:18am

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???

Thanks in advanced.

Maciej Los 14-Mar-14 15:33pm

Before you start importing data, you should delete existing table by using DoCmd.DeleteObject Method (Access)[^].
DoCmd.DeleteObject acTable, "TableName"