Solved

VERY EASY QUESTION

Posted on 2002-05-13
16
170 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
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

821 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