Re: Update a record in an SQL table

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

Douglas:

Thank you, that worked. Now that I know the next batch number, I need to ADD
a batch row to the Batch table. For example, I got this SQL string by
creating an Access Append query (if otherwise OK, do I need the aliases?):

INSERT INTO Batch ( BatchNumber, status )
SELECT "29" AS BatchNum, "O" AS Status;

I will assemble this string each time and run it the same as your example
for the UPDATE, but use this INSERT SQL string. Will that be OK?


"Douglas J. Steele" wrote:

There's no need for the recordset at all.


Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim sqltext As String, strConnect As String

Set mydb = CurrentDb

Set myq = mydb.CreateQueryDef("")
sqltext = "UPDATE batchbmp SET szValue_AN = " & BatchNumber & ";"
strConnect = "ODBC;DSN= PPM_700;;" & _
"Network=DBMSSOCN;Trusted_Connection=Yes"

With myq
.Connect = strConnect
.ReturnsRecords = False
.SQL = sqltext
.Execute dbFailOnError
End With


UpdateBatchNumber_Exit:
myq.Close
Set myrs = Nothing: Set myq = Nothing: Set mydb = Nothing
sqltext = vbNullString: strConnect = vbNullString
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 3151
MsgBox "Information not found due to ODBC connection failure." &
vbCrLf & _
"Open Maintenance, Lookup and Correct DSN",
_
vbInformation, Err.Number & " - " & Error$
Case Else
MsgBox Err.Number & " - " & Err.Description, vbInformation,
"Error in GetBatchNumber"
End Select
Resume UpdateBatchNumber_Exit

End Sub

I'm not sure about your Connect string, though: the two consecutive
semi-colons doesn't seem right. I would also have expected that your DSN
would already contain the information about Network and Trusted Connection,
so that all you'd need would be:

strConnect = "ODBC;DSN= PPM_700"

You might also consider going DSN-less, to simplify distributing the
application to multiple workstations. In that case, your connect string
would be something like:

strConnect = "ODBC;Driver={SQL Server};Server=MyServerName;" & _
"Database=MyDatabaseName;Trusted_Connection=yes"

or

strConnect = "ODBC;Driver={SQL Server};Network=DBMSSOCN;" & _
"Server=MyServerName;Database=MyDatabaseName;" & _
"Trusted_Connection=yes"

(replace MyServerName and MyDatabaseName with the correct names)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"richardb" <richardb@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A773A884-AAA0-4083-B4FE-B5EAC1E924E2@xxxxxxxxxxxxxxxx
I am trying to update a value in an SQL table, but can't seem to get all
the
pieces correct. Would appreciate advice. Here is the listing:

Public Sub UpdateBatchBMP(BatchNumber As Long)
On Error GoTo ErrorHandler

Dim mydb As DAO.Database
Dim myq As DAO.QueryDef
Dim myrs As DAO.Recordset
Dim sqltext As String, strConnect As String

Set mydb = CurrentDb

Set myq = mydb.CreateQueryDef("")
sqltext = "UPDATE batchbmp SET szValue_AN = " & BatchNumber & ";"
strConnect = "ODBC;DSN= PPM_700;;" & _
"Network=DBMSSOCN;Trusted_Connection=Yes"

With myq
.Connect = strConnect
.ReturnsRecords = False
.SQL = sqltext
Set myrs = .OpenRecordset (??? here I'm not sure)
Set myrs = .Updatable (??? or here)
End With

With myrs
' ... code to work with recordset ...
'.MoveFirst
!szValue_AN = Str$(BatchNumber)
.Update
.Close
End With

UpdateBatchNumber_Exit:
myq.Close
Set myrs = Nothing: Set myq = Nothing: Set mydb = Nothing
sqltext = vbNullString: strConnect = vbNullString
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 3151
MsgBox "Information not found due to ODBC connection failure."
&
vbCrLf & _
"Open Maintenance, Lookup and Correct DSN",
_
vbInformation, Err.Number & " - " & Error$
Case Else
MsgBox Err.Number & " - " & Err.Description, vbInformation,
"Error in GetBatchNumber"
End Select
Resume UpdateBatchNumber_Exit

End Sub

Thank you...




.

Relevant Pages

  • Re: Update a record in an SQL table
    ... Dim mydb As DAO.Database ... Dim sqltext As String, strConnect As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: ADO .addnew Access 2K problems
    ... When you tell ADO that you're going to use batch updating, ... >>> Sub CloseForm_Click ... Dim rstRecordSet As ADODB.Recordset ... Dim strIndex As String ...
    (microsoft.public.access.formscoding)
  • Re: multitasking movefile and running queries?
    ... So would a simple batch file spawned by the Shell command. ... Each data set that I generate, ... Sub transfer(str_file As String, str_dest_location As String) ... Dim fs As Variant ...
    (comp.databases.ms-access)
  • Re: multitasking movefile and running queries?
    ... So would a simple batch file spawned by the Shell command. ... Each data set that I generate, ... Sub transfer(str_file As String, str_dest_location As String) ... Dim fs As Variant ...
    (comp.databases.ms-access)
  • Re: Update a record in an SQL table
    ... Doug Steele, Microsoft Access MVP ... Now that I know the next batch number, ... I will assemble this string each time and run it the same as your example ... Dim mydb As DAO.Database ...
    (microsoft.public.access.modulesdaovba)