SQL Update

[deekras]

i am trying to update a field in my SQL table.

this is what i have so far, but it doesn't work properly.



strSQL = "UPDATE [dbo_tbl_transactions] SET [sdt_transaction_date] = #" & updated_trans_date & "#"" &_
"WHERE [int_member_id] = '" & int_id"

dbs.execute strSQL

updated_trans_date is a date
int_id is a number

anyhelp would be appreciated.

 

[Shadez]

IC lots of problems.

1st your Quotes are all wrong

try

strSQL = "UPDATE [dbo_tbl_transactions] SET [sdt_transaction_date] = #" & updated_trans_date & "#" &_
"WHERE [int_member_id] = " & int_id

2nd, You will probably need to format your dates, (Dam americans)



ShadeZ

 

[deekras]

thanks for the quick response.
i changed the quotes.

it reads as follows:

Set dbs = CurrentDb()

Set mytable = db.openrecordset("dbo_tbl_transactions", dbOpenDynaset, dbSeeChanges)

strSQL = "UPDATE mytable " & _
"SET [sdt_transaction_date] = #" & updated_trans_date & "# " & _
"WHERE [int_transaction_id] = " & trans_id

dbs.Execute strSQL

(i added the dbSeeChanges because i had received an error message about needing it)

now the error is "object required"

 

[deekras]

made some changes, now it reads:


Set db = CurrentDb

Set rs = Recordset
Set rs = db.OpenRecordset("dbo_tbl_transactions", dbopendynaset, dbSeeChanges, dbOptimistic)

strSQL = "UPDATE rs " & _
"SET [sdt_transaction_date] = #" & updated_trans_date & "# " & _
"WHERE [int_transaction_id] = ' " & trans_id & "'"

dbs.Execute strSQL


now says "invalid argument"

 

[WayneRyan]

deekras,

strSQL = "UPDATE rs " & _ 
"SET [sdt_transaction_date] = #" & updated_trans_date & "# " & _ 
"WHERE [int_transaction_id] = ' " & trans_id & "'"

Why did you add the single-quotes around trans_id, isn't it
an integer?

Wayne

 

[deekras]

yes, it is. i changed that.
still getting the error 'invalid argument'. it comes at the openrecordset line. if i take out the dbopendynaset, etc., i will get error message about needing dbSeeChanges.

the table is on the server, linked by using ODBC. the table also has an autonumber.

 

[WayneRyan]

deekras,


try this:

strSQL = "UPDATE dbo_tbl_transactions " & _ 
"SET [sdt_transaction_date] = #" & updated_trans_date & "# " & _ 
"WHERE [int_transaction_id] = " & trans_id & ";" 

dbs.Execute strSQL

Wayne

 

[deekras]

i still have the 'invalid argument' problem with the openrecordset line.

if i take out the line entirely, i get 'object required'.

if i take out the dbSeeChanges, i get ' ...need dbSeeChanges ... IDENTITY column'.

what do i do?

 

[WayneRyan]

deekras,

There are a couple of ways to do this.

The first is to forget the recordset and use the code in my
last post. The change that I made was to remove the
single-quotes and put your table name in the SQL.

If you do you use a recordset, then you need to have:
[/code]
Dim dbs As Database
Dim rst As RecordSet
Dim strSQL As String

Set dbs = CurrentDb()

strSQL = "Select * from dbo_tbl_transactions " & _
"Where [int_transaction_id] = " & Me.trans_id

Set rst = dbs.OpenRecordSet(strSQL)

If rst.EOF and rst.BOF Then
MsgBox("No record!")
Exit Sub
End If

rst.Edit
rst![sdt_transaction_date] = Me.updated_trans_date
rst.Update
[/code]

hth,
Wayne

 

[Dgar007]

i am not familiar with - [/code] - . What does that do? I am not very familiar with SQL Server tables, does this have something to do with that? I would think you couldn 't use
Set db = CurrentDB() if it's a SQL Server table. Wouldn't you have to reference the table through ODBC?

 

[WayneRyan]

Dgar007,

oops ...

Those are just for formatting text on this forum. It preserves
things like indentation and makes your code look better.
There is nothing worse than trying to read a many-level
If - Then statement with no indentation.

I made a typo, the first one should not have the slash.

Wayne

 

[Dgar007]

Thanks for getting back so quickly...I've been scouring my books looking for the what that could be. I'm kinda new to this forum and not sure how to format anything.

Thanks again,

 

[Calvin]

Which version of Access are you using? if Acc2k, you need to make sure the "Microsoft DAO" is Referenced in the project and it is listed above the "Microsoft ADO" (ActiveX Data Objects) reference.