?
Solved

Access 2010 procedure willnot openrecordset for table

Posted on 2014-09-10
7
Medium Priority
?
542 Views
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
    rst.MoveFirst
    Do While Not rst.EOF
        rstTable.AddNew
        rstTable!ImportID = rst!ImportID
        rstTable!LastName = rst![Last Name]
        rstTable!FirstName = rst![First Name]
        rstTable!Properties = rst!Project
        rstTable.Update
        rst.MoveNext
   Loop
Else
    'do Nothing
End If

rst.Close
rstTable.Close

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

End Sub
0
Comment
Question by:ssmith94015
[X]
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
7 Comments
 
LVL 120

Accepted Solution

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

Set rstTable = dbs.OpenRecordset("tblImportedMonths")
0
 

Author Closing Comment

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

Jim.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

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

Sandra
0
 
LVL 58
ID: 40323062
Sandra,

 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.

Jim.
0
 

Author Comment

by:ssmith94015
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.

Sandra
0
 
LVL 58
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>

Jim.
0

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

719 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