Disconnected Record-set

[Khalid_Afridi]

Hi Friendz!

I have back-end database on Local Network, around 50 users are directly connected to the back end database from their front end applications.

Sometime the server is too busy to pull the data and the application processing becomes very much slow and data get corrupt.

I decided to convert my application to the disconnected record-set connection, so the server will have less load of traffic in processing data from the back-end.

The problem is:
I have too many complex queries on front-end database, which perform lot of functionality on the front end, so if I use disconnected recordset I have to write all these queries in code on front end to pull the data from the back end moreover there will be no visual/graphic interface to create the query easily.

Is there a way to move all my queries to back end and from there could I refer the same to my front end using disconnected record-set connection.

I know a lot work have to be done to accomplish all these.

Any idea?

Khalid

 

[Banana]

I'm not sure if I'm following because while we certainly can do a disconnected recordset, there's not much to do with queries you have on the front-end, neither would it matter if it's on front-end or back-end because there's no built-in functionality for using a saved query as a model for disconnected recordset (and unsurprisingly so because saved query are QueryDef object, which is a part of DAO object model which doesn't support the concept while to use disconnected recordset we need to use ADO object model).

But I suppose the most simplest way to do this is to do something like this:

(Assuming this is run in a form's module, hence the Me reference)

Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset

rs.Open CurrentDb.QueryDefs("NameOfQuery").SQL, CurrentProject.Connection
Set rs.ActiveConnection = Nothing
Set Me.Recordset = rs

...

The idea being that you would use SQL property of the QueryDef object to "re-use" the query you've already created for the recordset that would be then disconnected and thus assigned to the form.

Be aware that was only snippet and you may need to be sure all steps necessary to bind a recordset (disconnected or not) to form is satisfied else it will fail. For nitty-gritty details, consult the KB article: Bind ADO recordsets to Microsoft Access Forms.

Does that get you somewhere?

 

[LPurvis]

Specifying a Client side cursor before opening of course (unless the connection is already client side - which CurrentProject.Connection isn't, not in an MDB/ACCDB anyway).
i.e.
rs.CursorLocation = adUseClient

(Yes, predictably, I had to comment. )

 

[LPurvis]

Oh I agree though that the complexities of the queries plays no part in the location of the execution. The defintion of the query resides on one file or another (in an MDB/ACCDB having them in the BE makes far less sense).
The query is executed on the client - inevitably. Whether a saved query or ad hoc SQL. Executed via the Access UI or a code data API library.
That is inevitable until you move to a server RDBMS.

I wouldn't describe saved queries as being inherently DAO QueryDef objects though.
No more than they are ADO View or Procedure objects.
They're just objects. Until they're accessed by code using one library or the other.

Cheers.

 

[Khalid_Afridi]

(and unsurprisingly so because saved query are QueryDef object, which is a part of DAO object model which doesn't support the concept while to use disconnected recordset we need to use ADO object model).
.
.
.
Does that get you somewhere?

Thanks dear Banana!
your idea is somehow meaningful, I was thinking of to get the recordset from the back-end with the:

Dim cnn As ADODB.Connection

  Dim strSQL

  On Error GoTo ErrHandler

  Set CustomerDisconnectedRS = New ADODB.Recordset

  '----------

  'Example connection is to active connection.

  'Set connection here for foreign or networked data.

  '----------

  Set cnn = Application.CurrentProject.Connection

  strSQL = "SELECT * FROM Customers"

  With CustomerDisconnectedRS

    .CursorLocation = adUseClient

    .Open strSQL, cnn, adOpenStatic, adLockBatchOptimistic

    'Disconnect.

    Set cnn = Nothing

  End With

as you mention QueryDef for disconnected recordset to re-use it. this is little tricky for me.

 

[Khalid_Afridi]

consult the KB article: Bind ADO recordsets to Microsoft Access Forms.

Does that get you somewhere?

A very useful article:

This part is very close to solve my problem:

Private Sub Form_Open(Cancel As Integer)
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
         
   'Create a new ADO Connection object
   Set cn = New ADODB.Connection

   With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Data Source").Value = _
          "C:\Program Files\Microsoft Office\Office10" & _
          "\Samples\Northwind.mdb"
      .Open
   End With

   'Create an instance of the ADO Recordset class, and
   'set its properties
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = [COLOR="Red"]"SELECT * FROM Customers"[/COLOR]
      .LockType = adLockOptimistic
      .CursorType = adOpenKeyset
      .Open 
   End With
   
   'Set the form's Recordset property to the ADO recordset
   Set Me.Recordset = rs
   Set rs = Nothing
   Set cn = Nothing
End Sub

this is very much closer to solve the problem.

but instead of using the SELECT statement, could it be possible to use the pre-defined query to use as recordset?

 

[Banana]

Sure.

Just do this:

.Source = CurrentDb.QueryDefs("NameOfQuery").SQL

I suppose if we wanted to stay within ADO model, we could use ADOX Views (?) to retrieve the same thing.

 

[Banana]

I wouldn't describe saved queries as being inherently DAO QueryDef objects though.
No more than they are ADO View or Procedure objects.
They're just objects. Until they're accessed by code using one library or the other.

Cheers.

Hm, made me think twice. Always assumed that when a query was saved in Access, a new QueryDef object was made and added to QueryDefs collection. I suppose ADOX views could be used to provide similar access, though I was under the vague impression that ADOX merely peeked in the underlying object model of a given RDBMS and used DDL or whatever to provide the metadata whereas saving a query in UI would be DAO object.

Not first time I've been wrong and I don't expect it'll be my last.

 

[Khalid_Afridi]

Sure.

Just do this:

.Source = CurrentDb.QueryDefs("NameOfQuery").SQL

I suppose if we wanted to stay within ADO model, we could use ADOX Views (?) to retrieve the same thing.

Thanks a lot Banana
I am so exited if it will work. I did't try it yet..
but one question in my mind, which is, if i have no link tables in my front end database, how my queries will work in front end? should i transfer all the queries to back-end?

What is ADOX Views(?)

 

[Banana]

No, no need to move queries anywhere. The code will be running locally (e.g. on the client) and if you moved the querydef, you'd need to go to the other database to get that SQL, which is going to be even more messy.

Just to be clear, though. The SQL in the querydef has to be valid, referencing valid tables. If there's tables named in the saved queries that isn't in backend database, that will certainly blow up.

ADOX Views is basically ADO's equivalent of DAO's QueryDefs. ADO object model separates out from data manipulation (e.g. ADODB) from data definition (e.g. ADOX) whereas DAO has no such separation. Anyway, when you use ADOX, you can access Catalogs and Views... Views against a Jet database would then expose the same saved queries you would see in a DAO's QueryDef's collections (or on Access UI).

Did that help?

 

[Khalid_Afridi]

No, no need to move queries anywhere. The code will be running locally (e.g. on the client) and if you moved the querydef, you'd need to go to the other database to get that SQL, which is going to be even more messy.
Did that help?

Thanks Banana

In other words you mean, that when we open a connection to the back-end database, With

 cn.open

all the tables can be accessed even though we not open yet a recordset for any table in SELECT statement, so QueryDefs can be apply as:

.Source = CurrentDb.QueryDefs("NameOfQuery").SQL

you know what I mean?

 

[Banana]

All "cn.Open" means is "open a connection to this source". The source can be anything - a database, a text file, a server daemon, whatever. It does not requests for any tables or any other objects.

So when you then open a recordset, that would roughly translate into requesting the other end to where we're connected to for the actual data. As long the request itself is valid, we'll get what was requested.

Basically two separate process. In DAO object model, the rough equivalent would be when we did a OpenDatabase but we don't have to because usually have the database already opened and also because it's possible to treat linked tables as if it was a local table - Jet does a lot of magic behind the scene to make this happens. ADO doesn't have this concept of linked table (though it could link to a source that would support this concept but that would be redundant), which is why we're now dealing with connections then in turn recordsets.

Did that make sense?

 

[Khalid_Afridi]

very clear now Banana
I was think of if I have no table at all in my database, my query will not work at all and will get stuck for the missing tables/fields as we experience in the graphic interface of the queries when it is created.

I can now start work on my disconnected recordset. I have developed an application to create Work Orders for on going projects. This application is used by more than 50 users daily and they get connected to the be for more than 200 times daily.

Thanks dear.
I will come back to you if I got some problem.

yes this really make sense

 

[Khalid_Afridi]

All "cn.Open" means is "open a connection to this source". The source can be anything - a database,?

I am sorry but one thing I forgot to ask.

My back-end database is password protected, which most databases are. how do I save password with the connection string?

 With cn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "Microsoft.Jet.OLEDB.4.0"
      .Properties("Data Source").Value = _
          "C:\Program Files\Microsoft Office\Office10" & _
          "\Samples\Northwind.mdb"
      .Open
   End With

While linked tables save the password automatically...

 

[Banana]

I would daresay it'd be easier to use a connection string rather than setting the properties one by one.

This way, you can then use either Connectionstrings.com or Carl's Protham Connection strings site to get the right connection string.

From connectionstrings.com:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\myFolder\myAccess2007file.accdb;Jet OLEDB:Database Password=MyDbPassword;

So the connection would look like this:

Dim cn As ADODB.Connection

Set cn = New ADODB.Connection

cn.ConnectionString = "<the connection string>"
cn.Open
...

 

[Khalid_Afridi]

Thanks banana for your prompt reply.
you are really a sweet banana

I am using Access 2007 with Windows XP.

Cheers

 

[LPurvis]

>> ADOX merely peeked in the underlying object model of a given RDBMS
It's just another data access API - using the OLEDB provider (which is a pretty thorough component of Jet, exposing that which isn't otherwise exposable ;-).


>> whereas saving a query in UI would be DAO object.
Again, DAO is just a data access API. It natively wraps Jet (without the need for a provider) and DAO components even ship with it for the expression service. But none the less it isn't Jet. There isn't a querydef added as such. There aren't any querydefs at all until you instantiate a database object and examine the querydefs collection.

That's how I like to think about it - two discrete data access APIs. Each with their advantages. They read and wrap Jet but Jet gets by just fine without them.
It's hard to find an analogy, but I suppose you could say that SQL Server doesn't need the ODBC provider. It's just accessed extremely commonly through it.


>> Views against a Jet database would then expose the same saved queries you would see in a DAO's QueryDef's collections
Yes, although not entirely. Because queries to Access are a single concept (though clearly not entirely to Jet) then DAO reads them all as querydef objects.
Of course in ADOX they're divided into Views and Procedures. Procedures would include any "query" which is either performing an action or accepts parameters (therefore pushing it out of the technical definition of being a View).
So it's more of a maintenance task in ADOX as you have to know which collection you're looking into.
That's no bad thing of course, you should know what you're looking for and what the query does. But it's a consideration.


On this general subject, I don't see a lot of value in storing saved query definitions that exist purely to have their SQL read to service ad hoc statements in code connections.
Why not just maintain a local table of SQL statements?
There's no risk of them being accidentally opened and reparsed then (and without linked tables to use - the onus is still on the developer to write entirely accurate statements from scratch anyway).

Now, it does occur that if you have any asperations of an upsize in your future, then there could be real value in creating your queries in the BE file.
You can then open your connection, read the "views" or execute "procedures" to return your results and move to a server platform with very little changes.

So there is benefit to be had if you're so inclined.

As for connectionstring Vs assignable properties - I've always liked that flexibility of ADO. I try to write objects with a similar mindset.

Cheers.

 

[Khalid_Afridi]

On this general subject, I don't see a lot of value in storing saved query definitions that exist purely to have their SQL read to service ad hoc statements in code connections.
Why not just maintain a local table of SQL statements?
There's no risk of them being accidentally opened and reparsed then (and without linked tables to use - the onus is still on the developer to write entirely accurate statements from scratch anyway).

Now, it does occur that if you have any asperations of an upsize in your future, then there could be real value in creating your queries in the BE file.
You can then open your connection, read the "views" or execute "procedures" to return your results and move to a server platform with very little changes.

So there is benefit to be had if you're so inclined.

Thnaks LPurvis
can you just give me a link to read more about the ADOX (views) and example.
I will study those in detail how to use them in my application.
I will also try to up-size the database to SQL server, which could be more powerful and secure, I use sometime SQL server database in my front end, but for now I want to save my time to use the QueryDefs or pre-defined queries in my application,
I forced to use Ms-Access database because our department use it and my boss is happy with it

I am bound to use the Ms-Access database, because we have more than 7 applications which I developed using the same database which is in ACCDB format.

as Mr. Banana said, ADOX will gave same GUI in MS-Access so I want to know more about it.

Why not just maintain a local table of SQL statements?
A Local table mean to save all the data in the front application of the user. It will make bigger the front end application size bigger. is in't it?

from scratch anyway
as I said, from scratch mean to spend lot of time on re-writing all the queries in code

 

[LPurvis]

I'm not clear on what your overall goal is in this case.
The line "ADOX will gave same GUI in MS-Access" in particular makes me wonder what you're wanting.

I, personally, don't see what ADOX will bring to the party for you here.
It's another data library which accesses schema and security information.
You don't need to go reading objects schema AFAICT. It's not going to give you anything productive.
Of course, if you just want to know for the sake of curiosity and completeness then that's fine.
See what you think of something like this: http://msdn.microsoft.com/en-us/library/aa140021(office.10).aspx

If you have BE database based queries then you can refer to those as black box objects to an extent so that any syntax differences in your code aren't as profound if you do upsize.
But it's not the standard way to create Access applications.

>> Why not just maintain a local table of SQL statements?
>> A Local table mean to save all the data in the front application of the user. It will make bigger the front end application size bigger. is in't it?

No. A local table which holds the SQL statements that you want to execute.
This is instead of using queries and just reading their SQL definition (which seems fairly pointless). I'm not suggesting anything about the location of the data itself.
But SQL statements in a table reduces the amount of your code, allows you to more easy read and write the statements and keeps them in a single location.

>> from scratch anyway
>> as I said, from scratch mean to spend lot of time on re-writing all the queries in code

You can do that - but if there's logic to be had in providing some standard, reusable queries, then by all means do so. My advice would be don't code SQL strings in VBA for the sake of it. Have a reason for the plan.

Cheers.

 

[Khalid_Afridi]

Great idea!
Thanks Mr. Leigh Purvis

>>But SQL statements in a table reduces the amount of your code, allows you to more easy read and write the statements and keeps them in a single location.

You tend my attention to new techniques, its mean, save your queries in a memo field in a local table and then call/reuse it in your code... wawo...

But the idea is little tricky to refer to desire field in your code.
For example:

Dim strSQL as string
strSQL=[MemoFieldOfLocalTable]

Am I wrong???