ADODB .Open Problem

[cephal0n]

Hello! I have been reading articles all day and still do not get an explanation WHY is this happening. I'm using ADO in msaccess and set up a connection with ADODB, which I have successfully achieved, but now I want to use the "Movefirst " and "MoveNext" method of the recordset using an unbound text, so I wrote a simple function code in VB. Now my dilema starts here. I think I got it right at first, but when I run the database, I got nothing for an output, I mean nothing shows up in my text field when the NEXT command button is clicked. I placed my code here, so pls. anyone with a good heart to help me.
Thanks!

Public Sub recordMoveFirst()
Dim conn As ADODB.Connection
Dim sql As String
sql = "SELECT * FROM tblIW49;"
Set conn = New ADODB.Connection
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "C:\MyDocuments\dataPMC.mdb"
conn.Execute sql

'declare a recordset
Dim myTableRS As ADODB.Recordset
Set myTableRS = New ADODB.Recordset
myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic

       'go to start of record
       'If myTableRS.EOF Then
       myTableRS.MoveFirst
       Me.txtWorkorder = myTableRS!Workorder
       Me.txtActivity = myTableRS!ActivityType
       Me.txtCompliant = myTableRS!Compliant
       Me.txtComments = myTableRS!Comments
      ' End If
                
       'close the recordset
       myTableRS.Close
       Set myTableRS.ActiveConnection = Nothing

       'and the connection
       conn.Close
       Set conn = Nothing

End Sub

[sierra7]

Hi

This works fine! First time too !

I'm still mostly using DAO myself so was interested having a look at this.

I set up a dataPMC.mdb with the one table and four fields you describe. I added three records then closed it.

I created a new form in another 'test-bed' database, added a button, 3 text boxes and a checkbox (for Compliant), then pasted your code under the button.

I thought I was going to have to write a "With myTableRS" around the "Me.TxtWorkOrde r = myTableRS!Worko rder" bit, but it worked ok without.

Sorry can't help with your problem.

S7

[sierra7]

Hi again

As a Post Script; I could not understand why you needed to run some sql when setting up the Connection, then separately setting up a Recordset to open the table.

The answer is "you don't"; I commented out the "conn.Execu te sql" and it still works fine.

I was curious to open a 'query' rather than a table so I modified your 'open recordset' statement to refer to 'sql' rather than "tbliw49" and it would not work, saying 'Error in FROM clause'. I then noted that you are using a constant 'adCmdTable' which is obviously wrong when running a query, but when I looked-up to find what to change it to, my 'Help' says it should not be there anyway. This works;-

[CODE=vb]
Set myTableRS = New ADODB.Recordset
'myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimist ic
myTableRS.Open sql, conn, adOpenDynamic, adLockPessimist ic
[/CODE]
And so does your original line referencing the table, without adCmdTable.
I'm running Access 2002 on this computer so perhaps the 'adCmdTable' is a more recent addition.

I hope you can get you code working !
Regards,
S7

[NeoPa]

Please remember to provide a meaningful Title for any threads started (Please Use Appropriate Titles for New Threads!). This helps to ensure that other members, and also the general public, will have a better chance of finding answers to any similar questions.

ADMIN.

[cephal0n]

Hi again

As a Post Script; I could not understand why you needed to run some sql when setting up the Connection, then separately setting up a Recordset to open the table.

The answer is "you don't"; I commented out the "conn.Execu te sql" and it still works fine.

I was curious to open a 'query' rather than a table so I modified your 'open recordset' statement to refer to 'sql' rather than "tbliw49" and it would not work, saying 'Error in FROM clause'. I then noted that you are using a constant 'adCmdTable' which is obviously wrong when running a query, but when I looked-up to find what to change it to, my 'Help' says it should not be there anyway. This works;-

[CODE=vb]
Set myTableRS = New ADODB.Recordset
'myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimist ic
myTableRS.Open sql, conn, adOpenDynamic, adLockPessimist ic
[/CODE]
And so does your original line referencing the table, without adCmdTable.
I'm running Access 2002 on this computer so perhaps the 'adCmdTable' is a more recent addition.

