Solved

VERY EASY QUESTION

Posted on 2002-05-13
16
172 Views
Last Modified: 2010-05-02
i havent used VB is ages and have lost all of my prev proggies....

here is my code.....

Dim myconn
Set myconn = New ADODB.Connection
myconn.Open "DBQ=C:\farebase\farebase.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

Set myado = myconn.Execute("SELECT farebase.* From farebase;")
myado.AddNew
myado!airline = "GazzaAir"
     
myado.Update


when this button is clicked , it says that this recordset does not support updating??

Help what have i done wrong, i think it is to do with lockpesamistic or somehting like that but i cannot find my book

Thanx

My name is Bolox for a reason
0
Comment
Question by:bolox
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
  • +4
16 Comments
 
LVL 5

Expert Comment

by:rpai
ID: 7006288
The returned Recordset object is always a READ-ONLY, FORWARD cursor. If you need a Recordset object with more functionality, first create a Recordset object with the desired property settings, then use the Recordset object's Open method to execute the query and return the desired cursor type.

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

recordset.Open "SELECT farebase.* From farebase;", myconn, adOpenDynamic, adLockOptimistic, Options

Hope this helps.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7006291
The .Execute method of a connection object returns a read-only recordset. You should use a recordset object or use an Update statement in the execute method:

Dim myconn As ADODB.Connection
Dim myADO As ADODB.Recordset
Set myconn = New ADODB.Connection
Ser myADO = New ADODB.Recordset
myconn.Open "DBQ=C:\farebase\farebase.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

myADO.Open "SElect Farebase.* From Farebase",myconn,adOpenStatic,adLockOptimistic
myado.AddNew
myado!airline = "GazzaAir"
   
myado.Update

'Or

myConn.Execute "Insert Into Farebase (airline) Values('GazzaAir')"
0
 
LVL 5

Expert Comment

by:rpai
ID: 7006292
Should be ::
recordset.Open "SELECT farebase.* From farebase;", myconn, adOpenDynamic, adLockOptimistic
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Author Comment

by:bolox
ID: 7006473
everyone has an opinion  but i cannot get any to work.

sorry to be a pain i am really "BOLOX!"

(i can use ADODC fine by entering manually) but i want to hard code as i have a lot of complex** methods to include

(**complex in my terms means easy to you)

all i want in the end is in my database, in the root of the coding folder (C:\farebase) is for example a text box called text1.text to go into the database,

Table called: farebase and the field called aircode

Thankyou


0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7006482
What errors do you get? It could be that the table structure does not allow the insert because you have a primary key for which you are not supplying a value or that you have other required fields without default values that are also not being supplied. The methods shown work in principle but without knowing the errors you get or the structure of the table/database it is impossible to give a more precise answer at this time.
0
 
LVL 1

Author Comment

by:bolox
ID: 7006489
ahhhh, i do have a pri key.

if i use tim's method is comes up with type mismatch on the line.......

Ser myADO = New ADODB.Recordset

?

0
 
LVL 43

Expert Comment

by:TimCottee
ID: 7006501
Then declare myADO As ADODB.Recordset

If you have it declared as

Dim myADO As Recordset

Then this will cause a type mismatch as this is in fact a DAO.Recordset class which is not the same thing at all. If you are declaring objects you MUST ensure that you fully qualify them when you have both DAO and ADO libraries referenced in the project. (either implicitly by including certain controls or explicitly).
0
 
LVL 1

Author Comment

by:bolox
ID: 7006521
object or block variuable not set error now.??

refering this line

myADO.Open "SElect Farebase.* From Farebase", myconn, adOpenStatic, adLockOptimistic


just to recap, my button code is:

Private Sub Command4_Click()


Dim myconn As ADODB.Connection
Dim myADO As ADODB.Recordset
Set myconn = New ADODB.Connection
Ser myADO = New ADODB.Recordset
myconn.Open "DBQ=C:\farebase\farebase.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"

myADO.Open "SElect Farebase.* From Farebase", myconn, adOpenStatic, adLockOptimistic
myADO.AddNew
myADO!airline = "GazzaAir"
   
myADO.Update
   
End Sub

what do i put in the top::: genreal declarations section?







0
 
LVL 1

Author Comment

by:bolox
ID: 7006523
as i am messing you all around, more points are being added, sory for being soooo think
0
 
LVL 1

Author Comment

by:bolox
ID: 7006526
i mean thick (DUH!!)
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 7006604
Are you referencind ADO and DAO at same time?
If so, you need to explicitly create declarations objects with full name, for instance

'DAO
dim myrs as DAO.recordset

'ADO
dim myrs as ADOBD.recordset
0
 
LVL 18

Accepted Solution

by:
mdougan earned 100 total points
ID: 7006688
This is the code that I use to make updatable Access Recordsets.  You might have to check that you have this version of OLEDB on your system. Open RegEdit and search for Microsoft.Jet.OLEDB and see what version number comes up.  If you se 3.51 then you might have to change the provider= parameter below.

' General Declarations
' ADO Objects
Private Conn As ADODB.Connection
Private RS As ADODB.Recordset

Const ACCESS_DATABASE = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\farebase\farebase.mdb"

Private Sub Form_Load()
' Open your database connection
Set Conn = New ADODB.Connection
With Conn
    .ConnectionString = ACCESS_DATABASE
    .CursorLocation = adUseClient
    .Open
End With

' Always check to make sure the Connection is valid
If Conn.State <> adStateOpen Then
    MsgBox "Failed to Open Connection", vbExclamation, "Open Connection"
    End
End If

' Open your recordset
Set RS = New ADODB.Recordset
With RS
  .Source = "SElect Farebase.* From Farebase"
  .ActiveConnection = Conn
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
  .Open
End With

' Always check to make sure the Recordset is valid
If RS.State <> adStateOpen Then
    MsgBox "Failed to Open Recordset", vbExclamation, "Open Recordset"
    End
End If


End Sub

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next

'Close your Recordset
RS.Close
Set RS = Nothing

'Close your database connection
Conn.Close
Set Conn = Nothing

End Sub

Private Sub Command4_Click()

RS.AddNew
' I don't like using the "bang" notation as it will not be supported in the future
RS("airline").value = "GazzaAir"
RS.Update
 
End Sub



0
 
LVL 5

Expert Comment

by:rpai
ID: 7006994
>object or block variuable not set error now.??
>refering this line
>myADO.Open "SElect Farebase.* From Farebase", myconn, adOpenStatic, adLockOptimistic

>Ser myADO = New ADODB.Recordset

Shouldn't this be

SET myADO = New ADODB.Recordset

Rest of the code looks fine.
0
 
LVL 2

Expert Comment

by:ventond
ID: 7007578
if the highlighed code when the error pops up is:

adOpenStatic, adLockOptimistic

then you need to go into your project references and add

Microsoft Active Data Objects...
0
 
LVL 2

Expert Comment

by:priya_pbk
ID: 7007791
How about adding this code before opening your recordset

'try setting the cursorlocation (sometimes not setting     this gives a problem)

rs.CursorLocation=aduseClient
rs.Open "Select Farebase.* From Farebase", myconn, adOpenDynamic, adLockOptimistic

--where "rs" is your Recordset. Hope this helps!


 
0
 
LVL 1

Author Comment

by:bolox
ID: 7007888
thanx for that. spot on.

Thankyou to everyone else also, do not worry i will be in and out of here all day today. i have loads more questions for ya coming up

(easy also)
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month9 days, 21 hours left to enroll

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question