Run-time error '3078' question... 2

[irethedo]

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:

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:

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

 

[dhookom]

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

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

 

[irethedo]

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

 

[dhookom]

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

 

[combo]

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

combo

 

[dhookom]

Combo,
You have better vision 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

 

[Andrzejek]

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.


---- Andy

There is a great need for a sarcasm font.

 

[irethedo]

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

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:

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

 

[dhookom]

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

 

[irethedo]

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:

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

 

[dhookom]

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

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

 

[strongm]

>and the code was compiled

Andrzejek, why was this crossed out?

 

[Andrzejek]

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


---- Andy

There is a great need for a sarcasm font.