Pros / Cons to using an ADP 2

[hwkranger]

Good morning,

I would appreciate some help in brain storming the pro's and con's of using and ADP with SQL Server vrs. and MDB.

Thank you very much.

The ones I've come up with are:



Randall Vollen
National City Bank Corp.

 

[graphix03]

adp advantages

1. increase performance, because SQL Server is a true RDBMS vs. Access which is not a true RDBMS, it is just a file based system using Jet engine. With SQL server, a record set is sent over the network, not the entire file like Jet.

You can still have and create the front end as Access.mdb with form and report wizards and all, but database objects(tables, queries(views and stored procedures) are maintained in sql server.

2. you can do a lot of things with sql server as far as management, backup, and security. it is much more intelligent than Access.

3. scalability, you can have as many users as you want on sql server. with mdb, if you have more than 8 users you are asking for trouble.

adp disadvantage:

--it can be expensive to buy and maintain sql server.

so, it depends on what your needs are, what you need to accomplish and what you can afford.

 

[RandyMyers]

I don't think you have address his question. I don't believe anyone would question the advantages of using SQL Server over Access Jet Engine tables, however, what are the advantages of using ADP versus MDB (using ODBC data connection) to connect to the SQL Server.

I have been forced to use ADP recently (boss won't listen to pros and cons) and find there are a few advantages, mainly in that only the dataset is returned to the application from SQL Server so it saves bandwidth. However, I have found that ADP has numerous "bugs" and things that worked fine in MDB (or MDE) that just do not work in ADP (like the post I have that nobody has responded to in about a week now).

 

[hwkranger]

Actually,

I'm using an MDB as the FE and a SQL Server as the BE. What I'm really thinking is moving everything to and ADP for the FE and SQL as the BE. Reasoning, it's easier to move from an MDB to ADP than to .Net web app.

The questioning behind all this is that I know that going to an ADP that there is no way to make user on the fly queries anymore. That means specialized reports or forms can't be build on the fly - rather the DBA has to write the new SP's or Views.

Because of my role with my company, I am able to make any neccessary changes - although the project DBA has the final say I can still make them for the most part. Once my projects become true production environments, We will have an assigned DBA from another dept that will not want to make changes. In additonal, it will cost us an arm and leg to do it.

What I like about the MDB is that I can write specialized pass through queries if later on I need something and I don't have DBO rights for making the view or sp.

THe real pro/Con that I am thinking about is the pro's and Con's of using an MDB vrs an ADP as the FRONT END ONLY.

What I don't like about the MDB is that it still uses Jet. If I'm using a Bound form, that cursor is client side - not server.

What I don't like about the ADP is that there is no pass through query for late development.

I know that most of anything that I would want later could possible be retrieved in a recordset in VBA ADOX but - I like the option of just writing something In QA - Copy and past it into a passthrough query.

I like about the ADP that I can change connections by a click of the file drop down menu - in an MDB I have a module that handles the connection string of the tables/queries.

I hope this is better clarification.



Randall Vollen
National City Bank Corp.

 

I realize that you only concern about the Front End, but the core of things you get in the front end has everything to do with the backend, so I'm not sure how you can talk about one and not to mention the other.

The point of using ADP so that you utilize a lot of benefits and flexibility from SQL Server database that Access.mdb database cannot do and still be able to utilize the front end of form and report like you would with Access.mdb. For instance, you said you canno longer do the queries on the fly, why not? If you are used to the Access.mdb interface to do queries, you can still do the same in SQL Server by go to view >> click on new >> here it would allow you to choose the table(s) you are looking for and do the joining of the tables by drag and drop, just like you would with Access.mdb, then it would automatically write a SQL statement for you below of the columns you chose. You then can either save this new view or just copy that SQL statement and run it on the QA, or save it into a stored procedure.

It's best that you write a stored procedure for queries, especially those that you know you would use again and again. The stored procedures are especially important for client-server database systems because it is stored on the server side means that it is available to all clients. And when the procedure is modified, all clients automatically get the new version. With stored procedures, once you execute them the first time, those SQL statements would be stored in the cache, so when you execute it again, it's just retrieved it from there and not have to go look further. This is why stored procedures are fast.

Once you have the stored procedure in place, you can call it from the form, the controls (combo box, list box, etc..), the reports, within the codes, you name it. And you can call these stored procedures anytime you like.

If your role is a developer, then you should have full access in creating views and stored procedures IN DEVELOPMENT database. Once you are done with them and the application works fine with what you created, then have the DBA transfer these objects for you to the LIVE SERVER or PRODUCTION Datatabase.

And every time if you want to make any changes to these views or stored procedure, again, made the changes in DEVELOPMENT database first, then once you test them works, let the DBA know to make transfer those updated objects for you.

I don't understand this statement you wrote:
"What I don't like about the ADP is that there is no pass through query for late development." what do you mean by late development?

 

[markphsd]

Blank name:

I think what hwkranger means with late development, is that often as access developers we construct SQL through code. When we do that we aren't faced with the performance issues of a object query vs. sql in code.

However, with the adp we can no longer develop sql in code and get the same performance. We would therefore have to write some method to create the sp(stored procedure) to the server. However, that is sort of an incorrect assumtion.

Now, what i think hwkranger needs to do is find a good book on writing sp's and constructing a sql server database.

The difference with sp's then access developers are used to working with is first and easiest to learn, the way to pass a parameter. The second and harder methods are both to construct "if... then" statements within the sp, and also construct nested views and sp's within other sp's. This is because we are used to using the gui development tool for queries, which is a very usefull and fast way to write sql. Unfortunately SQL server doesn't offer this gui tool for nested queries.

I hope that helps.



Mark P.

Bleh

 

[hwkranger]

Actually:

The issue is really maintenance. My role as a developer on this project is that I develop everything, but in production I don't own anything. I am not permitted to make changes.

I work for the department that the database belongs to - meaning after the project is finished, I won't be a developer, but rather I'll be an business systems analyst (again). Unfortunately, the way my company works - departments do not maintain their own sql servers, and are not allowed to continue to develop their databases.

Right now I have a unique position that I can change views and SP's, since I am the project developer. I have no problem using SPs and Views over Access. I have the skill sets requires to write effective and efficient T-SQL. The problems is - that when I do not have the rights, I have to pass scripts for any new SP or View to a DBA. That DBA has to decide whether or not what I wrote is worth putting in. That DBA also manages alot of Databases, and has relatively no knowledge about the business rules of the department, their needs, or their work load.

That is why an ADP isn't appealing to me. With a Passthrough Query I can still put the load on the server circumventing Access, but I don't have to deal with any political issues.


If I could find some great reason behind using an ADP I would. Right now - I don't see the real benefit.



Randall Vollen
National City Bank Corp.

 

[sueb859]

I was wondering how people who are using ADP are able to determine if a user of the front end has the most recent version of the program. In the mdb a local table is often used to store this information but this is not an option in and ADP.

Sue

 

[markphsd]

write and read to a txt file or something easy like that sueb859.

Mark P.

Bleh

 

[payback]

I add a property to the ADP database therefore no tables or external links are required. I have a hidden button that I use to change the "Build No" whenever I want to update it. This Build No appears on the main form therefore is visible whenever the application is started. Initially I used an obvious Date and Time display but now I use a randomised but sequential set number - anything will do thats suit the programmer.

 

[ESquared]

I know this is old, but... why can't you build on-the-fly queries using code and have them perform well? Is this because Access, instead of just passing the query along to SQL Server, performs ax-murdering on it first?

I know for a fact that one can build on-the-fly queries, but perhaps the point was about performance. I'd like to hear more about that.

 

[psimon88]

Interesting, Randall.

I like about the ADP that I can change connections by a click of the file drop down menu - in an MDB I have a module that handles the connection string of the tables/queries.

Are you able to provide this module/code? I'd be interested in it.

Thanks.

 

[hwkranger]

I don't check my posts very often:

Should you happen to read this..

ESquared,

1) You can not use PASS-THROUGH Queries on the fly was my point. You can of course use a rst at anytime with VB. Sometimes It's nice to have ad-hoc reporting that is built on the fly (using RegX or any number of string manipulations)

2) Of course Performance is going to be better with an ADP since, ADP's use the server side record handling much more efficient. But then, if that's the point -- Why not use C# or VB.Net. Why even both with Access? Aside from the "reports" part, there is no gain...

