DoCmd.RunSQL who could check my sql?

  • Thread starter Thread starter Wouter
  • Start date Start date
W

Wouter

Hiya all,



I need some help to get my code running. I must say that I'm quit new with
VB / VBA so maybe my question is a bit stupid. But anyway I would be really
happy when the following code will run nice and smoothly.



The first question I have: Is the SQL after the DoCmd.sql the same code as
the sql access uses when you look in query designer view / sql? Because my
code comes straight from there.



The second question I have, could someone check the code and see what's
going on. The query stops when he is at:

DoCmd.RunSQL (mySQL) so apparantly the sql in red is incorrect.

( I know that the table collumns has spaces in the name and that that isn't
very good for the sql, access uses [] to work with it, and i cant change
these name because it isn't my database.



I expect that the sql behind mySQL1 also is incorrect like the first one and
so on.



Your help would be grateful!



Thanks in advance,



Wouter






Option Compare Database
Option Explicit


Sub UpdateCustomerCode()


Dim I As Integer
Dim mySQL As String


mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI ) SELECT
[1381 data].Code, [1381 data].Range, [1381 data].[EBC Tier], [1381 data].NNI
FROM [1381 data] WHERE ((([1381 data].Code)=1381));"


DoCmd.SetWarnings False



For I = 1 To 8



Select Case I



Case 1
DoCmd.RunSQL (mySQL)
Dim mySQL1 As String
mySQL1 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138120 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL1)



Case 2
DoCmd.RunSQL (mySQL)
Dim mySQL2 As String
mySQL2 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138124 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL2)



Case 3
DoCmd.RunSQL (mySQL)
Dim mySQL3 As String
mySQL3 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138125 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL3)



Case 4
DoCmd.RunSQL (mySQL)
Dim mySQL4 As String
mySQL4 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138126 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL4)



Case 5
DoCmd.RunSQL (mySQL)
Dim mySQL5 As String
mySQL5 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138127 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL5)



Case 6
DoCmd.RunSQL (mySQL)
Dim mySQL6 As String
mySQL6 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138128 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL6)



Case 7
DoCmd.RunSQL (mySQL)
Dim mySQL7 As String
mySQL7 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138129 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL7)



Case 8
DoCmd.RunSQL (mySQL)
Dim mySQL8 As String
mySQL8 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 1382 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL8)



End Select



Next



DoCmd.SetWarnings True

MsgBox ("Table is ready")

End Sub
 
Hi,
The first question I have: Is the SQL after the DoCmd.sql the same code as
the sql access uses when you look in query designer view / sql? Because my
code comes straight from there.
yes

The second question I have, could someone check the code and see what's
going on. The query stops when he is at:

DoCmd.RunSQL (mySQL) so apparantly the sql in red is incorrect.
Click to expand...

there is not need to put brackets, just leave them out:

DoCmd.RunSQL mySQL

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
Hi,

I left the brackets out, but still same error: "run-time error 3464: data
type mismatch in criteria expression".

Who have got some ideas?

Thanks
 
The SQL string looks correct. Try

DoCmd.RunSQL MySQL 'No Parentheses

I might rewrite the entire sub as

Option Compare Database
Option Explicit


Sub UpdateCustomerCode()
Dim I As Integer
Dim mySQL As String
Dim iCode as Long

mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI )" & _
"SELECT " & ICode & ", [1381 data].Range" & _
", [1381 data].[EBC Tier], [1381 data].NNI " & _
" FROM [1381 data] WHERE ((([1381 data].Code)=1381));"

'Comment out next line until code runs without error
'DoCmd.SetWarnings False

For I = 1 To 8
SELECT Case I
Case 1
iCode = 138120
Case 2
iCode = 138124
Case 3
iCode = 138125
....
End Select

DoCmd.RunSQL mySQL

Next

DoCmd.SetWarnings True

MsgBox ("Table is ready")

End Sub


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hiya all,



I need some help to get my code running. I must say that I'm quit new with
VB / VBA so maybe my question is a bit stupid. But anyway I would be really
happy when the following code will run nice and smoothly.



The first question I have: Is the SQL after the DoCmd.sql the same code as
the sql access uses when you look in query designer view / sql? Because my
code comes straight from there.



The second question I have, could someone check the code and see what's
going on. The query stops when he is at:

DoCmd.RunSQL (mySQL) so apparantly the sql in red is incorrect.

( I know that the table collumns has spaces in the name and that that isn't
very good for the sql, access uses [] to work with it, and i cant change
these name because it isn't my database.



I expect that the sql behind mySQL1 also is incorrect like the first one and
so on.



Your help would be grateful!



Thanks in advance,



Wouter






Option Compare Database
Option Explicit


Sub UpdateCustomerCode()


Dim I As Integer
Dim mySQL As String


mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI ) SELECT
[1381 data].Code, [1381 data].Range, [1381 data].[EBC Tier], [1381 data].NNI
FROM [1381 data] WHERE ((([1381 data].Code)=1381));"


