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 119

Accepted Solution

Rey Obrero 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.

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
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 …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now