Access 2010 procedure willnot openrecordset for table

The below stops at the openrecordsset for the table but it appears to be written correctly.

Public Sub CheckImportDates()
'checks to see if there are importID's for employee.  If not, adds
'the ImportId information to the table
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstTable As DAO.Recordset
Dim strDoesNotExist As String
Dim qdfDoesNotExist As QueryDef

strDoesNotExist = "SELECT DISTINCT tblTEMPImport.ImportID, tblTEMPImport.[Last Name], " & _
    "tblTEMPImport.[First Name], tblTEMPImport.Project " & _
    "FROM tblTEMPImport " & _
    "WHERE (((Exists (SELECT ImportID, LastName, FirstName, ProjectName " & _
    "FROM tblImportedMonths " & _
    "WHERE  tblTEMPImport.ImportID= tblImportedMonths.ImportID AND " & _
    "tblTEMPImport.[First Name] = tblImportedMonths.FirstName AND " & _
    "tblTEMPImport.[Last Name] = tblImportedMonths.LastName AND " & _
    "tblTEMPImport.Project = tblImportedMonths.ProjectName))=False)) "

If fntDoesObjectExist("qryDoesNotExist", "Query") Then DoCmd.DeleteObject acQuery, "qryDoesNotExist"
Set dbs = CurrentDb
Set qdfDoesNotExist = dbs.CreateQueryDef("qryDoesNotExist", strDoesNotExist)
Set rst = qdfDoesNotExist.OpenRecordset
Set rstTable = dbs.OpenRecordset("tblImportedMonths", dbOpenTable) "ERRORS OUT HERE

If rst.RecordCount > 0 Then
    Do While Not rst.EOF
        rstTable!ImportID = rst!ImportID
        rstTable!LastName = rst![Last Name]
        rstTable!FirstName = rst![First Name]
        rstTable!Properties = rst!Project
    'do Nothing
End If


Set rst = Nothing
Set rstTable = Nothing
Set dbs = Nothing

End Sub
Who is Participating?
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
how about

Set rstTable = dbs.OpenRecordset("tblImportedMonths")
ssmith94015Author Commented:
Thank you, works even though all the samples I came across did have the second statement.
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<I came across did have the second statement. >>

 You only can open a table as a table if it's local to the current DB.  

If the table is remote, you can't do that.

Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

ssmith94015Author Commented:
Jim, does this apply to linked tables?  I want to have a front end and back end ACCESS 2010 databases.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:

 A linked table it a remote table, so you can't use the dbOpenTable argument.   The recordset must be something else.

 That really doesn't limit you as much as you would think because:

1. The only time you really need to open a recordset with dbOpenTable is if you plan on using the .Seek method.

2. You can do a workaround:  Open the BE database as a database first, then open the recordset as a table rather than doing it through CurrentDB(), which is of course the current database.

That would look like this:

        ' Got a key.  Does it need to be tested for unique?
        ' Note assumes a JET linked table.
190     If (rst1![UniqueKey]) Then
200       Set tdfAttached = dbCurrent.TableDefs(strTableName)
210       strPath = tdfAttached.Connect
220       If strPath <> "" Then
230         If dbRemote Is Nothing Then
240           strPath = right$(strPath, Len(strPath) - InStr(strPath, "="))
250           Set dbRemote = Workspaces(0).OpenDatabase(strPath, False, True)
260           Set rst2 = dbRemote.OpenRecordset(strTableName, DB_OPEN_TABLE)
270         End If
280       Else
290         Set rst2 = dbCurrent.OpenRecordset(strTableName, DB_OPEN_TABLE)
300       End If

310       rst2.Index = "PrimaryKey"
320       rst2.Seek "=", lngKeyValue

330       If Not (rst2.NoMatch) Then
340         rst2.Close
350         GoTo GetAKey
360       Else
370         rst2.Close
380       End If

390     End If

 Note the open of the database on line 250, then the recordset on 260.

ssmith94015Author Commented:
Thanks Jim.  The thing I really like about your answers is the explanations of what is going on and why.  This actually addresses another issue about uniqueness.  Yes, linked tables are remote, totally had a brain misfire on that.  I have remote users, some in LA and NY that connect to my system here in San Francisco and was thinking of that.

Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Yes, linked tables are remote, totally had a brain misfire on that.  >>

 Well I'm on my 3rd cup of coffee and still misfiring a bit, so join the club<g>

All Courses

From novice to tech pro — start learning today.