Re: DoCmd.RunSQL who could check my sql?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

Wouter wrote:
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


.

Relevant Pages

  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: Performance von SQL-Abfragen auf Exceltabellen
    ... Nun habe ich es mit SQL auf Exceldaten versucht und konnte das ganze ... Performance konstant bei rund 3 Sekunden. ... Dim iColumnCount As Long ... Dim FirstColumnforSQLexists_jn As String 'j if first column for SQL ...
    (microsoft.public.de.excel)
  • Re: SqlDataAdapter.Update()
    ... string-chewing algorithm wouldn't work well in SQL. ... looping over words in the string and parsing them off, ... >> Dim cn As New SqlConnection ... >> understood that the data adapter did something like this inside the ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: search by 1 of 3 combos
    ... I guess I should expand a bit on an example of what you can do with modifying SQL for a search combo... ... , mWhere as string ... Dim mRecordID As Long ... Private Sub cmd_search_Click ...
    (microsoft.public.access.formscoding)