I hope you can get you code working !
Regards,
S7

--------------------------------------------------

Thanks sierra7! your suggestion is definitely appreciated

[Jim Doherty]

--------------------------------------------------

Thanks sierra7! your suggestion is definitely appreciated

Hi cephalon

I don't see you using the 'movenext' command anywhere in your code you are only ever referring to movefirst so I suspect you are grappling with understanding recordset navigation generally.

The following code comprises of several sub procedures each tied to a command button as you can see that moves as needed. Each sub then calls a sub procedure to display the relevant data in your fields.

The idea here is to define your connection and recordset variable at form level to hold the necessary information for a recordset so that EACH sub procedure can then reference the recordset merely by the myTableRS variable.

The recordset is opened when the form opens and moves to the last record to grab a total recordcount and then immediately moves to the first row and displays your fielded data in the controls straight away.
I havent done anything with the recordcount but there is nothing to stop you throwing that into a variable if you wished and displaying that on the form

Just paste it all into the code behind your form as is and provided you have command buttons named to match the 'cmd' click subs then it will work for you because i created a db at my end replicating your problem. You should now be in a position once pasted (you might want to create yourself a new form for this) to compare what you have there against this and thus maybe understand then why your original was not working

There is no error handling in this in favour keeping the code lines short but obviously a file must exist in the defined path and so on... otherwise this will fail. You can obviously implement your own error handling routines here.

 
Option Explicit
Dim conn As ADODB.Connection
Dim sql As String
Dim myTableRS As ADODB.Recordset
Dim MyRecordCount As Long
Private Sub cmdFirst_Click()
	myTableRS.MoveFirst
	NavigateToRecord
End Sub
Private Sub cmdPrevious_Click()
	myTableRS.MovePrevious
	NavigateToRecord
End Sub
Private Sub cmdNext_Click()
	myTableRS.MoveNext
	NavigateToRecord
End Sub
Private Sub cmdLast_Click()
	myTableRS.MoveLast
	NavigateToRecord
End Sub
Private Sub Form_Open(Cancel As Integer)
	sql = "SELECT * FROM tblIW49;"
	Set conn = New ADODB.Connection
	conn.Provider = "Microsoft.Jet.OLEDB.4.0"
	conn.Open "C:\MyDocuments\dataPMC.mdb"
	Set myTableRS = New ADODB.Recordset
	myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
	If Not (myTableRS.BOF Or myTableRS.EOF) Then
		myTableRS.MoveLast
		MyRecordCount = myTableRS.RecordCount
		myTableRS.MoveFirst
	NavigateToRecord
	End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
	myTableRS.Close
	Set myTableRS = Nothing
	conn.Close
	Set conn = Nothing
End Sub
Private Sub NavigateToRecord()
	If myTableRS.BOF Then myTableRS.MoveFirst
	If myTableRS.EOF Then myTableRS.MoveLast
	Me.txtWorkOrder = myTableRS.Fields("WorkOrder").Value
	Me.txtActivity = myTableRS.Fields("ActivityType").Value
	Me.txtCompliant = myTableRS.Fields("Compliant").Value
	Me.txtComments = myTableRS.Fields("Comments").Value
End Sub

Regards

Jim :)

[cephal0n]

Hi cephalon

I don't see you using the 'movenext' command anywhere in your code you are only ever referring to movefirst so I suspect you are grappling with understanding recordset navigation generally.

The following code comprises of several sub procedures each tied to a command button as you can see that moves as needed. Each sub then calls a sub procedure to display the relevant data in your fields.

The idea here is to define your connection and recordset variable at form level to hold the necessary information for a recordset so that EACH sub procedure can then reference the recordset merely by the myTableRS variable.

The recordset is opened when the form opens and moves to the last record to grab a total recordcount and then immediately moves to the first row and displays your fielded data in the controls straight away.
I havent done anything with the recordcount but there is nothing to stop you throwing that into a variable if you wished and displaying that on the form

