January 2, 2014 at 1:41 pm
I have an MS Access 2007 database that I use to connect, via ODBC, to a SQL Server DB on a remote host. I notice that when I am running an update query in MS Access on a SQL table (which may take 20 minutes), the SQL DB will not respond to any queries from Management Studio.
E.g.
- MS Access update query is running on SQL DB Providers table
- I issue the query in Mgmt Studio: SELECT LastName FROM Providers WHERE ID=300
-- The above query will time out - the SQL DB does not respond. The status message is "Executing..."
- The moment the MS Access query finishes, then I am able to run the Mgmt Studio query.
Surely one query running in MS Access will not cause the entire SQL DB to be unresponsive?
I can't figure this out.
Thanks,
Matt
January 2, 2014 at 1:56 pm
I have a feeling you have huge async network io waits during this.
Unfortunately Access can be terrible for accessing data from SQL Server. Here's a thread with some suggestions.
January 2, 2014 at 2:21 pm
Per Confio - When Microsoft Access attempts to join a SQL Server table with a local Access table, Access will retrieve every row from SQL Server and perform the join locally within Access. Redesign the Access query so it can retrieve a subset of the data from SQL Server and then join locally to the Access table.
Accessing Less Number of rows might help or avoid "MS Access" in the first place to access SQL Server if at all possible:-D
January 3, 2014 at 3:42 am
If no local (MSAccess) tables are involved in the query, create a stored procedure on the server and call it from Access:
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("")
With qdf
.Connect = StrConnection
.SQL = strProcedureName & " " & strArguments
.Execute
.Close
End With
Set qdf = Nothing
Where:
- strConnection is the Connection string to the server.
e.g. "ODBC;DRIVER={SQL Server};SERVER=ServerName;DATABASE=DatabaseName;Trusted_Connection=Yes;"
- strProcedureName is the name of the procedure you want to be executed by the server.
- strArguments are the argugents expected by the stored procedure.
They can be named ("@FirstName='MyFirstName', @LastName='MyLastName'") or un-named ("'MyFirstName', 'MyLastName'"). If you use un-named arguments, they must be passed in the order that the procedure expects.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply