CODEWITHSUNDEEP
ms-accessvba
Rafa3lOneiL
Rafa3lOneiL

Reputation: 89

How to insert file names into access table with VBA

I'd like some help ... I have a sub from the access where I read all files with .gif extension in a certain folder and would like to know how to proceed to insert all the names that were read in an access table, I made the code below but it is not working ... can you help me?

Sub realAllFiles ()

Dim varFile As Variant
Dim CustomerFolder As String
Dim fDialog As FileDialog, result As Integer
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog

.AllowMultiSelect = True
.Title = "Upload | Selecione a pasta de imagens capturadas..."
.Filters.Clear
.Filters.Add "All files", "*.gif*"
.InitialFileName = "H:\Gestao de Dados Processamento\FAP\Baixa de Pendencia DUT\Novo Fluxo"

If .Show = True Then
      For Each varFile In .SelectedItems
         CustomerFile = varFile
      Next
      Else: Exit Sub
   End If
End With

DoCmd.TransferText acImportDelim, , "MyTable", CustomerFile, False

End Sub

Upvotes: 2

Views: 2311

Answers (1)

Kostas K.
Kostas K.

Reputation: 8518

There are a number of ways to do this.

The first approach is to set-up an import query passing the file name as parameter.

The query's SQL:

PARAMETERS [prmFileName] Text (255);
INSERT INTO T ( FieldName )  '<- Change to the actual table and field names
SELECT [prmFileName] As _FileName;

Then call the query inside the loop:

Sub realAllFiles()

    Dim varFile As Variant
    Dim CustomerFolder As String
    Dim fDialog As FileDialog, result As Integer
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

    With fDialog
        .AllowMultiSelect = True
        .Title = "Upload | Selecione a pasta de imagens capturadas..."
        .Filters.Clear
        .Filters.Add "All files", "*.gif*"
        .InitialFileName = "H:\Gestao de Dados Processamento\FAP\Baixa de Pendencia DUT\Novo Fluxo"
    End With

    If fDialog.Show = True Then
        For Each varFile In fDialog.SelectedItems
            With CurrentDb().QueryDefs("ImportQueryName") '<- change to the above query's name
                .Parameters("[prmFileName]").Value = varFile
                .Execute dbFailOnError
            End With
        Next
    End If
End Sub

The second approach is through a recordset:

Sub realAllFiles()

    Dim varFile As Variant
    Dim CustomerFolder As String
    Dim fDialog As FileDialog, result As Integer
    Dim rs As DAO.Recordset

    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    Set rs = CurrentDb().OpenRecordset("TableName") '<- change to the actual table name

    With fDialog
        .AllowMultiSelect = True
        .Title = "Upload | Selecione a pasta de imagens capturadas..."
        .Filters.Clear
        .Filters.Add "All files", "*.gif*"
        .InitialFileName = "H:\Gestao de Dados Processamento\FAP\Baixa de Pendencia DUT\Novo Fluxo"
    End With

    If fDialog.Show = True Then
       For Each varFile In fDialog.SelectedItems
          rs.AddNew
          rs("Fieldname").Value = varFile '<- change to the actual field name
          rs.Update
       Next
    End If

    If Not rs Is Nothing Then rs.Close
End Sub

Upvotes: 1

Related Questions