Apologies if this issue has been dealt with before but I'm under pressure with deadlines.
I'm connecting to a remote sql server via odbc to download among other things a text field which needs to be converted to a number before adding it to a local table. I am currently using a function to achieve this in the query itself i.e.
select Left(RemoveAlphas([fieldname]),9) AS Expr1
This is a mission critical system which is subject to very busy periods and I keep on reading that to optimise odbc queries you need to avoid any operation that can't be performed on the remote server.
I am very keen to minimise the amount of time spent connected to the server.
To achieve this would it be better to download the field to a temp table and then perform the conversion in an update query - or is this just what Access97 does anyway so the performance gain would be neglible? Again the goal is to minimise the time spent connected to the remote server.
Importing the whole recordset and then running the function on the local table would probably be faster as Access retrieves every record 1-by-1 from the server when you have any expressions in the sql the server can't process itself. A bulk insert into from a linked table should be considerably faster and then running an update on it locally would not suffer from the overhead odbc does when fetching single rows.