Access 2010 procedure willnot openrecordset for table

Posted on 2014-09-10
Last Modified: 2014-09-15
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
Question by:ssmith94015
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 120

Accepted Solution

Rey Obrero (Capricorn1) earned 500 total points
ID: 40315841
how about

Set rstTable = dbs.OpenRecordset("tblImportedMonths")

Author Closing Comment

ID: 40315854
Thank you, works even though all the samples I came across did have the second statement.
LVL 57
ID: 40316751
<<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.

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.


Author Comment

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

LVL 57
ID: 40323062

 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.


Author Comment

ID: 40323286
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.

LVL 57
ID: 40323314
<<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>


Featured Post

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA.…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question