Wrox Programmer Forums
|
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 January 31st, 2007, 02:57 PM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default ODBC Timeout Problem

Here's the scenario... Not optimal, but it's what they wanted, and they pay the bills!!

SQL 2005 backend with Access 2003 front end. I have tried to ensure the DB Design is as normalized as possible. The database server is dedicated, but does host data for other apps. The access portion resides on another Server and is accessed from each users desktop. We have considered making the Access Application local to each user, but not sure if that would fix this problem.

There are probably about 20-30 users that use the database throughout the day. Most of the forms use unbound controls to display data, and then reconnect to the database to update a field if it has changed and loses focus. The idea was to make the app as independent as possible and minimize traffic to and from the database server.

The problem is that during high traffic times, users are getting ODBC time out errors when an update query is triggered on a specific table - probably the most heavily used table of all.

I was using an ADODB connection to execute the update, but changed it to a DoCmd.RunSQL This has made it run a little better, but we still have the errors.

The SQL statement is basically just

UPDATE m_summary SET summary_Summary = " Text contents here " WHERE summary_FK=3249

Any suggestions would be very appreciated.

Thanks

Mike

Mike
EchoVue.com
__________________
Mike
EchoVue.com
 
Old February 1st, 2007, 08:56 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I would move the front end to each users' desktop, and then give it a try. I am seeing lots of folks running enterprise access apps this way, but I was always taught that was a big no-no. Causes locking and access (rights) issues.

This makes it a little more difficult to update users when there is an update to the front end, but then again Access is a kludge these days anyway.

Oooh, also, I would use a provider statement and not an ODBC. I have moved away from ODBC since it is slower, even though more robust.

HTH

mmcdonal
 
Old February 2nd, 2007, 01:29 AM
Friend of Wrox
 
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Hi Mike,

Thanks! I was of at the Office 2007 launch today, but it'll be a while before the company decides to upgrade, and hopefully by then I can talk them into changing technologies...

Do you have any more info on Provider Statements. I think I may have some idea, but don't want to appear completely clueless by trying to explain what I think it is.

Thanks again,

Mike

Mike
EchoVue.com
 
Old February 2nd, 2007, 08:27 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is the syntax:

sSQL = "SELECT * FROM Employee"

'Open Connection to SQL
Set cn = New ADODB.Connection
With cn
    .ConnectionString = CurrentProject.Connection
    .CursorLocation = adUseClient
End With
cn.Open "Provider=SQLOLEDB;Data Source=SQLServerName;Initial Catalog=DatabaseName;UID=Username;PWD=password"

'Open Recordset on Employee
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic
rs.Open sSQL, cn


A modification would be this:

cn.Open "Provider=SQLOLEDB;Data Source=SQLServerName;Initial Catalog=DatabaseName;UID=" & sUserName & ";PWD=" & sPassword

I usually take the username and password in a login form that is local to the application, and then push those to a hidden form for future reference, so I would have this sort of thing at the top of the code:

Dim sUserName, sPassword As String

sUserName = [Forms]![frmHiddenLoginForm].[Username]
sPassword = [Forms]![frmHiddenLoginForm].[Password]

This is slightly better than putting the username and password in the hard code, but you have to assume that if someone is sneaking around in the code looking for credentials, they would have to have the username and password to get in in the first place.

The nice thing about provider statements is that they work without having to modify the user's machine, and presume that if you can set up an ODBC connection on the user's machine, you can use the provider statement as well since the SQL Server has to be visible in the environment in both cases.

Another option is to put your provider statement in a public variable, and then have a function to retrieve the provider statement as needed, so the code becomes even more portable.

In any event, provider statements are faster than ODBC connections, since they go right to the database, and don't go to the ODBC and have that process start, and wait for it to work.

This may not be the resolution to your problem, but something to consider in the client / server environment.

See: "Programming Microsoft Office Access 2003" by Dobson (MS Press - sorry Wrox) for a lot of in depth material on this subject.

HTH




mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
Session timeout problem zaheerabbas.sk .NET Framework 1.x 3 April 10th, 2007 02:14 AM
ASP.Net Timeout Problem busher ASP.NET 1.0 and 1.1 Professional 1 December 8th, 2005 01:17 AM
Age Old ODBC Sql Server Driver Timeout busher Classic ASP Databases 5 December 7th, 2005 12:10 PM
Timeout problem with SqlTransaction aldwinenriquez ADO.NET 6 July 18th, 2005 09:15 PM
Form not submitting - Timeout problem? danlinn General .NET 1 June 24th, 2004 08:40 PM





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