Re: Please Help with database upate. New to database


OdAwG wrote:
Works like a charm, thanks for the expert advice.

I'm so sorry to ask you another question, you've been such a great help,
however, is there a way to automatically import text/excel files into Access
tables or query. what I would like to do is when I launch my database, it
would automatcially import the text files or excel files.

Argus

the way I would handle it is something like this:
1. create import specifications for your text files.
2. Always put the files to be imported into the same folder.
'---code these---
3. loop through the contents of the folder (from step 2), and import
the files.
4. any files successfully imported should be moved to a different
folder. (use Rename) The failed ones should go in another folder.

Sorry, getting brain damage... this is most of it... I've commented out
the parts that aren't working yet. Oh, the BrowseFolder API... that's
here:

http://www.mvps.org/access/api/api0002.htm

Here's my code so far...

Public Sub ImportFilesInDirectory(ByVal strExtension As String)
Dim strOriginalPath As String
Dim strFinalPath As String

Dim strFinalFile As String
Dim myFile As String

strOriginalPath = BrowseFolder("Select a folder to process")
strFinalPath = BrowseFolder("Select a folder to move processed
files to:")

myFile = Dir(strOriginalPath & "\*." & strExtension)
strFinalPath = strFinalPath & "\" & myFile

'---Loop through the files in the folder (strPath)
Do While myFile <> ""
'---handle Text and Excel files differently...
If strExtension = "txt" Then
DoCmd.TransferText acImportDelim, "MySpecificationName",
"DestinationTable", myFile, False
'Name "c:\origfile.txt" As "c:\newfile.txt" <---NEEDS
REPAIR
ElseIf strExtension = "xls" Then
DoCmd.TransferSpread*** acImport,
acSpreadsheetTypeExcel9, "DestinationTable", myFile, True
End If

'---run the "move file" code here...

myFile = Dir
Loop
End Sub

.