Binding Continous Form to ADO recordset

[roh_8_it_3]

Hi All,

I have an adp project connected to sql server 2000.I need to bind a continous form to a dynamic sql query.For this I am trying to bind the form to a ADO recordset like me.recordset=rs.

Here is the code-
1).

Dim db As ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String

Sql=”select * from tablename”

Set db = rtnConnection
Set rs = db.Execute(sql)
Set Me.Recordset = rs

In rtnconnection,I m just opening a db connection with the connection string as

Public Const strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=user;Pwd=pwd;Initial Catalog=mydb;Data Source=server"

It fails on Set Me.Recordset = rs,saying that-the object that you have entered is not a valid recordset property.

2.if I use Set db = currentproject.connection then it works fine.The connection string it uses is-

"Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=server;User ID=user;Initial Catalog=mydb;Data Provider=SQLOLEDB.1"

Also if I change my global connection string to

'Public Const strConn = "Provider=Microsoft.Access.oledb.10.0;Data Provider=SQLOLEDB.1;Persist Security Info=False;User ID=user;Pwd=pwd;Initial Catalog=mydb;Data Source=server"

Then it works fine.

I did a google search and the following MS KB article says that this happens when you use a disconnected recordset to bind a form.

http://support.microsoft.com/kb/230737

but I don’t think I am using a disconnected recordset in snippet 1.

Any ideas/suggestions regarding this.Thanks

 

[boblarson]

Why are you doing all of this? You have your SQL string:

Sql=”select * from tablename”

Just set the form's recordset to it - no need for an ADO recordset.

 

[roh_8_it_3]

I have a backkground of working in asp.net and feel comfortable in writing the unbound code.

Yes,I have checked-me.recoordset=sql works.

But any idea why the first code snippet isnt working?

Thanks!

 

[boblarson]

I have a backkground of working in asp.net and feel comfortable in writing the unbound code.

Yes,I have checked-me.recoordset=sql works.

But any idea why the first code snippet isnt working?

1. Because you don't need to connect an ADO recordset to an object that is using DAO by default. The form's recordset is using DAO and adding ADO to that layer is redundant and just not right.

2. The connection string, if using the current database should ONLY be:

CurrentProject.Connection

nothing else. You don't need to connect to the current database as if you were connecting to an outside source.

 

[roh_8_it_3]

Thanks for your input.

1.As per you the forms recordset can only be bind to DAO recordset?

I checked this kb atricle from MS.

http://support.microsoft.com/kb/281998/

MORE INFORMATION
To bind a Microsoft Access form to a recordset, you must set the Recordset property of the form to a valid Data Access Objects (DAO) or ADO Recordset object.

So I think i can bind a form to a ADO recordset.

 

[boblarson]

But in this case it doesn't make sense as you are defining the ADO recordset in the same place as your form's recordset which means you can bypass it completely as it is a redundant step.

If you have an existing ADO recordset that is set somewhere else, THEN it may make sense to use it, but it doesn't make sense to open an ADO recordset with the SQL statement that could be used by the form directly.

 

[roh_8_it_3]

Ok..may be I dont need a recordset here.But i didnt understand what difference these connection strings are making to the recordset when I am trying it to bind to the form.

1.Public Const strConn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=user;Pwd=pwd;Initial Catalog=mydb;Data Source=server"

2."Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data Source=server;User ID=user;Initial Catalog=mydb;Data Provider=SQLOLEDB.1"

1st gives me an error and 2nd works .The second gets generated when you use currentproject.connection or currentproject.accessconnection.

I think ,ms access started the currentproject.connection from the access 2000 version.I am supporting an adp project which i think was created before that then they created the connection to sql server using the first string.
well ,the project the executing the sql and generating the recordsets using the First and I dont have problems in binding the single form controls to the recordset items or accessing the recordset.
Its just for the first time ,I tried to bind the continous form with the ado recordset and it failed.

The connection is made in a global module and i can easily change it to conn=currentproject.connection in place of conn=adodbconnobject.open strconn.I hope they didnt make local connection on the forms ..have to search the code.

I think the link which i pasted before answers this.

Requirements for Microsoft SQL Server
There are two main requirements for supporting updateability when you bind a form to an ADO recordset that is using Microsoft SQL Server data:
• The ADO recordset's connection must use the Microsoft Access 10.0 OLEDB provider as its service provider.
• The ADO recordset's connection must use the Microsoft SQL Server OLEDB provider as its data provider.
NOTE: The Microsoft Access 10.0 OLEDB provider is an OLEDB service provider that was written specifically for use in Microsoft Access. It was not designed to be used in applications other than Microsoft Access, and it is not supported in other applications.

When you create ADO recordsets within Microsoft Access, you have a choice as to which ADO connection will be used by the recordset. Your ADO code can share the ADO connection that Microsoft Access is using for the SQL Server database currently open in an Access project (ADP) file; or you can programmatically create a new ADO connection to a different SQL Server database.

Thanks for you help!

 

[boblarson]

If you are using linked tables then you do not need an explicit connection string since the connection is already made. If you do not have linked tables then you would need a connection string. The CurrentProject.Connection will reference the current database since you do not need an outside connection.

What occurs, if you try to use an outside connection, while connected to the datasource, is that you will run into a limited number of connections allowed and will typically get a Too Many Connections error, or an error which lets you know little about the root cause.

So, the rule of thumb is this -

If you are referring to the current database in any way, use CurrentProject.Connection and if you need to refer to an outside database (that is not linked via linked tables), use the normal ADO connection string for that datasource type.

 

[roh_8_it_3]

Yes,its make sense to use the connection that access already opened from File-->Connection.
But as i was explaining ,I inherited this from someone else and they had made explicit connection via the VBA code.There is global module that open the connection like db.open stconn.

On the forms ,there are nuemrous db transactions like..
dim db as adodb.connection
db=rtnconnection
processing
db.close
set db =nothing

I can change the global module to open the connection using currentproject.connection.But as you see the code is explicitly closing the connection.
Will that cause any issues ?

Also ,I have a another question.I executed the sp_who2 sp in sql server and it shows me the host name of my pc.
Is there a wway for me the configure the adp client so that it shows the pc name of the users running the client.

Thanks for you help.