Import Data

[Keith]

I want to create a macro which opens up a 'file open' selection box so I can
choose which text file to import into my database each day. At the moment I
am using the TextTransfer option for which I have typed in the exact path
and filename, each day I simply save the new text file over the old one then
run the macro, however I am now going to need to be able to select a
different file each day, I can do this easily in Excel but not sure how to
go about it in Access?

Any help would be great.

Thanks

Keith

 

[Ken Sheridan]

Keith:

Download Bill Wilson's class module from:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=22415&webtag=ws-msdevapps

Add it to your database and call it in the Click event procedure of a button
on a form with code along these lines:

On Error GoTo Err_Handler

Const conPROMPT = "Enter table name into which to import data:"
Dim OpenDlg As New BrowseForFileClass
Dim strPath As String, strTable As String
Dim strAdditionalTypes As String

OpenDlg.DialogTitle = "Select File"
OpenDlg.AdditionalTypes = _
"Text Files (*.txt; *.csv; *.tab; *.asc) |*.txt; *.csv; *.tab; *.asc"
strPath = OpenDlg.GetFileSpec
Set OpenDlg = Nothing

If strPath <> "" Then
strTable = InputBox(conPROMPT, "Table Name")
If strTable <> "" Then
DoCmd.TransferText acImportDelim, , strTable, strPath
End If
End If

Exit_here:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_here

Ken Sheridan
Stafford, England

 

[Keith]

Thanks Ken,

That works a treat from a button, is there an easy way of incorporating it
into a macro as I do lots of other things to the data once I have imported
it, quite a few queries which all run from a macro, as it stands I need to
click a button to select the file then import the data to a temp table, then
run my macro for all the other manipulation?

Thanks again

Keith