Randall Vollen
National City Bank Corp.

 

[hwkranger]

philaffinity,

Sorry about the delay. Per your request:

This is copied and pasted straight out of some test code:

Private Function tst()
    'MsgBox ("REMEMBER TO CHANGE CONNECTION DEFAULT DBFORMAT BEFORE CONVERSION!!!!")
    Call ReconnectDatabase("CentralProofOH  ", "OHCLESQL1001")
    'Call ReconnectDatabase("CentralProofTE", "(LOCAL)")
End Function

Here's the functions. note that s is my status bar, you don't need that.

Public Function ReconnectDatabase(strDbName As String, strServerName As String)
'On Error GoTo Reconnect_Err

Call fUpdatePassThroughQueryConnection(strDbName, strServerName)
Call fChangeTblConnectString(strDbName, strServerName)
MsgBox ("Finished reconnecting database tables and passthrough queries.")

Exit Function

Reconnect_Err:
MsgBox "The Connection Process was interupted.  You need to compact and repair this database before attempting to reconnect.", vbCritical

End Function

Private Function fUpdatePassThroughQueryConnection(str_DBName As String, str_Server)

Dim i As Integer
Dim s As New clsStatusBar

s.Steps = CurrentDb.QueryDefs.Count
s.MeterText = "Linking Queries"
    
    For i = 0 To CurrentDb.QueryDefs.Count - 1
    s.MoveMeterBar
        If (Len(CurrentDb.QueryDefs(i).Connect) > 1) Then
            CurrentDb.QueryDefs(i).Connect = "ODBC;driver={SQL Server};server=" & str_Server & ";" & _
          "database=" & str_DBName & ";" & "Trusted_Connection=Yes;" '"ODBC;DSN=" & DSN & ";Description=Central Proof Databases;DATABASE=" & DbName & ";Network=DBMSSOCN;Trusted_Connection=Yes"
        End If
        CurrentDb.QueryDefs(i).ODBCTimeout = 600
    Next i

