Re: URGENT !!! Operation must use an updateable quary

"Bob Barrows [MVP]" wrote:

joshua siew wrote:
I got a return error runing a VB6, MDAC 2.5 compile and run on a
WinXP200(SP3) machine.....any way to check if that machine is using
the correct MDAC because I'm facing an error as the above.
My syntax is ADODB.Recordset .... bla bla bla.....
My machine is running fine without any error...sigh

You failed to tell us what database you are using so I will assume Jet. This
article was written with vbscript/asp in mind, but what it says applies to
other programming languages:
http://www.aspfaq.com/show.asp?id=2062

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Dear Bob,
Sorry about that. It's access mdb2000 with no security at my user
workstation. I'm using Vb6 tools with the below statement to insert the
records to the table of a mdb.

declare,
Public SQLDBDisposal As SQLDatabase ' Disposal Asset Database

connection,
Set SQLDBDisposal = New SQLDatabase
SQLDBDisposal.SetAccessDatabaseWithPassword app.path &
"\DisposalAssetsDB.mdb", "XXXX"

start from,
**lets assume all class is declare with no errors
Dim X As New XArrayDB ' To store the Asset IDs

X will be initiate from a

If AssetMgr.DisposeAssets(X, txtDisposalRemarks.Text) Then
txtDisposalRemarks.Text = ""
MsgBox "Information is updated successfully.", vbInformation, App.Title
Else
MsgBox "Failure to update the information.", vbCritical, App.Title
End If

in the class,

' Dispose assets in batch
Public Function DisposeAssets(XAssetID As XArrayDB, DisposalRemarks As
String) As Boolean
Dim rst As ADODB.Recordset
Dim DeprMethod As DepreciationMethod
Dim BalDeprAmt As Double ' Balance Depreciation Amount
Dim AccDeprAmt As Double ' Accumulated Depreciation Amount
Dim strDateAcquired As String
Dim strDisposalRemarks As String
Dim sqlAsset As String
Dim i As Integer

DisposeAssets = False

DeprMethod = GetDepreciationMethod()

sqlAsset = ""
For i = 0 To XAssetID.Count(1) - 1
sqlAsset = sqlAsset & "'" & SQLDB.DataSQL(XAssetID(i, 0)) & "',"
Next
sqlAsset = Left(sqlAsset, Len(sqlAsset) - 1)

' Get the Asset records to be processed
strSQL = "SELECT
AssetID,AssetDescription,Brand,Model,SerialNo,CategoryID,"
strSQL = strSQL &
"StatusID,DepartmentID,LocationID,DateAcquired,DateSold,"
strSQL = strSQL & "PriceCurrency,PurchasedPrice,Remarks,"
strSQL = strSQL & "UserDef1,UserDef2,UserDef3,UserDef4,UserDef5 "
strSQL = strSQL & "FROM AssetMaster WHERE "
strSQL = strSQL & "AssetID IN (" & sqlAsset & ")"
Set rst = SQLDB.SQLRecordSet(strSQL)
If Not rst.EOF Then
' Disposal Asset - Open Connection Persist
SQLDBDisposal.OpenConnectionPersist
SQLDB.OpenConnectionPersist

rst.MoveFirst
While Not rst.EOF
strDateAcquired = CNStr(rst(9).value)

' Calculate Depreciation amount
BalDeprAmt = 0#
AccDeprAmt = 0#
...
...
...

' Add to Disposal Database
AddDisposalItem g_User.UserID, CNStr(rst(0).value),
CNStr(rst(1).value), CNStr(rst(2).value), _
CNStr(rst(3).value), CNStr(rst(4).value),
CNStr(rst(5).value), CNStr(rst(6).value), _
CNStr(rst(7).value), CNStr(rst(8).value), strDateAcquired,
CNStr(rst(10).value), _
CNStr(rst(11).value), CNStr(rst(12).value), _
CStr(AccDeprAmt), CStr(BalDeprAmt), CNStr(rst(13).value), _
CNStr(rst(14).value), CNStr(rst(15).value),
CNStr(rst(16).value), CNStr(rst(17).value), _
CNStr(rst(18).value), DisposalRemarks

AddAuditTrailWithPersist "X", g_User.UserID, CNStr(rst(0).value)
rst.MoveNext
Wend

