INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Run-time error '3078' question...

Run-time error '3078' question...

(OP)

I am getting a Run-time error '3078' which states the Microsoft Access database engine cannot find input table or query when I attempt to execute the following code:

CODE

strSq1 = "SELECT * INTO ViewOrderData_tbl FROM Total_NW_tbl WHERE (((Total_NW_tbl.SO_NO)=1492198995))"
CurrentDb.Execute strSql 

but when I place the following into the SQL of a query it works just fine:

CODE

SELECT * INTO ViewOrderData_tbl
FROM Total_NW_tbl
WHERE (((Total_NW_tbl.SO_NO)=1492198995)); 

Why doesn't this work in the first instance?

Thanks

RE: Run-time error '3078' question...

I'm not sure if it will make any difference but I would try:

CODE --> vba

strSq1 = "SELECT * INTO ViewOrderData_tbl FROM Total_NW_tbl WHERE SO_NO=1492198995"
debug.print strSQL
CurrentDb.Execute strSql, dbFailOnError 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run-time error '3078' question...

(OP)

Quote (I'm not sure if it will make any difference but I would try:
[code)


strSq1 = "SELECT * INTO ViewOrderData_tbl FROM Total_NW_tbl WHERE SO_NO=1492198995"
debug.print strSQL
CurrentDb.Execute strSql, dbFailOnError]

Thanks Duane- Same result with the debug.print and dbFailonError - Run-time error '3078'
(and nothing shows up in the immediate window.)

This is very strange as the error message indicates not finding a table or query and suggests
checking the spelling but the exact same SQL statement, when placed in a query
(and adding the ";" at the end) works flawlessly.

I am confused why this is...

RE: Run-time error '3078' question...

If nothing shows up in the debug window can you confirm the code is even being run? Is there more to the code that you aren't showing us? Did you attempt to set a break point and step through the code?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run-time error '3078' question...

In your code strSq1<>strSql (digit 1 vs. letter l), so you execute empty string.

combo

RE: Run-time error '3078' question...

Combo,
You have better vision glasses than I do. Great sleuthing...

I expect if the code had Option Explicit in the declarations and the code was compiled the error would have been discovered immediately.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run-time error '3078' question...

"if the code had Option Explicit in the declarations and the code was compiled " then this code would never compile and stop on/point to the issue right away. smile


---- Andy

There is a great need for a sarcasm font.

RE: Run-time error '3078' question...

(OP)

Quote:

combo (TechnicalUser)15 Dec 17 16:28 In your code strSq1<>strSql (digit 1 vs. letter l), so you execute empty string.][/code]

Quote:


combo (TechnicalUser)15 Dec 17 16:28
In your code strSq1<>strSql (digit 1 vs. letter l), so you execute empty string.

That was a good catch and thank you or catching that..

I changed this and now have:

CODE

strSql = "SELECT * INTO ViewOrderData_tbl FROM Total_NW_tbl WHERE SO_NO=1492198995"
Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError 

but now I am getting a Run-Time error '3010' Table 'ViewOrderData_tbl' already exists.

Do I need to delete this table before running this and if so, what is the best way to do this?

thanks again

RE: Run-time error '3078' question...

I typically delete all of the records from an existing table and then use an insert query. This type of process create bloat so the data file might need to be compacted periodically.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run-time error '3078' question...

(OP)

Thanks Duane-

I want to change the hard coded number of "1492198995" with a value that is input from the user with the following variation of the code:

CODE

Dim PWD As String
Dim strSql As String

 strSql = "Delete * From ViewOrderData_tbl"
 CurrentDb.Execute strSql
 
PWD = InputBox("Please enter the sales Order Number that you wish to view...", "", Default, 100, 100)

strSql = "Delete * From ViewOrderData_tbl"
CurrentDb.Execute strSql

strSql = "INSERT INTO ViewOrderData_tbl SELECT Total_NW_tbl.SO_NO, * FROM Total_NW_tbl WHERE (SO_NO = PWD)"
Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError 

but when I substitute


strSql = "INSERT INTO ViewOrderData_tbl SELECT Total_NW_tbl.SO_NO, * FROM Total_NW_tbl WHERE SO_NO = 1492198995"

with

strSql = "INSERT INTO ViewOrderData_tbl SELECT Total_NW_tbl.SO_NO, * FROM Total_NW_tbl WHERE SO_NO = PWD"

I get a Run-time error '3061' Too few parameters so I am guessing that the syntax for how I am using the"PWD" needs to be
tweaked a little bit...

RE: Run-time error '3078' question...

I think this should work however
  • I never do an INSERT without specifying all of the field names.
  • Your code will also create two copies of SO_NO.
  • I never use InputBox when a text box on a form can be used.
  • Why would you name your variable PWD when it's not a password
  • Is the SO_NO field numeric? If so, don't DIM it as string.

CODE --> vba

Dim PWD As String
Dim strSql As String

 strSql = "Delete * From ViewOrderData_tbl"
 CurrentDb.Execute strSql
 
PWD = InputBox("Please enter the sales Order Number that you wish to view...", "", Default, 100, 100)

strSql = "Delete * From ViewOrderData_tbl"
CurrentDb.Execute strSql

strSql = "INSERT INTO ViewOrderData_tbl SELECT Total_NW_tbl.SO_NO, * FROM Total_NW_tbl WHERE SO_NO = " & PWD
Debug.Print strSql
CurrentDb.Execute strSql, dbFailOnError 

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Run-time error '3078' question...

>and the code was compiled

Andrzejek, why was this crossed out?

RE: Run-time error '3078' question...

Well, the code would never compiled.
The point is - use OPTION EXPLICIT pc2


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close