?
Solved

Automate Linking or Importing of MS Access Tables

Posted on 2012-08-28
26
Medium Priority
?
1,292 Views
1 Endorsement
Last Modified: 2012-08-29
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?
1
Comment
Question by:HRISTeam
  • 16
  • 6
  • 2
  • +1
26 Comments
 
LVL 1

Author Comment

by:HRISTeam
ID: 38343903
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 38343920
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
 
LVL 1

Author Comment

by:HRISTeam
ID: 38343933
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 40

Expert Comment

by:als315
ID: 38344987
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 600 total points
ID: 38345241
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38345943
@HRISTeam

Are you attaching these files for the purpose of importing the data into the Access tables?
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 38346112
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
 
LVL 1

Author Comment

by:HRISTeam
ID: 38346183
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
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 600 total points
ID: 38346233
You might need a schema.ini file.

Please answer my prior question
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 38346342
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
 
LVL 1

Author Comment

by:HRISTeam
ID: 38346370
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 38346419
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38346421
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
 
LVL 1

Author Comment

by:HRISTeam
ID: 38346523
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
 
LVL 1

Author Comment

by:HRISTeam
ID: 38346648
0
 
LVL 46

Assisted Solution

by:aikimark
aikimark earned 600 total points
ID: 38347009
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
 
LVL 40

Assisted Solution

by:als315
als315 earned 300 total points
ID: 38347174
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
 
LVL 1

Author Comment

by:HRISTeam
ID: 38347274
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
 
LVL 1

Author Comment

by:HRISTeam
ID: 38347639
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 600 total points
ID: 38347674
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38347722
What version of Access are you using?
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 38347835
I am using Access 2003 SP2
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 38347863
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
 
LVL 1

Author Comment

by:HRISTeam
ID: 38347871
0
 
LVL 1

Author Comment

by:HRISTeam
ID: 38348110
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
 
LVL 46

Expert Comment

by:aikimark
ID: 38348350
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

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question