X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> How To Import Text File With A Variable Name Into An Existing Access Table Via Access VBA, Access 2013    
post Nov 17 2017, 01:31 PM

Posts: 18
Joined: 9-June 16

Hello Gurus,
Thank you for taking a time on reading my post and trying to help me. I am trying to build a code that will import a text file with a variable name into an existing table that is located in access db.

I built the below listed code but it is giving me an error stating that the location of the file is not a local file.
Can some one help me to resolve this?
Thank you for your time in helping me.

Function ImportCumulativeBeneFilePriorYear()
Dim fDialog As Office.FileDialog
Dim strComPath As String
Dim strFilePath As String
Dim strTable As String

strComPath = '"\\Here is a path to my file\"

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
With fDialog
.InitialFileName = strComPath
.AllowMultiSelect = False
.Title = "Please select the most recent File to import."
If .Show = True Then
strFilePath = .SelectedItems(1)
MsgBox "You clicked Cancel in the file dialog box."
End If
End With

DoCmd.SetWarnings False

DoCmd.TransferText TransferType:=acImportFixed, _
SpecificationName:="ImportCumulativeBeneDataFile", _
TableName:="CumulativeBeneficiaryDataFileCurrentYear", _
FileName:=strFilePath, _

DoCmd.SetWarnings True
End Function
Go to the top of the page
post Nov 21 2017, 10:34 AM

Posts: 848
Joined: 25-April 14

save 1 of the files as a temp name: c:\temp\File2Import.txt
link this file as an external linked table.
make an import append query to post the data to your internal db.

then your steps are:
1.user select file to import,
2. vb code save to the same file everytime : FileCopy vSrc , "c:\temp\File2Import.txt"
3. run import append query

Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    22nd March 2018 - 10:11 PM