Import Comma Delimited

[Al Camp]

I have a large table that needs regular "updates" to it via
frequent Comma Delimited text files.

I'd like a directory dialog box to open, allow me to select
the "latest" text update file, and then use the TransferText
command to import/append that data into a "temp" table.
(tblTempUpdateData) Then I'll use that temp table to run 2
queries... one to "update" the main table with any corrections to
existing recs, and another to "append" any new records.

I just can't seem to find out what procedure/function/command
I can use to open up a "Find your file" dialog box... and then
use the filename selected as an argument in my TransferText
command.

Thanks for any help in advance,
Al Camp

 

[Joe Fallon]

For older versions of Access try this:
http://www.mvps.org/access/api/api0001.htm

This code is a new feature in Access 2002.
It allows you to browse for a file and then store the selected file in 2
text boxes:
Me![txtLocalDir] , Me![txtLocalFileName]

'1 = DialogOpen, 2= SaveAs, 3=FilePicker, 4 = FolderPicker
'Cannot be used in Access 2000!
With Application.FileDialog(3)
.AllowMultiSelect = False
If .Show = True Then
Me![lblEdit].Visible = True
Me![txtLocalDir] = Left$(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName] = Right$(.SelectedItems(1),
Len(.SelectedItems(1)) - InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName].SetFocus
End If
End With

 

[Al Camp]

Thanks Joe,
Unfortunately... I'm working in Access2000. (which I should
have indicated in my initial question)
But.... I see what functions/methods you're using and I'll try
to "translate" into 2K.

Thanks for the speedy reply...
Al Camp

For older versions of Access try this:
http://www.mvps.org/access/api/api0001.htm

This code is a new feature in Access 2002.
It allows you to browse for a file and then store the selected file in 2
text boxes:
Me![txtLocalDir] , Me![txtLocalFileName]

'1 = DialogOpen, 2= SaveAs, 3=FilePicker, 4 = FolderPicker
'Cannot be used in Access 2000!
With Application.FileDialog(3)
.AllowMultiSelect = False
If .Show = True Then
Me![lblEdit].Visible = True
Me![txtLocalDir] = Left$(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName] = Right$(.SelectedItems(1),
Len(.SelectedItems(1)) - InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName].SetFocus
End If
End With

--
Joe Fallon
Access MVP

I have a large table that needs regular "updates" to it via
frequent Comma Delimited text files.

I'd like a directory dialog box to open, allow me to select
the "latest" text update file, and then use the TransferText
command to import/append that data into a "temp" table.
(tblTempUpdateData) Then I'll use that temp table to run 2
queries... one to "update" the main table with any corrections to
existing recs, and another to "append" any new records.

I just can't seem to find out what procedure/function/command
I can use to open up a "Find your file" dialog box... and then
use the filename selected as an argument in my TransferText
command.

Thanks for any help in advance,
Al Camp

 

[John Nurick]

Hi Al

In earlier versions, use the code at
http://www.mvps.org/access/api/api0001.htm

Thanks Joe,
Unfortunately... I'm working in Access2000. (which I should
have indicated in my initial question)
But.... I see what functions/methods you're using and I'll try
to "translate" into 2K.

Thanks for the speedy reply...
Al Camp

For older versions of Access try this:
http://www.mvps.org/access/api/api0001.htm

This code is a new feature in Access 2002.
It allows you to browse for a file and then store the selected file in 2
text boxes:
Me![txtLocalDir] , Me![txtLocalFileName]

'1 = DialogOpen, 2= SaveAs, 3=FilePicker, 4 = FolderPicker
'Cannot be used in Access 2000!
With Application.FileDialog(3)
.AllowMultiSelect = False
If .Show = True Then
Me![lblEdit].Visible = True
Me![txtLocalDir] = Left$(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName] = Right$(.SelectedItems(1),
Len(.SelectedItems(1)) - InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName].SetFocus
End If
End With

--
Joe Fallon
Access MVP

I have a large table that needs regular "updates" to it via
frequent Comma Delimited text files.

I'd like a directory dialog box to open, allow me to select
the "latest" text update file, and then use the TransferText
command to import/append that data into a "temp" table.
(tblTempUpdateData) Then I'll use that temp table to run 2
queries... one to "update" the main table with any corrections to
existing recs, and another to "append" any new records.

I just can't seem to find out what procedure/function/command
I can use to open up a "Find your file" dialog box... and then
use the filename selected as an argument in my TransferText
command.

Thanks for any help in advance,
Al Camp

 

[Joe Fallon]

Which is in line 2 of my original post. <g>
--
Joe Fallon
Access MVP

Hi Al

In earlier versions, use the code at
http://www.mvps.org/access/api/api0001.htm

Thanks Joe,
Unfortunately... I'm working in Access2000. (which I should
have indicated in my initial question)
But.... I see what functions/methods you're using and I'll try
to "translate" into 2K.

Thanks for the speedy reply...
Al Camp

For older versions of Access try this:
http://www.mvps.org/access/api/api0001.htm

This code is a new feature in Access 2002.
It allows you to browse for a file and then store the selected file in 2
text boxes:
Me![txtLocalDir] , Me![txtLocalFileName]

'1 = DialogOpen, 2= SaveAs, 3=FilePicker, 4 = FolderPicker
'Cannot be used in Access 2000!
With Application.FileDialog(3)
.AllowMultiSelect = False
If .Show = True Then
Me![lblEdit].Visible = True
Me![txtLocalDir] = Left$(.SelectedItems(1),
InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName] = Right$(.SelectedItems(1),
Len(.SelectedItems(1)) - InStrRev(.SelectedItems(1), "\"))
Me![txtLocalFileName].SetFocus
End If
End With

--
Joe Fallon
Access MVP



I have a large table that needs regular "updates" to it via
frequent Comma Delimited text files.

I'd like a directory dialog box to open, allow me to select
the "latest" text update file, and then use the TransferText
command to import/append that data into a "temp" table.
(tblTempUpdateData) Then I'll use that temp table to run 2
queries... one to "update" the main table with any corrections to
existing recs, and another to "append" any new records.

I just can't seem to find out what procedure/function/command
I can use to open up a "Find your file" dialog box... and then
use the filename selected as an argument in my TransferText
command.

Thanks for any help in advance,
Al Camp