Automate Linking or Importing of MS Access Tables

I have a directory that contains 100+ files (*.txt & *.xls) that I would like to link to an Access database. Ideally I would like link all the files in a subdirectory to tables with names that are the same as the file name without the extension. The files all contain headers in the first row of the file. Additionally, I would like all the fields to be imported as text. Does anyone have some VBA code that would help automate this process?
LVL 1
HRISTeamAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
You have found some pretty good links already...

So, what is wrong with transfertext and transferspreadsheet functions ?

I think it is going to be a bit of a challenge import directly from excel and make it ignore the natural field characteristics that Excel reckons are needed.

There are some other great examples at : http://www.accessmvp.com/KDSnell/EXCEL_Import.htm
0
 
HRISTeamAuthor Commented:
0
 
HRISTeamAuthor Commented:
http://www.ozgrid.com/forum/showthread.php?t=27088

http://www.ozgrid.com/forum/showthread.php?t=156415

Option Compare Database
Option Explicit
 
Sub Link_To_Excel()
     'Macro Loops through the specified directory (strPath)
     'and links ALL Excel files as linked tables in the Access
     'Database.
     
    Const strPath As String = "G:\Med_Serv\Start Smart\HEDIS\Summary Detail\" 'Directory Path
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
     
     'Loop through the folder & build file list
    strFile = Dir(strPath & "*.csv")
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        Redim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list of files & link to Access
    For intFile = 1 To UBound(strFileList)
        DoCmd.TransferText acLinkDelim, , _
        strFileList(intFile), strPath & strFileList(intFile), True, "" 
         'Check out the TransferSpreadsheet options in the Access
         'Visual Basic Help file for a full description & list of
         'optional settings
    Next
    MsgBox UBound(strFileList) & " Files were Linked"
End Sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HRISTeamAuthor Commented:
0
 
als315Commented:
Can you manually link files without addititional settings (should you manually set type of some fields during import)?  Can you upload some sample files?
0
 
aikimarkCommented:
@HRISTeam

Are you attaching these files for the purpose of importing the data into the Access tables?
0
 
HRISTeamAuthor Commented:
Sub Link_Files(strFileType As String, strPath As String)
     'Macro Loops through the specified directory (strPath)
     'and links ALL Excel files as linked tables in the Access
     'Database.
     
    Dim strFile As String 'Filename
    Dim strFileList() As String 'File Array
    Dim intFile As Integer 'File Number
   
     'Loop through the folder & build file list
    strFile = Dir(strPath & "*" & strFileType)
    While strFile <> ""
         'add files to the list
        intFile = intFile + 1
        ReDim Preserve strFileList(1 To intFile)
        strFileList(intFile) = strFile
        strFile = Dir()
    Wend
     'see if any files were found
    If intFile = 0 Then
        MsgBox "No files found"
        Exit Sub
    End If
     'cycle through the list of files & link to Access
    For intFile = 1 To UBound(strFileList)
   
        Select Case [strFileType]
          Case ".xls"
            DoCmd.TransferSpreadsheet acLink, , _
            strFileList(intFile), strPath & strFileList(intFile), True
          Case ".txt"
            DoCmd.TransferText acLinkDelim, "ImportSpec", _
            strFileList(intFile), strPath & strFileList(intFile), True
         'Check out the TransferSpreadsheet options in the Access
         'Visual Basic Help file for a full description & list of
         'optional settings
         End Select
    Next
    MsgBox UBound(strFileList) & " Files were Linked"
End Sub
0
 
HRISTeamAuthor Commented:
The code above works perfectly for the Excel files. However, my text files are a different story. The text files are pipe delimited so I attempted to create an import specification file to assist with this issue. All my text files have a different number of columns so it doesn’t work well (all txt based tables end up with a fixed number of fields based on original spec import). In addition, I would like all the fields be of type text. Does anyone have an idea on how this can be accomplished? Any help would be greatly appreciated.
0
 
aikimarkCommented:
You might need a schema.ini file.

Please answer my prior question
0
 
HRISTeamAuthor Commented:
I want to write queries from the data contained in the files so I either need to link the files or import the data to tables. My preference is to link the tables so when the files are updated I can just overwrite the old files with the updated files. Ultimately I will write a report from the linked information. The files are relatively small so performance is not really much of an issue.
0
 
HRISTeamAuthor Commented:
I ran the following query against my spec import.

DELETE *
FROM MSysIMEXColumns
WHERE SpecID In (SELECT SpecID FROM MSysIMEXSpecs WHERE SpecName=[Enter Specification Name]);

That allows for dynamic length but it doesn't include column headers. Any ideas?
0
 
aikimarkCommented:
I'm trying to wrap my mind around writing queries that get their data from hundreds of tables that are of different widths (column count) and, potentially, different columnar data types.

In addition to linked tables, you also have the ability to specify an external data source within a query, using the IN keyword.  This is not the same IN() keyword you would find in the WHERE clause.
0
 
HRISTeamAuthor Commented:
I understand this is an unusual situation but it is the case. I am not familiar with the keyword “IN” to specify an external data source but I want to keep this relatively simple (as far as wrting queries goes) so maybe my best bet would be to look into a schedma.ini file.
0
 
HRISTeamAuthor Commented:
0
 
aikimarkCommented:
Also, you might want to consider the WritePrivateProfileString API.

Here is a link to the MS documentation on the IN clause
http://office.microsoft.com/en-gb/access-help/HV080761082.aspx
0
 
als315Commented:
For text files (if you have limited set of variants) you can try to use this sample:
http://www.experts-exchange.com/Microsoft/Applications/Q_27836044.html#a38319403
It is for excel, but there will be almost no difference in VBA code. You can prepare from csv files with different structure one file with fixed structure.
0
 
HRISTeamAuthor Commented:
The "IN" clause is of limited use since I don't know how to use that for a pipe delimited text file. If you can provide an example, I might be able to use it.
0
 
Mark WillsTopic AdvisorCommented:
It sounds like the spreadsheet has been solved using TransferSpreadsheet method - is that correct ?

So, for text, if it is being so variable, then maybe you might need to manually code the TEXT import part.

Have a look at : http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22806457.html

Otherwise you can create a specification (or schema.ini) and mention that specification name as part of the transfertext method. Best way to create a specification is to do it manually using the import / export wizard and if you do have a "home" table for all imports, then create the specification by exporting that table and any non matching columns from the import should be blank.

Have a look at : http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_27098941.html for the specification bit.
0
 
aikimarkCommented:
What version of Access are you using?
0
 
HRISTeamAuthor Commented:
I am using Access 2003 SP2
0
 
HRISTeamAuthor Commented:
I created a Schema.ini file like:

[Some_File_1_A.txt]
Format = Delimited(|)
CharacterSet = ANSI
ColNameHeader = True
[Some_File_1_B.txt]
Format = Delimited(|)
CharacterSet = ANSI
ColNameHeader = True

DoCmd.TransferText acLinkDelim, strPath & "Schema.ini", _
        strFileList(intFile), strPath & strFileList(intFile), True

Now I am getting a Run-time error '3625'. The text file specification does not exist.
0
 
HRISTeamAuthor Commented:
0
 
HRISTeamAuthor Commented:
Thanks everyone for your input. There were some really good ideas and I have decided that I want to create a schema.ini. Now that I know what I want to do for a solution, please take a look at my new post if you have any ideas on how to fix my code. Thanks!

http://www.experts-exchange.com/Programming/Languages/Visual_Basic/Q_27847288.html
0
 
aikimarkCommented:
If you have a schema.ini, you should be able to do something like the following in a query:
SELECT *
From [Some_File_1_A.txt] In "" [Text;FMT=Delimited;HDR=YES;DATABASE=C:\Users\AikiMark\Downloads;] 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.