' Disposal Asset - Close Connection Persist
SQLDBDisposal.CloseConnectionPersist
SQLDB.CloseConnectionPersist
End If
rst.Close
Set rst = Nothing

' Delete from Asset database (Batch)
strSQL = "DELETE FROM AssetMaster WHERE "
strSQL = strSQL & "AssetID IN (" & sqlAsset & ")"
DisposeAssets = SQLDB.ExecuteSQL(strSQL)

End Function

' Add Disposal one Item to Disposal Database
Private Function AddDisposalItem(UserID As String, AssetID As String,
AssetDescription As String, Brand As String, Model As String, SerialNo As
String, CategoryID As String, _
StatusID As String, DepartmentID As String, LocationID As String,
DateAcquired As String, DateSold As String, PriceCurrency As String, _
PurchasedPrice As String, AccDeprAmt As String, BalDeprAmt As
String, Remarks As String, _
UserDef1 As String, UserDef2 As String, UserDef3 As String, UserDef4
As String, UserDef5 As String, DisposalRemarks As String)

strSQL = "INSERT INTO
DisposedMaster(UserID,AssetID,AssetDescription,Brand,Model,SerialNo,CategoryID,StatusID,"
strSQL = strSQL &
"DepartmentID,LocationID,DateAcquired,DateSold,PriceCurrency,"
strSQL = strSQL & "PurchasedPrice,AccDeprAmt,BalDeprAmt,Remarks,"
strSQL = strSQL &
"UserDef1,UserDef2,UserDef3,UserDef4,UserDef5,DisposalRemarks) VALUES("
...
...
...
strSQL = strSQL & "'" & SQLDB.DataSQL(DisposalRemarks) & "')"

AddDisposalItem = SQLDBDisposal.ExecuteSQL(strSQL)
End Function

Public Function ExecuteSQL(SQLCommand As String, Optional ConnectionString
As String) As Boolean
Dim sqlConn As New ADODB.Connection

On Error GoTo Execute_Err
ExecuteSQL = False
If IsMissing(ConnectionString) Or ConnectionString = "" Then
sqlConn.Open strConnect
Else
sqlConn.Open ConnectionString
End If

sqlConn.Execute SQLCommand
sqlConn.Close
Set sqlConn = Nothing

ExecuteSQL = True
Exit Function
Execute_Err:
MsgBox Err.Number & " " & Err.Source & Chr$(13) & Err.Description,
vbCritical, App.Title
End Function

Public Function OpenConnectionPersist() As Boolean
On Error GoTo Open_Err
OpenConnectionPersist = False
Set objConn = New ADODB.Connection
objConn.Open strConnect
OpenConnectionPersist = True

Exit Function
Open_Err:
' Do Nothing
End Function

Public Sub SetAccessDatabaseWithPassword(AppDBName As String, DBPassword As
String)
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;"
strConnect = strConnect & "Data Source=" & AppDBName & ";"
strConnect = strConnect & "Persist Security Info=True;"
strConnect = strConnect & "Jet OLEDB:Database Password=" & DBPassword

DBPwd = DBPassword
End Sub

There you go....I think that's all the code that's require for you to
anaylze....
let me know if i miss out any important code for you to check....

thanks in advanced !

Regards,
Joshua Siew

.

Relevant Pages

  • Re: VBScriptADO Max Length of String
    ... Dim objFSO, objFile, strServers, arrServers, strServer ... Dim strTableName, strSql, objConn, strSvrName, strDBName ... whole file is one string. ...
    (microsoft.public.windows.server.scripting)
  • Re: SQL Fustrations
    ... Dim strLogEvent As String ... Dim strSQL As String ...
    (microsoft.public.vb.general.discussion)
  • RE: Creating a query using multiselect list box and text boxes
    ... Dim strSQL As String ... Dim strWhere As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Importing text, etc. on remote .mdb file
    ... You can then use VBA string functions to get from there to the ... Is there an easy way to schedule the imports of the text files so I ... 'Modify strSQL and filenames as required ... Dim oJet 'DAO.DBEngine ...
    (microsoft.public.access.externaldata)
  • Re: Delete button and refresh
    ... which is great except that it filters the form to only include the records ... Dim strSQL As String ...
    (microsoft.public.access.formscoding)