If record exists, append table then move on to next

Cassie_H

New Member
Joined
Dec 29, 2011
Messages
9
Hello,

I have a database that checks the names of subfolders in a folder and then creates a table for them in the database, named tblTempProjects. What I then want it to do is check to see if these folder names already exist on another table, tblProjects, if the name does exist, then continue down the list of names, if it doesn't then append the table with the new folder name.

Both tables have the same fields, 'ProjectName' and 'Active'. They're both text and Boolean. Not sure what I'm doing wrong, not very good with Access at all!

I'm getting a runtime 3070 error when I'm trying to get it to search the tables. This is what I have so far:

Code:
Private Sub UpdateProjects_Click()
    
    Dim dbs               As Database
    Dim tdfNew          As TableDef
    Dim Folder           As String
    Dim strFind          As String
    Dim DirectoryName
    Dim sql
    Dim rsProject       As DAO.Recordset
    Dim rsTemp         As DAO.Recordset

    
    Set dbs = CurrentDb
    Folder = "C:\NAT\BUS\PAY\PAM\Database\QA\"
     
   For Each mytable In dbs.TableDefs
      If mytable.Name = "tblTempProjects" Then
         dbs.TableDefs.Delete "tblTempProjects"
      End If
   Next

   Set tdfNew = dbs.CreateTableDef("tblTempProjects")

   With tdfNew
     .Fields.Append .CreateField("ProjectName", dbText)
     .Fields.Append .CreateField("Active", dbBoolean)
   End With
   
   dbs.TableDefs.Append tdfNew

     DirectoryName = Dir(Folder, vbDirectory)
     Do Until DirectoryName = ""
         If DirectoryName <> "." And DirectoryName <> ".." Then
             If (GetAttr(Folder & DirectoryName) And vbDirectory) = vbDirectory Then
             sql = "INSERT INTO tblTempProjects ( ProjectName ) " & _
                 "SELECT '" & DirectoryName & "' AS Directory"
         CurrentProject.Connection.Execute sql
         End If
        End If
         DirectoryName = Dir
     Loop

Set rsProject = CurrentDb.OpenRecordset("tblProjects", dbOpenDynaset)
Set rsTemp = CurrentDb.OpenRecordset("tblTempProjects", dbOpenDynaset)

Do While Not rsProject.EOF
     rsTemp.MoveFirst
     
     Do While Not rsTemp.EOF
         strFind = rsProject!ProjectName
         rsTemp.FindFirst strFind
         If rsTemp.NoMatch Then
           rsProject.AddNew
             rsProject!ProjectName = rsTemp!ProjectName
           rsProject.Update
         End If
       rsTemp.MoveNext
     Loop

   rsProject.MoveNext
 Loop

End Sub

Assistance would be greatly appreciated!
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,185
Office Version
  1. 365
Platform
  1. Windows
The error is telling you what cannot be found, yes? Post the message and indicate on which line of code the error is raised.
 

Forum statistics

Threads
1,137,117
Messages
5,679,716
Members
419,853
Latest member
hc9587

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top