Just paste it all into the code behind your form as is and provided you have command buttons named to match the 'cmd' click subs then it will work for you because i created a db at my end replicating your problem. You should now be in a position once pasted (you might want to create yourself a new form for this) to compare what you have there against this and thus maybe understand then why your original was not working

There is no error handling in this in favour keeping the code lines short but obviously a file must exist in the defined path and so on... otherwise this will fail. You can obviously implement your own error handling routines here.

 
Option Explicit
Dim conn As ADODB.Connection
Dim sql As String
Dim myTableRS As ADODB.Recordset
Dim MyRecordCount As Long
Private Sub cmdFirst_Click()
	myTableRS.MoveFirst
	NavigateToRecord
End Sub
Private Sub cmdPrevious_Click()
	myTableRS.MovePrevious
	NavigateToRecord
End Sub
Private Sub cmdNext_Click()
	myTableRS.MoveNext
	NavigateToRecord
End Sub
Private Sub cmdLast_Click()
	myTableRS.MoveLast
	NavigateToRecord
End Sub
Private Sub Form_Open(Cancel As Integer)
	sql = "SELECT * FROM tblIW49;"
	Set conn = New ADODB.Connection
	conn.Provider = "Microsoft.Jet.OLEDB.4.0"
	conn.Open "C:\MyDocuments\dataPMC.mdb"
	Set myTableRS = New ADODB.Recordset
	myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic
	If Not (myTableRS.BOF Or myTableRS.EOF) Then
		myTableRS.MoveLast
		MyRecordCount = myTableRS.RecordCount
		myTableRS.MoveFirst
	NavigateToRecord
	End If
End Sub
Private Sub Form_Unload(Cancel As Integer)
	myTableRS.Close
	Set myTableRS = Nothing
	conn.Close
	Set conn = Nothing
End Sub
Private Sub NavigateToRecord()
	If myTableRS.BOF Then myTableRS.MoveFirst
	If myTableRS.EOF Then myTableRS.MoveLast
	Me.txtWorkOrder = myTableRS.Fields("WorkOrder").Value
	Me.txtActivity = myTableRS.Fields("ActivityType").Value
	Me.txtCompliant = myTableRS.Fields("Compliant").Value
	Me.txtComments = myTableRS.Fields("Comments").Value
End Sub

Regards

Jim :)

HI JIM!!!!
first of THANKS 100X!!!!

[cephal0n]

You're a real lifesaver, I found my self, dumbfounded and very insecure, to be true. I now know that using unbound objects are not for beginners, unless there’s a guiding hand to lead you, well once again I'm in debt to you and this forum rock!
Thanks guys!

[sierra7]

Hi Jim

The same thought struck me, "How's he going to move to next record" so built a mini system much along these lines.

A good idea to put the checks for BOF/EOF in the NavigateToRecor d sub rather than under each button though. This was an excuse to play with ADO rather than DAO.

I see that you have kept in the term 'adCmdTable'

 
myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic

This does not come up in the 'auto-help' when typing the line (in Access 2002) and as I said in my previous post if you substitute a sql string instead of the table name it causes an error. As I would most likely want to open data filtered and ordered, what do you suggest should be used instead? (Leaving it out works too!)

On a separate issue, I'm going to try and help a guy with some DAO code today (thread title Membership History). If you have time to look over it and do an ADO version that would save him having to add areference to DAO.

Cheers

S7

[NeoPa]

Hi Jim
...
On a separate issue, I'm going to try and help a guy with some DAO code today (thread title Membership History). If you have time to look over it and do an ADO version that would save him having to add areference to DAO.

Cheers

S7

Sierra, why don't you add a link in here to the other post. That way Jim can find it more easily if he's interested.

[Jim Doherty]

Hi Jim

The same thought struck me, "How's he going to move to next record" so built a mini system much along these lines.

A good idea to put the checks for BOF/EOF in the NavigateToRecor d sub rather than under each button though. This was an excuse to play with ADO rather than DAO.

I see that you have kept in the term 'adCmdTable'

 
myTableRS.Open "tblIW49", conn, adOpenDynamic, adCmdTable, adLockPessimistic

This does not come up in the 'auto-help' when typing the line (in Access 2002) and as I said in my previous post if you substitute a sql string instead of the table name it causes an error. As I would most likely want to open data filtered and ordered, what do you suggest should be used instead? (Leaving it out works too!)

On a separate issue, I'm going to try and help a guy with some DAO code today (thread title Membership History). If you have time to look over it and do an ADO version that would save him having to add areference to DAO.

Cheers

S7

A big "yes" for you on the bof/eof observation Sierra nicely spotted :))

Cephalon!!... listen to sierra on that observation please if you notice I have not placed a logical test for the row position in each click sub. It IS used in the on 'open event' and a flavour of that called when calling to the NavigateToRecor d sub but this NavigateToRecor d sub is called 'after' the move commands. It is feasible that the move command could unexpectedly fail potentially for some reason unbeknown to the code and thus generate an untrapped error.

Given the simplicity of the code (it is thrown together as a learning/comparison aide more than a mission critical reliance on error handling) it can be rectified merely by placing an 'on error resume next' statement line prior to the 'move' commands in each sub procedure which will cause the move command to be skipped over and therefore NOT generate an error .....you just don't get any response on screen to the click thats all. I'm not recommending 'on error resume next' heavily here please appreciate as it is in many ways an invitation to laziness for those so inclined (and I do include myself in that :)) ). Code should be trapped for the most part where one is able to do so etc etc. :))

In relation to the acCmdTable constant I interpreted the code as near as one could to the posted code without going off at tangents 'to much' otherwise my perception was a newbie might get confused when comparing against what he knows or has typed out at his /her end.

The recordset is simply that a recordset of the table type which when opened you can do other things with. if you want.... you can then go onto 'filtering and sorting' as sierra mentions.

I prefer however much of the time to open recordsets using an SQL statement that is restricted by a WHERE clause rather than filtering an entire recordset that is first opened and THEN filtered (think if you will for a moment of the data load on huge recordsets. Do we need to open everything then filter? or is it more appropriate to define what we need and only open those records. A 'network' would prefer to know 'which one' has less records that it has to throw around as I'm sure you can appreciate bandwidth blah blah blah and the network guys will luv ya for considering their role in life as will your application on the client side because it will be swifter)

A quick example of the code that would be used as an extension to my last post on the filtering sorting side of things might be this where I'm sure (cephalon) you can see how it might fit in

myTableRS.Filter = "columnname = 'somestring'"
' Filter records where the column name is equal to 'somestring' or
' 'other'
myTableRS.Filter = "columnname = 'somestring' Or columnname = 'a different string'
' Filter records where record id column is less than 15,000
myTableRS.Filter = "[record id] < 15000"
' Remove the filter
myTableRS.Filter = ""
' Sort the records per the column name
myTableRS.Sort = "columnname"
' Sort them descending
myTableRS.Sort = "columnname Desc" 
' Sort them by two columns
myTableRS.Sort = "columnname, another_columnname"
' Remove the sorting
myTableRS.Sort = ""

Sierra..... if I'm around today I might get to the thread you spoke about but have a quite busy schedule today, will have a look later. :)

Regards

Jim :)

[sierra7]

Sierra, why don't you add a link in here to the other post. That way Jim can find it more easily if he's interested.

NeoPa
Basically because I am not sure how !

I suppose i could look it up but I am in a bit of a rush today with lots to do before holidays start tomorrow.

While on the topic, I though threads had numbers at one time ?

Also I have noticed just this last week, that if I start a Reply then 'Previous Page' to check details of previous post and return to my 'Reply', its Blank, Gone!

I'm sure this was not the situation a week ago. I now, as a matter of course, Cut & Paste if I have to refer back. An alternative is to 'Preview Post', then it's saved to come back to.

S7

[Jim Doherty]

Hi Sierra and cephalon,

In response to this bit of your post sierra

"As I would most likely want to open data filtered and ordered, what do you suggest should be used instead?"

and which might be of interest to cephalon as an extension of his thought process in coding and learning the techniques in all of this:

Given we have an example here that is common to us all provided by cephalons datasource which I suspect you replicated your end?

Let us imagine now we added 'another' command button to his form called cmdGetRecords in addition to his navigation buttons

The following code if pasted into the event for the command button will open another instance of the same database and create a new sorted recordset based based a WHERE clause (where workorder is not null) and then immediately export that to a textfile in the same folder as the database. (I thought I'd throw the external file scripting in just for interest sake basically more than anything else to do something with the recordset)

Notice the use in this case of the MyRst.GetString method to retrieve the dataset into the variable myTableRS...... ...difference method basically nothing more than that in how one might wish to grab and assign an recordset to a variable, in that in this example I want to separate the relevant field data with a tab marker (vbtab) and delineated with a carrige return linefeed (vbcrlf)

None of this is the 'only' way to do things we all know that.. there are a miriad of things we could put in, change or amend and so on... like cursor positions, lock type etc etc. it merely gives a flavour of what one can or could do

 
Private Sub cmdGetRecords_Click()
	Dim conn As ADODB.Connection
	Dim MyRst As ADODB.Recordset
	Dim myTableRS As Variant
	Dim MyHeader As Variant
	Dim fld As ADODB.Field
	Dim fso As Object
	Dim myFile As Object
	Set conn = New ADODB.Connection
	conn.Provider = "Microsoft.Jet.OLEDB.4.0"
	conn.Open "C:\MyDocuments\dataPMC.mdb"
	Set MyRst = New ADODB.Recordset
	MyRst.Open ("SELECT * FROM tblIW49 WHERE WorkOrder Is Not Null ORDER BY WorkOrder DESC"), conn, adOpenForwardOnly, adLockReadOnly, adCmdText
	If Not (MyRst.BOF Or MyRst.EOF) Then
		'return all rows as a formatted string
		'delimited by tab and rows by control line feed char
		'but first lets grab the field headers
		For Each fld In MyRst.Fields
		MyHeader = MyHeader & fld.Name & vbTab
		Next fld
		'now lets grab the data
		myTableRS = MyRst.GetString(adClipString, , vbTab, vbCrLf)
		'view both elements in the immediate window so we can see what we have here
		Debug.Print MyHeader
		Debug.Print myTableRS
	End If
	'save to a text file
	Set fso = CreateObject("Scripting.FileSystemObject")
	Set myFile = fso.CreateTextFile(CurrentProject.Path & "\MyTextFile.txt", True)
	With myFile
	.writeline MyHeader
	.writeline myTableRS
	.Close
	End With
	'clean up
	MyRst.Close
	Set fso = Nothing
	Set fld = Nothing
	Set MyRst = Nothing
	conn.Close
	Set conn = Nothing
End Sub

Regards

Jim :)

[NeoPa]

NeoPa
Basically because I am not sure how !

1. Prepare a post.
2. Browse to the link (in another window or Tab) and copy the title.
3. Switch back to original post and paste in the title.
4. Switch back to "linked to" thread and now copy the link.
5. Switch back again to the post and select the title text.
6. Click on the "globe" button (Insert Hyperlink) and hit enter when it prompts you with the title.
7. Next it prompts you with "http://" so paste your link in over the top.
8. Hit enter again and the fully specified link is ready in your post.

...
While on the topic, I though threads had numbers at one time ?

They still do. However, there are a number of valid links to the same thread. Some link by thread number; others by post number. Even within those there are different formats.

Also I have noticed just this last week, that if I start a Reply then 'Previous Page' to check details of previous post and return to my 'Reply', its Blank, Gone!

I'm sure this was not the situation a week ago. I now, as a matter of course, Cut & Paste if I have to refer back. An alternative is to 'Preview Post', then it's saved to come back to.

S7

This might be a recent update. I noticed something myself recently along these lines and lost a lot of hard work in the process. We'll see what happens there.

[sierra7]

Hi Jim

This is the link to the other thread where I have given advice in 'DAO'
Membership History

When I first came to ADO I found it overly complicated having to set-up connection strings every time you needed to do something, so I stuck with DAO.

I have subsequently noted that with LINKED table you can use something like 'adActiveConnec tion' or the like ??

S7