DoCmd.SetWarnings False



For I = 1 To 8



Select Case I



Case 1
DoCmd.RunSQL (mySQL)
Dim mySQL1 As String
mySQL1 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138120 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL1)



Case 2
DoCmd.RunSQL (mySQL)
Dim mySQL2 As String
mySQL2 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138124 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL2)



Case 3
DoCmd.RunSQL (mySQL)
Dim mySQL3 As String
mySQL3 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138125 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL3)



Case 4
DoCmd.RunSQL (mySQL)
Dim mySQL4 As String
mySQL4 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138126 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL4)



Case 5
DoCmd.RunSQL (mySQL)
Dim mySQL5 As String
mySQL5 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138127 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL5)



Case 6
DoCmd.RunSQL (mySQL)
Dim mySQL6 As String
mySQL6 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138128 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL6)



Case 7
DoCmd.RunSQL (mySQL)
Dim mySQL7 As String
mySQL7 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138129 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL7)



Case 8
DoCmd.RunSQL (mySQL)
Dim mySQL8 As String
mySQL8 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 1382 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL8)



End Select



Next



DoCmd.SetWarnings True

MsgBox ("Table is ready")

End Sub
Click to expand...
 
Check and make sure that [1381 data].Code is a number field and not a text field.

If this is a text field then you need to add apostrophes around the 1381.

mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI ) SELECT
[1381 data].Code, [1381 data].Range, [1381 data].[EBC Tier], [1381 data].NNI
FROM [1381 data] WHERE ((([1381 data].Code)='1381'));"

OR use quote marks (have to be doubled up to get one quote inside a string)

mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI ) SELECT
[1381 data].Code, [1381 data].Range, [1381 data].[EBC Tier], [1381 data].NNI
FROM [1381 data] WHERE ((([1381 data].Code)=""1381""));"


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
John,

I've tried your code but retrieved the following error: datatype mismatch in
criteria expression

What i try to do is this.
I want to loop the following:
Append query tbl_1381 data =named "1381 data" to tbl_Customer IDA =named
"Customer IDA"
Then I want to update the values in column "Code" where value ="1381" to
case1 number, case2 number and so on.

Where are the & signs for? To concatenate?
Why are there "" signs around a part of the SQL sintax?

A lot of why's and how's but I've got to understand this!

Cheers!


John Spencer said:
The SQL string looks correct. Try

DoCmd.RunSQL MySQL 'No Parentheses

I might rewrite the entire sub as

Option Compare Database
Option Explicit


Sub UpdateCustomerCode()
Dim I As Integer
Dim mySQL As String
Dim iCode as Long

mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI )" & _
"SELECT " & ICode & ", [1381 data].Range" & _
", [1381 data].[EBC Tier], [1381 data].NNI " & _
" FROM [1381 data] WHERE ((([1381 data].Code)=1381));"

'Comment out next line until code runs without error
'DoCmd.SetWarnings False

For I = 1 To 8
SELECT Case I
Case 1
iCode = 138120
Case 2
iCode = 138124
Case 3
iCode = 138125
....
End Select

DoCmd.RunSQL mySQL

Next

DoCmd.SetWarnings True

MsgBox ("Table is ready")

End Sub


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hiya all,



I need some help to get my code running. I must say that I'm quit new with
VB / VBA so maybe my question is a bit stupid. But anyway I would be really
happy when the following code will run nice and smoothly.



The first question I have: Is the SQL after the DoCmd.sql the same code as
the sql access uses when you look in query designer view / sql? Because my
code comes straight from there.



The second question I have, could someone check the code and see what's
going on. The query stops when he is at:

DoCmd.RunSQL (mySQL) so apparantly the sql in red is incorrect.

( I know that the table collumns has spaces in the name and that that isn't
very good for the sql, access uses [] to work with it, and i cant change
these name because it isn't my database.



I expect that the sql behind mySQL1 also is incorrect like the first one and
so on.



Your help would be grateful!



Thanks in advance,



Wouter






Option Compare Database
Option Explicit


Sub UpdateCustomerCode()


Dim I As Integer
Dim mySQL As String


mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI ) SELECT
[1381 data].Code, [1381 data].Range, [1381 data].[EBC Tier], [1381 data].NNI
FROM [1381 data] WHERE ((([1381 data].Code)=1381));"


DoCmd.SetWarnings False



For I = 1 To 8



Select Case I



Case 1
DoCmd.RunSQL (mySQL)
Dim mySQL1 As String
mySQL1 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138120 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL1)



Case 2
DoCmd.RunSQL (mySQL)
Dim mySQL2 As String
mySQL2 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138124 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL2)



Case 3
DoCmd.RunSQL (mySQL)
Dim mySQL3 As String
mySQL3 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138125 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL3)



Case 4
DoCmd.RunSQL (mySQL)
Dim mySQL4 As String
mySQL4 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138126 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL4)



Case 5
DoCmd.RunSQL (mySQL)
Dim mySQL5 As String
mySQL5 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138127 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL5)



Case 6
DoCmd.RunSQL (mySQL)
Dim mySQL6 As String
mySQL6 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138128 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL6)



Case 7
DoCmd.RunSQL (mySQL)
Dim mySQL7 As String
mySQL7 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 138129 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL7)



Case 8
DoCmd.RunSQL (mySQL)
Dim mySQL8 As String
mySQL8 = "UPDATE [Customer IDA] SET [Customer IDA].Code = 1382 WHERE
((([Customer IDA].Code)=1381));"
DoCmd.RunSQL (mySQL8)



End Select



Next



DoCmd.SetWarnings True

MsgBox ("Table is ready")

End Sub
Click to expand...
Click to expand...
 
The & _ is a line continuation in VBA. So it allows one to use multiple lines
to display one line of code.

When you are building a text string and using line continuations you must
close off the text string before the line continuation and then restart the
text string after the line continuation.

Data type mismatch in criteria, probably means that CODE is not a number
field, but is a text field that contains numbers. If that is the case then
you need to modify your SQL string, to include text delimiters around the
value you have input. Another possibility is that I forgot to include a space
before the word SELECT in building the SQL string.

mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI )" & _
" SELECT " & ICode & ", [1381 data].Range" & _
", [1381 data].[EBC Tier], [1381 data].NNI " & _
" FROM [1381 data] WHERE ((([1381 data].Code)='1381'));"

If Code is a number field, then try the above minus the apostrophes around the
1381.


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
John,

I've tried your code but retrieved the following error: datatype mismatch in
criteria expression

What i try to do is this.
I want to loop the following:
Append query tbl_1381 data =named "1381 data" to tbl_Customer IDA =named
"Customer IDA"
Then I want to update the values in column "Code" where value ="1381" to
case1 number, case2 number and so on.

Where are the & signs for? To concatenate?
Why are there "" signs around a part of the SQL sintax?

A lot of why's and how's but I've got to understand this!

Cheers!


John Spencer said:
The SQL string looks correct. Try

DoCmd.RunSQL MySQL 'No Parentheses

I might rewrite the entire sub as

Option Compare Database
Option Explicit


Sub UpdateCustomerCode()
Dim I As Integer
Dim mySQL As String
Dim iCode as Long

mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI )" & _
"SELECT " & ICode & ", [1381 data].Range" & _
", [1381 data].[EBC Tier], [1381 data].NNI " & _
" FROM [1381 data] WHERE ((([1381 data].Code)=1381));"

'Comment out next line until code runs without error
'DoCmd.SetWarnings False

For I = 1 To 8
SELECT Case I
Case 1
iCode = 138120
Case 2
iCode = 138124
Case 3
iCode = 138125
....
End Select

DoCmd.RunSQL mySQL

Next

DoCmd.SetWarnings True

MsgBox ("Table is ready")

End Sub


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Thanks, that solved the issue... it was a text field indeed. The script is
running nice and smoothly which saves me some extra time every time!

Thanks a lot!

John Spencer said:
Check and make sure that [1381 data].Code is a number field and not a text field.

If this is a text field then you need to add apostrophes around the 1381.

mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI ) SELECT
[1381 data].Code, [1381 data].Range, [1381 data].[EBC Tier], [1381 data].NNI
FROM [1381 data] WHERE ((([1381 data].Code)='1381'));"

OR use quote marks (have to be doubled up to get one quote inside a string)

mySQL = "INSERT INTO [Customer IDA] ( Code, Range, [EBC Tier], NNI ) SELECT
[1381 data].Code, [1381 data].Range, [1381 data].[EBC Tier], [1381 data].NNI
FROM [1381 data] WHERE ((([1381 data].Code)=""1381""));"


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Hi,

I left the brackets out, but still same error: "run-time error 3464: data
type mismatch in criteria expression".

Who have got some ideas?

Thanks
Click to expand...
Click to expand...
 
Wouter said:
DoCmd.RunSQL (mySQL)
Click to expand...

I prefer, if DAO, to use Currentdb.Execute strSQL,dbfailonerror
command instead of docmd.runsql. For ADO use
CurrentProject.Connection.Execute strCommand, lngRecordsAffected,
adCmdText

If you're going to use docmd.setwarnings make very sure you put the
True statement in any error handling code as well. Otherwise weird
things may happen later on especially while you are working on the
app. For example you will no longer get the "Do you wish to save your
changes" message if you close an object. This may mean that unwanted
changes, deletions or additions will be saved to your MDB.

Also performance can be significantly different between the two
methods. One posting stated currentdb.execute took two seconds while
docmd.runsql took eight seconds. As always YMMV.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
 
Back
Top