Import Comma Delimited


A

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
 
Ad

Advertisements

J

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
 
A

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

Joe Fallon said:
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



Al Camp said:
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
 
J

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

Joe Fallon said:
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



Al Camp said:
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
 
Ad

Advertisements

J

Joe Fallon

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



John Nurick said:
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

Joe Fallon said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top