s.ClearBar
End Function

Public Function fChangeTblConnectString(str_DBName As String, str_Server)
'This sample function changes the connect string of the linked SQL Server
'Events database to be DSN-less.

On Error GoTo TblCnt_Err

Dim db As DATABASE
Dim t As TableDef
Dim int_error As Integer

Dim s As New clsStatusBar

s.Steps = CurrentDb.TableDefs.Count
s.MeterText = "Linking Tables"

'added "Network Library=dbmssocn;" for this to use TCP/IP
Set db = CurrentDb
    For Each t In db.TableDefs
        s.MoveMeterBar
        If t.Attributes And dbAttachedODBC Then
          t.Connect = "driver={SQL Server};server=" & str_Server & ";" & _
          "database=" & str_DBName & ";" & "Trusted_Connection=Yes;" & "Network Library=DBMSSOCN;"
          t.RefreshLink
        End If
    Next
db.Close

s.ClearBar
If int_error > 0 Then
    MsgBox ("There were " & int_error & " errors during reconnection of tables.")
End If

Exit Function
TblCnt_Err:
    int_error = int_error + 1

End Function

Randall Vollen
National City Bank Corp.

 

[ESquared]

1) You can not use PASS-THROUGH Queries on the fly was my point. You can of course use a rst at anytime with VB. Sometimes It's nice to have ad-hoc reporting that is built on the fly (using RegX or any number of string manipulations)

I'm still missing the point. What exactly is this "ad-hoc rpoerting that is built on the fly" that can be done in MDBs but not ADPs? You can't mean the GUI part of MDBs that can create true passthrough queries because that wouldn't be programmatic using RegExps or whatever. And what other way is there to do server-side queries if you don't use VB? Have you thought about using the SQLDMO object? Maybe I'm getting hung up on the word passthrough. What do you mean by that?

2) Of course Performance is going to be better with an ADP since, ADP's use the server side record handling much more efficient. But then, if that's the point -- Why not use C# or VB.Net. Why even both with Access? Aside from the "reports" part, there is no gain...

There's a lot of gain: Development time. Reusability. Invested learning.

For my company to stop and convert everything they have in Access to something else would take a year of nothing but studying the new thingybobs and getting good with them and reinventing everything we used to be able to do in Access. (I, for one, don't know how to make a continuous subform with VB, dot Fred or otherwise). Then after a year of no progress we could actually do more things that earn us more money. I think the boss isn't going to have us do that.

 

[psimon88]

Hello

I am still wondering why certain things are so different between MDB and ADP files.

For example, see:

http://www.tek-tips.com/viewthread.cfm?qid=1122630&page=1

I thought that an instr function would suffice here (ADP file).

In the MDB, it took me two minutes to get this:

SELECT Count(Trim(Right$([strEmail],Len([strEmail])-InStr(1,[strEmail],"@")))) AS [domain], Trim(Right$([strEmail],Len([strEmail])-InStr(1,[strEmail],"@"))) AS domain2
FROM dbo_VM_Leads
GROUP BY Trim(Right$([strEmail],Len([strEmail])-InStr(1,[strEmail],"@")))
HAVING (((Count(Trim(Right$([strEmail],Len([strEmail])-InStr(1,[strEmail],"@"))))) Is Not Null) AND ((Trim(Right$([strEmail],Len([strEmail])-InStr(1,[strEmail],"@")))) Is Not Null));

In the ADP and SQL Query Analyzer, it's a pretty convoluted process. Is this just my lack of familiarity with the differences or is it fundamentally easier in an MDB?

Don't even get me started on formatting and decimal places. 100x easier in an MDB.

Thanks.

 

[ESquared]

Things are so different because the query engine is Access in one case and MS SQL Server in the other, and they are completely different products.

Just replace InStr with CharIndex, lose the dollar signs on the Right functions, convert the double quotes to single quotes, and your code should work as is in the ADP.

 

[ESquared]

Formatting and decimal places aren't so bad either. For one thing, this is a presentation issue that can be solved in the user interface.

If you demand that the server do it, it's still not that hard, even though there's no tidy Format function.