[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1185
  • Last Modified:

Access - Error says it can't find input table or query

I have some code that is giving me this error whenever I try to run it:
"Run-time error '3078': The Microsoft Access database engine cannot find the input table or query". Make sure it exists and that its name is spelled correctly."

I have checked the table it references and the name is correct.

Is there something wrong in the code I have written?

Private Sub btn_AddtoTeam_Click()
Dim db As DAO.Database
Dim rst As Recordset
Dim strsql As String
Dim TNAME As String

Set db = CurrentDb

Set rst = db.OpenRecordset(strsql)

strsql = "Select Top 1 * from Team"


TNAME = Forms!frm_Staff!FName & " " & Forms!frm_Staff!Lname

rst.AddNew
    rst!TEAMName = TNAME
rst.Update

Set rst = Nothing
Set db = Nothing

Exit Sub

End Sub

Open in new window

0
Megin
Asked:
Megin
  • 6
  • 6
  • 3
1 Solution
 
Anthony BerenguelCommented:
What line does it error on? Have you spelled the table name/ query correctly?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Reverse these two statements
Set rst = db.OpenRecordset(strsql)

strsql = "Select Top 1 * from Team"

to
strsql = "Select Top 1 * from Team"

Set rst = db.OpenRecordset(strsql)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You were referring to strsql before it was defined :-)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Anthony BerenguelCommented:
try this
Private Sub btn_AddtoTeam_Click()
Dim db As DAO.Database
Dim rst As Recordset
Dim strsql As String
Dim TNAME As String

Set db = CurrentDb
strsql = "Select Top 1 * from Team"
Set rst = db.OpenRecordset(strsql)




TNAME = Forms!frm_Staff!FName & " " & Forms!frm_Staff!Lname

rst.AddNew
    rst!TEAMName = TNAME
rst.Update

Set rst = Nothing
Set db = Nothing

Exit Sub

End Sub

Open in new window

0
 
Anthony BerenguelCommented:
Sorry, joe! I wasn't trying to step on toes!
0
 
MeginAuthor Commented:
Woohooo!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!  I WORKS!!!!!


THANK YOU THANK YOU THANK YOU!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
See my post in your previous Q re simplifying this code ...

mx
0
 
MeginAuthor Commented:
I just looked closer at it.
(I get really excited when things actually work, and sometimes I glaze over what is said afterwards.)

I have a few questions:

You suggested I remove the Dim statement declaring that db is dao.database. Can I remove that from other pieces of code I use in my Access database? Or, rather, how come I don't need it?

When I use the With statement, does that just mean that it implied that I mean to put "rst" in front of everything that comes after it? I just want to verify because it looks like that is exactly what you did.

With rst
        .AddNew
        ![TeamName] = TNAME
        .Update
        .Close
     End With  'rst

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"When I use the With statement, does that just mean that it implied that I mean to put "rst" in front of everything that comes after it? I"

Exactly.  Saves a LOT of typing ... and Intellisense still works  ... and technically, it executes faster.



"Can I remove that from other pieces of code I use in my Access database? "

The answer is ... "it depends"

In the case above, there is really only one place where CurrentDB needs to be used/referenced ... which is on the OpenRecordSet statement.  So, no need to Dim an Object variable and have code to Set in and the cleanup code.

However, if you have a routine where you need to use CurrentDB several places in the code, then you will want to Dim an Object Variable ... and use it instead of continually referring to CurrentDB ... which would technically be a performance hit.

OR ... you can also do

With CurrentDB
      ' code which refers to CurrentDB several times (and other code - whatever)

End With

For example (although this example is overkill)

Private Sub btn_AddtoTeam_Click()

Dim rst As DAO.Recordset
Dim strsql As String
Dim TNAME As String

TNAME = Form!frm_Staff.Form!FName & " " & Form!frm_Staff.Form!Lname
strsql = "Select Top 1 * from Team"

With CurrentDB

  Set rst = .OpenRecordset(strsql)   ' CurrentDB is implied here
   With rst
       .AddNew
       ![TeamName] = TNAME
       .Update
      .Close
    End With   'rst

End With   'CurrentDB

Set rst = Nothing

End Sub


BTW ... this cannot be working either:

TNAME = Form!frm_Staff.Form!FName & " " & Form!frm_Staff.Form!Lname

s/b

TNAME = Forms!frm_Staff.Form!FName & " " & Forms!frm_Staff.Form!Lname
0
 
MeginAuthor Commented:
I am going to print this for reference.

Thank you for taking the time to explain all of this to me!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
No problem and you are welcome.

Where are you located btw ?
0
 
MeginAuthor Commented:
I am in Seattle. You?
0
 
MeginAuthor Commented:
I have another question about this that may complicate things. Do you mind if I ask you here, or would it be better if I opened another question?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Rancho Cucamonga CA :-)
(same time zone at least)

I will be in Seattle (Bellevue/Redmond) in Nov for the Microsoft Global MVP Summit.

Another Q ...
I don't mind but ... per EE rules, you should open another Q ... just to keep it on the up and up :-)
0
 
MeginAuthor Commented:
I just put up the second next question.

I used to live near Rancho Cucamonga!  Seattle has much nicer weather this time of year. ;)
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 6
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now