SQL query 1

[clayton74]

Hello All

I have this code in an excel sheet
that has 1 SQl statement in it. I need to put another SQL statement in but dont know how or if it can be done.

Required SQL:
'update wip set wstate = 'RUN' where wona = ('" & a & "')
and wopnumber =('" & b & "')and wstate ='ns'"

Thanks for any help

Dim strString As String
Dim a, b
Dim conn As New Connection
Dim rec As New Recordset
Dim ws As Worksheet
Dim sql$

today = Date

strString = txt1
'Range("A1")
a = Mid(strString, 2, InStr(strString, "%") - 2) 'get value of barcode from midstring from % and starts from 2nd letter
'Range("A1") = Left(strString, InStr(strString, "%") - 1)dead line
'Range("B1")
b = Right(strString, Len(strString) - InStr(strString, "%")) ' value of barcode from % to the end
'MsgBox (a & b)


conn.Open "provider=msdasql.1;driver=sql server;server=wrem-efacs;database=efacdb"

sql = "insert into used values ('" & a & "','" & b & "','DIGIT','1','1001','1','RUN','" & today & "',' ','2000-01-02 00:00:00',' ','1.0',' ',' ',' ','0.0','0.0',' ',0.000,0.000,' ',0.000,' ')"

rec.Open sql, conn


txt1 = Null 'clears contents of textbox

 

[SkipVought]

Clayton,

You stated "I need to put another SQL statement in..."

Do you mean that

you need an entirely new QueryTable on your sheet

or

you need to change the SQL for the existing QueryTable on the sheet?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Have you tried something like this ?
rec.Sql = "Your new sql instruction"
rec.Execute

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[clayton74]

Thanks for the replies.

I am after 2 SQL statements in the same routine

sub()
conn.Open "provider=msdasql.1;driver=sql server;server=wrem-efacs;database=efacdb"

sql = "sql statement 1"
rec.Open sql, conn

sql = "sql statement 2"
rec.Open sql, conn

endsub()

This procedure will run only 1 of the sql statements

Thanks for your help

 

[SkipVought]

What are you trying to accomplish?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[clayton74]

I want on a click of a button, to update 1 table in the database and insert a record into another table in sql with the information from a textbox.

Thanks for your time

Andy

 

[SkipVought]

Do you get an error?

Might the problem be in the second SQL? Please post.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PHV]

Despite you haven't replied to my previous post, yet another suggestion.
Have you tried this ?
sql = "sql statement 1"
rec.Open sql, conn
rec.Close
sql = "sql statement 2"
rec.Open sql, conn


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[clayton74]

Thanks very much for all your replies. The rec.close worked great. Much appreciated