May 11, 2004 at 3:53 am
I am hoping to establish once and for all, the most efficient way to deliver MS Access based reports to our 20-30 users. I am not talking about ADP projects or delivery via the web at this stage, just old fashioned ODBC linked tables in MDB database files. I am sure other people must have had very similar issues to this.
In the current setup we have a series of Access databases, written over several years by different members of staff that reside on a Novell file share. We do not have Microsoft network/domains, only Windows client machines. All these client machines have MS Access installed locally.
All these file share MDB databases have ODBC linked tables to a single large SQL Server 2000 database and the tables are linked through a single file based DSN also residing on the Novell server. Originally, although the databases where always on the file share, we had local machine based DSN's on each client pc, but this seemed to cause no end of problems with some users linking new tables which could not then be seen by others/broken links etc. so we changed to a single file based DSN for ease of management.
However, the performance of these databases is pretty abysmal and has only recently improved as I have begun to rewrite some of the Access queries behind reports as server side views and then linked them back into the databases with ODBC
The question is, bearing in mind that for the present we need to stick with OBDC/Access queries due to current skill set, is it more efficient to:
1. Leave things as they are - the complete access applications inc. forms, queries, reports, linked tables on the file share with a single file DSN
2. Just have one MDB backend on the file share containing all the linked tables and have copies of the front ends on each client pc - not sure where the DSN should be
3. Copy each complete access application containing the forms, linked tables etc. to each client machine and go back to the problems of linking in the dozens of tables to each users pc with machine based DSN
4. Some other combination of the above
My problem is not fully understanding how/where the processing occurs within the different scenarios - which will create the most bottlenecks, network traffic and how much work is being done by Jet or SQL Server in each case?.
Any help would be greatly appreciated.
May 12, 2004 at 8:12 am
Hi,
We have some applications use MS Access 2000 front end and SQL Server 2000 back end. All the tables and views are linked using DSN-less ODBC connections. We have the Access front end in a server and users have short cut which points to the application. All the report logic are in SQL Server stored procedures and output is moved to table. So far we don't have issues. Ther are about 10-15 users connect at one time the most. I don't whether this info is useful. I like to know as well whether this a good method.
May 12, 2004 at 8:40 am
We use numerous Access programs which have an Access front end with the data stored in SQL Server. Each different program has a master version, which is stored on the server, and a local version which is copied down to each person's computer. All connections are dsn-less. Each user has a shortcut which uses an exe to check to see if they have the most recent version of the program. This is tracked by having a local table in the Access database which stores the last dated it was updated. The exe program compares these two dates and if they are different it will replace their local Access program with a copy of the master. If any changes need to be made I make them on the master and put this new updated master on the server. After checking the dates, and replacing if needed, the exe program will then open the Access front end.
This has worked great for us. I am replacing some tables with views and making more use of stored procedures. The great part about this has been it is very easy for me to make changes to the program. The exe program has been written so I can use the same exe for all of the front ends I use (all master programs are stored in the same directory on the server and all local programs are stored in the same directory on the local computer). Also with all connections DSN-less I can quickly relink to a test database and then link back to the production database when I am ready to distribute.
Don't know if this is the best way, but it works for us.
May 13, 2004 at 9:36 am
Just reading this thread, and two of you have referred to DSN-less connections. I've never seen this terminology before. Can you explain more about what this is and how it works?
Thanks - Warren
May 13, 2004 at 9:56 am
A dsn-less connection simply stores the location of the data within the database instead of setting up a DSN on the computer. To set this up I initially have to set up a DSN to do the initial link to the data but then I can run the code below to make the connection DSN-less. This means when I put the Access program on another computer they can locate the data without having a DSN set up on their computer. It is also nice as you can quickly relink to a test database and then change back to production prior to distributing a new copy.
Code below is what I use to set links. Hope this helps.
Public Function RefreshLink()
'Used to refresh connection strings of all tables
'must have a reference to DAO 3.6 Object Library
'--------Change SERVER, DATABASE & Address for each use---------
Dim tbl As DAO.TableDef
Dim tblCnx As String
tblCnx = "DRIVER=SQL Server;SERVER=<servername>;" _
& "APP=Microsoft® Access;DATABASE=<databasename>;" _
& "Network=DBMSSOCN;Address=<servername>,1433;Trusted_Connection=Yes"
For Each tbl In CurrentDb.TableDefs
Debug.Print tbl.Name & "-" & tbl.Connect
If Left(tbl.Connect, 4) = "ODBC" Then
tbl.Connect = tblCnx
tbl.RefreshLink
End If
Next
End Function
May 13, 2004 at 11:50 am
OK, thanks. I experimented with this sort of thing--specifying connection strings in code--a long time ago, but never got it to work completely right. I'll have to revisit the subject, armed with your code sample.
May 13, 2004 at 1:16 pm
If you are going to take on a project to update your code base, I suggest using ADO or even ADO.net as opposed to DAO. Microsoft has all but abandoned DAO and ADO is far more flexible, especially over the internet or intranet. For that matter, If you are planning a move away from the linked SQL Server tables in Access, you might consider rebuilding your apps using Visual Basic, or VB.NET. There may be a learning curve, but its well worth it.
May 13, 2004 at 2:38 pm
If you are going to take on a project to update your code base, I suggest using ADO or even ADO.net as opposed to DAO.
In this case, we have a 3rd-party Access application (talking to SQL). On occasion I add rather small supplemental routines to it, so it's not a matter of updating a large code base.
If you are planning a move away from the linked SQL Server tables in Access, you might consider rebuilding your apps using Visual Basic, or VB.NET.
For this and similar apps, I don't see why we'd want to move away from linking tables to SQL. Maybe I'm missing something in your answer, but that would mean we'd just have to store the data somewhere else--and link there instead.
When appropriate, we do use VB instead of Access when we don't need all the interactive database query stuff, report writing, and that sort of thing.
(Hmmm, what happened to the quoting facility? Doesn't seem to be working in this version of the forum.)
May 13, 2004 at 3:54 pm
Yours is the standard client/server application. Each user having his or her client, that accesses data from a server, in your case SQL Server. This paridigm is notorious for the type of problems you are currently having, i.e., having to update multiple clients frequently, DSN issues, bandwidth issues, and scaling (or growth) issues.
A more effecient design is what's known as an N-Tier application. An N-Tier design has 3 or more layers.
An N-Tier application architecture seperates these layers. The Presentation Layer, or User Interface, in your case the Access Application, talks only to the Business Tier, which is commonly a .dll that encapsulates business rules and communicates with the Data Tier.
All requests made by the client go through the business tier object, which processes the request, applies the applicable business logic and requests the data from the Data Tier, i.e., SQL Server, and returns it to the client.
If changes need to be made, they are most often made in the Business Tier and should rarely require changes to be made at the client.
The design is more complex and takes more time to build than a client/server application, but can significantly produce productivity, is far more scalable, easier to maintain, and more effecient.
May 13, 2004 at 5:29 pm
OK, I see what you mean. The apps with which I'm involved (one of which is 3rd-party and therefore is impractical to rewrite even if we wanted to) are basically running on just one client workstation, and generating various reports (non-paper) on a pre-set schedule. Occasionally one or two other people will run the 3rd-party app for brief periods. If we had lots of users, the n-tier architecture would be something to look at.
Our other, more heavily-used app (also 3rd party) might be considered 3-tier since all client requests are funneled through a web server on the way to SQL.
May 14, 2004 at 2:25 am
Going back to the original question, bearing in mind that we are sticking with simple ODBC linked tables for now, no rewriting of apps at this stage, I am still not clear which of the 4 scenarios I listed would provide the best solution in our situation
May 14, 2004 at 6:50 am
Peter, we use ASP.Net and SQL Server as well, which I beleive is a N-tier because of using a browser, webserver(business logic) and database.
Now if I am using windows forms (vb.net or C#.net), how do I make this N-tier?
Rushdi
July 6, 2004 at 5:20 pm
Can anyone tell me if they know how to create a DSN-less connection that uses SQL login security rather than NT authentication. For reasons relating to the security scheme we have developed in VB, we don't want users authenticated via NT logins (b/c they would have access to tables directly).
Here is the function I created to relink a single table, but even though it's coded to used SQL login, it creates a link with NT authentication. Anyone know why this is happening or have code to do this correctly?
Public Function LinkTableDAOnoDSNSqlUser( _
strLinkName As String, _
strDBName As String, _
strTableName As String, _
strSrvrName As String) As Boolean
' link or relink single table / returns True or False based on Err value
' without a DSN and with SQL user authentication,
' but unfortunately it creates a linked table with NT user authentication instead! Why?
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db = CurrentDb
' if link exists delete it
Set tdf = db.TableDefs(strLinkName)
If Err.Number = 0 Then
'found an existing tabledef
db.TableDefs.Delete strLinkName
db.TableDefs.refresh
Else
'No existing tabledef
'ignore error and reset
Err.Clear
End If
'Create a new TableDef object
Set tdf = db.CreateTableDef(strLinkName)
'set the Connect and SourceTableName
'properties to establish the link
tdf.Connect = _
"ODBC;Driver={SQL Server};Server=;Database=;UID=;PWD="
'creates connection w/o a DSN and SQL security --
'this string below could replace the above tdf.connect string,
'but it doesn't seem to work with variables for the server and db names
' "ODBC;Driver={SQL Server};Server=" & strSrvrName _
' & ";Database=" & strDBName _
' & ";UID=;PWD="
'creates connection w/o a DSN and NT security --
tdf.SourceTableName = strTableName
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
LinkTableDAOnoDSNSqlUser = (Err = 0)
End Function
That's it. I based this function on one in the book "Microsoft Access Developer's Guide to SQL Server". Thanks in advance for any thoughts.
John
July 7, 2004 at 10:45 am
John,
Check out this site as it seems to pertain to why you are getting NT authenticated.
http://support.microsoft.com/default.aspx?scid=kb;en-us;194641
July 8, 2004 at 8:59 am
Thanks Sue. I actually have a database name, password, and user ID in my code, but I deleted it from the post. Even when I include those it's using integrated security (NT authenctication) instead of showing the SQL user and password prompt. It's very confusing.
When I checnge the code to create a connection with a DSN it works correctly, but with the code above, whether I have a User ID and password or leave it null, it defaults to NY authentication and won't give me the SQL login prompt.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply