Solved

VERY EASY QUESTION

Posted on 2002-05-13
16
165 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
  • 6
  • 3
  • 3
  • +4
16 Comments
 
LVL 5

Expert Comment

by:rpai
Comment Utility
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
Comment Utility
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
Comment Utility
Should be ::
recordset.Open "SELECT farebase.* From farebase;", myconn, adOpenDynamic, adLockOptimistic
0
 
LVL 1

Author Comment

by:bolox
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 1

Author Comment

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

Author Comment

by:bolox
Comment Utility
i mean thick (DUH!!)
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
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
Comment Utility
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
Comment Utility
>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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now