Consulting

Results 1 to 4 of 4

Thread: Expected user-defined type, not project

  1. #1
    VBAX Regular
    Joined
    Jul 2019
    Posts
    42
    Location

    Expected user-defined type, not project

    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.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,089
    Location
    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
    Last edited by OBP; 10-31-2019 at 04:46 AM. Reason: added code

  3. #3
    VBAX Regular
    Joined
    Jul 2019
    Posts
    42
    Location
    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!

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,089
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •