Expected user-defined type, not project

[mongoose]

I've looked through several pages on SF on this error. This is the first time I've tried to add records to Access via VBA. Here is my code:

Option Compare Database




Public Sub Retrieve_SOPS()
' Retrieve SOP files
    'Record starting timer - BEGIN
    Dim StartTime As Double
    StartTime = Timer
    
    'Set network folder path
    Const FolderPath As String = "\\JACKSONVILLE-DC\Common\SOP's for JV\SOPs Final"
    
    'Instantiate FSO
    Dim oFSO As Object
    Dim oFolder As Object
    Dim oFiles As Object
    Dim oFile As Object
    Set oFSO = CreateObject("Scripting.FileSystemObject")
    Set oFolder = oFSO.GetFolder(FolderPath)
    Set oFiles = oFolder.Files
    
    'Instantiate DAO
    Dim db As DAO
    Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblSOP", dbOpenDynaset)
    
    Dim v As Variant
    
    'Loop through each file in FSO
    For Each oFile In oFiles
        'Remove temporary/hidden files
        If (oFile.Attributes And 2) <> 2 Then
            'Split filename
            v = Split(oFile.Name, "-")
            
            ' Instantiate Necessary Variables
            Dim file_path As String
            Dim file_id As Integer
            Dim file_title As String
            Dim lang_code As String
            Dim creation_date As String
                        
            file_path = oFile.Path
            file_id = v(2)
            file_title = v(4)
            lang_code = v(5)
            'If dimension in array exists; Remove file extension
            If UBound(v) >= 6 Then
                creation_date = v(6)
            End If
            
            With rs
                .AddNew
                
                .Fields("file_path").Value = file_path
                .Fields("file_id").Value = file_id
                .Fields("file_title").Value = file_title
                .Fields("lang_code").Value = lang_code
                If UBound(v) >= 6 Then
                    .Fields("creation_date").Value = creation_date
                End If
            End With
        End If
        
        'Stop For Loop (TEMP)
        Exit Sub
    Next oFile
End Sub

Then I get this error: "Compile error: Expected user-defined type, not project"
I'm reading "Microsoft Access 2019 Bible" By Wiley.

[OBP]

I do not know what is causing the error, but I can identify one error in the With Addnew code and that is that you do not have a .update & .Bookmark = rs.LastModified at the end of it before the End with.
However to identify a compile error I would copy your code to text program like notepad and then delete all of it from the within the Public Sub to End sub statements.
I would the paste it back a block at a time and run the code.
In this way you can narrow down where the Compile Error is occurring.

I also have some recommendations for your code.
I would move the Dim statements that are inside the For Each Loop to before the loop as it is carrying out the dim statement on every pass.
I do not set the recordset to DAO, I just set them to an Object as you have done with the FSO.

I have found is unnecessary to use the .value when assigning a value to a recordset field.

Let me know if you can't find the error and I will try it by incorporating your code in a database.

ps here is an alternative to FSO

Dim FileNm, FilePathName, Path, FileNameList() As String
Dim FileCount As Integer
DoCmd.SetWarnings False
Path = Me.FilePath ' from form
FileNm = Dir(Path & "")
Search_path = Path ' where ?
Search_Filter = "*.txt" ' what ?
Docname = Dir(Search_path & "" & Search_Filter)
Do Until Docname = "" ' build the collection
FilePathName = Path & Docname
DoCmd.TransferText transferType:=acImportDelim, TableName:="Test", FileName:="C:\Users\A C\Downloads\Benjamins.txt", hasfieldnames:=True

Docname = Dir
Loop

[mongoose]

I have solved the problem. I had a typo in the code where I had instantiated DAO. I forgot the to add ".Database" after it. Also, as you pointed out, I forget the .Update as well.

I will move my Dim declarations and streamline it a bit further.

I'm definitely enjoying getting further into VBA. I started off using it in Excel; now I have used it to make custom Word files and now using it for Access.

Thank you very much!

[OBP]

Very good.
It is amazing just what VBA can do, I have only scratched the surface for the most common uses.
Manipulating Access itself is very powerful, but also a bit scary.