Consulting

Results 1 to 5 of 5

Thread: Solved: vb-ado-access mdb

  1. #1
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location

    Solved: vb-ado-access mdb

    Hello dear all,
    I'm , just because I got a errormsg and I don't know why.
    pliiiiiiz help.

    environment:
    vb6 connected to access2k mdb
    via ado (msado15.dll)

    this is the pice of my code

    [vba]
    Me.Label_status.Caption = "status: del data from first point"
    Me.Refresh

    Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset

    conn.Open "DSN=MS Access adatb?zis;DBQ=" & App.Path & "\sinter.mdb;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=admin;UID=admin;"

    Set cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText

    cmd.CommandText = "SELECT * FROM [rep1] where casetteID=?"

    Set prm1 = cmd.CreateParameter("casetteID", adChar, adParamInput, Len(mainfrm2.Text_grafitID.Text), mainfrm2.Text_grafitID.Text)
    cmd.Parameters.Append prm1
    rs.Open cmd, , adOpenKeyset, adLockOptimistic
    [/vba]

    and this rs.open tell me the following err:
    runtime error 3265 (and the hungarian error string)

    thats means for me that the cmd query string have a incorrect clause.
    but if it is not true?

    I checked this again and again and all are good in the mdb

    I have a table with "rep1" name
    I have a field in the rep1 with "casetteID" name.

    why I get this err?

    many thanks to any help
    regards
    L@ja

  2. #2
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    A couple of things. Just as a best practice always be explicit in your field names (prefix with table.) Use brackets in all the references. Also remember that ADO wild cards are "%" & "_" instead of "*" & "?". Finally if you are using a wildcard you will need to use the LIKE keyword. I suspect this should work for you:
    [VBA]SELECT * FROM [rep1] where [rep1].[casetteID] LIKE '_';[/VBA]
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

  3. #3
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location
    Hello,
    10x for your reply

    I write little more info:
    process:
    1, usr type the casetteId into the txtbox.
    2, I check that this is exist or not,
    2/1 if not exist then I show a new form,
    that the usr select an exist one
    3, next step I delete this record from this table
    and here are when I get the error

    if I opened twice, why not accept third???
    1th open code:
    [vba]Dim conn As New ADODB.Connection
    Dim cmd As New ADODB.Command
    Dim rs As New ADODB.Recordset
    conn.Open glob.connstring
    rs.CursorLocation = adUseClient
    Set cmd.ActiveConnection = conn
    cmd.CommandType = adCmdText
    rs.CursorLocation = adUseClient

    cmd.CommandText = "SELECT * FROM [rep1]"
    rs.Open cmd, , adOpenStatic, adLockReadOnly
    rs.MoveFirst
    Do
    Me.Combo1.AddItem rs("casetteID") & ";(" & rs("date") & " " & rs("time") & ")"
    rs.MoveNext
    Loop While rs.EOF = False
    rs.Close[/vba]

    2nd open code:
    [vba]
    cmd.CommandText = "SELECT * FROM [rep1] where casetteID=?"
    cmd.Prepared = True

    Set prm1 = cmd.CreateParameter("casetteID", adChar, adParamInput, Len(mainfrm2.Text_grafitID.Text), mainfrm2.Text_grafitID.Text)
    cmd.Parameters.Append prm1
    rs.Open cmd, , adOpenDynamic, adLockPessimistic
    If Not rs.RecordCount = 1 Then
    rs.Close
    Me.Enabled = True
    MsgBox "not in the db!", , "Warning!"
    kaz_selector.Show 1, Me
    Exit Sub
    Else
    Me.Label_serial.Caption = rs("serial")
    Me.Label_type.Caption = rs("typeID")
    End If
    'clear conn
    rs.Close
    cmd.Parameters.Delete ("casetteID")
    [/vba]
    and this second copyed to here because use this as third, it is not work.

    the full code uploaded here
    http://laja.extra.hu/vbtest
    thanks your time
    regards
    L@ja

  4. #4
    VBAX Regular
    Joined
    Aug 2007
    Location
    Hungary Budapest
    Posts
    53
    Location
    of course I was wrong.
    sorry for it,
    I reusedthe recordset(rs) and not cleared these sort property.
    sorry again...
    L@ja

  5. #5
    Knowledge Base Approver VBAX Master Oorang's Avatar
    Joined
    Jan 2007
    Posts
    1,135
    Location
    lol Been there
    Cordially,
    Aaron



    Keep Our Board Clean!
    • Please Mark your thread "Solved" if you get an acceptable response (under thread tools).
    • Enclose your code in VBA tags then it will be formatted as per the VBIDE to improve readability.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •