Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 19th, 2007, 08:55 AM
Authorized User
 
Join Date: Jan 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default View an ado recordset in a form

I think a bit of background would be useful first, before discussing the coding issue.

I have an Adp connecting to a Sql 2000 Server. The purpose of the front end is to record visits to premises.

My users have laptops so therefore will not always have a connection to the Sql backend. Therefore when a visit record is inserted, it is also inserted in a table on a local mdb file held in their my Documents folder. The purpose is to view previous visits whilst offline.

My problem is that I can program and open a connection to the local mdb. I can run a select query which is assigned to a recordset object. What I can't do is assign a form's recordset property to the recordset I have created.

I get Run time error '91' Object variable or with block not set. Something I don't really understand, so I would appreciate any attempt to help me comprehend.

Below is the code I'm using when the form loads

Private Sub Form_Load()

Dim cmd As ADODB.Command
Dim rsVisit As ADODB.Recordset
Dim frm As Form_sbFrm_ViewCR
Dim objVisit As Object

Dim strcnn As String
Dim strMdbPath As String
Dim strDbName As String

' Calls Function to Find Path of MyDocumenrs

strMdbPath = SpecFolder(CSIDL_Personal)
strDbName = "\wsrsVisits.mdb"

strMdbPath = strMdbPath + strDbName

strcnn = "Provider=Microsoft.jet.oledb.4.0;"
strcnn = strcnn & "Data Source= " & strMdbPath & ";"
strcnn = strcnn & "Persist Security Info=False"

Set cmd = New ADODB.Command
cmd.ActiveConnection = strcnn
cmd.CommandType = adCmdText
cmd.CommandText = "Select VisitID,DateOfVisit,Method,Reason From tblCrVisit"


Set rsVisit = cmd.Execute

Me.Recordset = rsVisit



 
Old June 19th, 2007, 11:17 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

How about this:

Private Sub Form_Load()

Dim cmd As ADODB.Command
Dim cn As ADODB.Connection
Dim rsVisit As ADODB.Recordset
Dim frm As Form_sbFrm_ViewCR
Dim objVisit As Object

Dim strcnn As String
Dim strMdbPath As String
Dim strDbName As String
Dim sSQL As String

' Calls Function to Find Path of MyDocumenrs

strMdbPath = SpecFolder(CSIDL_Personal)
strDbName = "\wsrsVisits.mdb"

strMdbPath = strMdbPath + strDbName

strcnn = "Provider=Microsoft.jet.oledb.4.0;"
strcnn = strcnn & "Data Source= " & strMdbPath & ";"
strcnn = strcnn & "Persist Security Info=False"

Set cn = New ADODB.Connection
With cn
    .CursorLocation = adUseClient
End With
cn.Open strcnn


sSQL = "Select VisitID,DateOfVisit,Method,Reason FROM tblCrVisit"
Set rsVisit = New ADODB.Recordset
rsVisit.Open sSQL, cn, adOpenDynamic, adLockOptimistic

This will pull in the data from the access database in My Docs. Now, what do you want to do with it?

I normally just pull the data to a local table and then open the form on the local table.

Did any of that help?




mmcdonal
 
Old June 19th, 2007, 11:47 AM
Authorized User
 
Join Date: Jan 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for your help. I've assigned the rsVisit to the form's recordset.

The form controls then needed renaming to the name of the fields in the recordset, for the form to be populated.

I have one question regarding your advice, what is the purpose of cn.CursorLocation, the options being either client or server.

My intention is for this to be the initial display when you first load the form.

The form is actually a subform. The main form has combo boxes which I will then use as query parameters (e.g Date from, Date To). After the combobox has been updated I then need to requery the subform with the new parameters.

I have this working successfully on a local mdb where the form, query and table are all contained in the same database, I now need to pull the query result from the local mdb --> adp form.



 
Old June 19th, 2007, 12:00 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Basically, while a little more costly than the default adUseServer, it allows more actions with the recordset.

In your case, I would open a second local recordset, and then append all the data from the remote table to a local table. Then build the subform off the local table, or a query on the local table.

Then if you want to push this data to the SQL Server, just package all the records you want and open a local and remote recordset and push them to the server.

Using a local table is a little more kludgy. I use it to keep traffic down on the wire, but since this is all local data, then that is not an issue.

Just call the data again from the My Docs database with your added parameters.

Did that help?


mmcdonal
 
Old June 20th, 2007, 05:01 AM
Authorized User
 
Join Date: Jan 2007
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks for getting back to me,

Where I'm up to now....I created a query event on the subform which would run the select query with the search criteria. However when I update the combo box I could not get it run the event. form_sbfrmCR_Visit.query???

What would be the best way to achieve this? To be honest you are on different level to me, and you've left me a little confused about how to proceed.


 
Old June 20th, 2007, 06:19 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Put the code in a module, and then call the module from the sub form event and from the main form event you are using.

Depending on the amount of data, I would transfer this to a local table and run from there just to keep the transactions off the wire... I keep forgetting this is a local database.



mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Dynamically pass ADO recordset from Form to Report tcarnahan Access 1 June 19th, 2008 11:31 AM
Clone DAO Recordset into ADO Recordset kamrans74 VB How-To 0 March 6th, 2007 11:57 AM
ADO recordset to table kanoorani VB Databases Basics 3 May 16th, 2006 02:59 AM
ADO Recordset Scootterp Access VBA 4 February 27th, 2006 06:44 PM
Convert ADO recordset to DAO recordset andrew_taft Access 1 May 5th, 2004 02:31 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.