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
  • 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.

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

820 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