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.