Discussion question...MSAccess and SQL Server 1

[mike777]

I keep getting the "ODBC timed-out" error messages while running queries on a linked table, linked to a SQL Server table. I have set the time-out setting under Tools>Options>Advanced to the maximum, 300 sec, but it's not helping (it helps, but I still get the errors when I increase the size of the query return).
Is there anyone who can give me a general opinion on the effectiveness of MS Access as front-end for SQL server? Is VB better? Is there anything else that would be considered the "ultimate" front-end development tool for SQL Server?
Thanks for your help.
michael.kemp@gs.com

 

[jagilman]

Here is how one of my querys look.

On Error Resume Next
Set qry = db.QueryDefs("qryProductSummary&quot
If Err Then Set qry = db.CreateQueryDef("qryProductSummary&quot
Err.Clear
qry.Connect = ConnectionString
qry.sql = "sp_SWItemDetail" 'SQL Server stored procedure

qry.ODBCTimeout = 0 < this is what you are looking for!!

qry.Close

The value of 0 is forever!
John A. Gilman
gms@uslink.net

 

[rbowes]

I'll tell you what I think. Access 2K is a wonderful tool for front end development with SQL Server. I use it all of the time and I prefer it to VB. Your query sounds like it is getting a lot of data at a time. I would suggest that you switch to ADO recordsets, commands or connections.
In Access 2K your forms have a recordset property which which can be set to your ADO recordset object. They are faster than saved queries in Access. Also, you may want to create a stored procedure to run large data queries. The only problem is that you need to write sql code in Transact-SQL.

Bob Bowes