Darryl Collins
Darryl.Collins at coles.com.au
Thu Feb 5 20:26:39 CST 2009
Hi Doug,
I saw you question in the Access list, but I am pretty new to both Access and SQL Server (I am an old skool Excel person) so I though I would see if someone comes up with something. since things are a bit quiet I will try and help you out with what little I know.
These days I don't manually link the tables to SQL Server from Access using the linked table manager, rather we have set up a table (tblTables) in SQL Server that has a list of the tables I want to link into Access. Since many of the tables in SQL Server you will not need or want on the client (MS Access) side this is a good approach.
The code below will relink all the tables from SQL Server to Access, based on the data in the tblTables table (heh, that is a mouthful!) in SQL Server.
As for queries and the like. Run absolutely everything you can off the server using Stored Procedures or Views and only pull back to Access the results. the server will process data much much faster than Access in nearly all instances.
I hope that makes sense.
here are some links that I have found useful (and put on my website).
http://www.excelyourbusiness.com.au/SQL%20Server.htm
good luck
cheers
Darryl.
'=======================
Option Compare Database
Option Explicit
Public Sub RefreshData()
'***********************************************'
' '
' This Procedure links tables in SQL Server '
' that are listed in an SQL Server table. '
' This Procedure must be customised to the '
' application's Schema. '
' '
' This procedure could be blank if linked '
' tables are not used. '
' '
'***********************************************'
Dim strText As String, msg As String, strCon As String
Dim cmd As ADODB.Command
Dim rsSP As ADODB.Recordset
Dim tbl As DAO.TableDef
On Error GoTo ErrHandler
strText = "SELECT sqlName, tblName from " & GetParameter("ListTables") & ";"
Set cmd = New ADODB.Command
cmd.ActiveConnection = DbADOConStr
cmd.CommandType = adCmdText
cmd.CommandText = strText
Set rsSP = New ADODB.Recordset
Set rsSP = cmd.Execute()
If Not rsSP.EOF Then
UnlinkODBC
strCon = DbDAOConStr
rsSP.MoveFirst
Do Until rsSP.EOF
'Debug.Print rsSP("tblNAme")
If IsTableQuery("", rsSP("tblName")) = True Then
DoCmd.DeleteObject acTable, rsSP("tblName") ' if query exists, delete it!
End If
'DoCmd.TransferDatabase acLink, "ODBC Database", strCon, acTable, rsSP("sqlName"), rsSP("tblName"), False, True
Set tbl = CurrentDb.CreateTableDef(rsSP("tblName"), dbAttachSavePWD, rsSP("sqlName"), DbDAOConStr)
CurrentDb.TableDefs.Append tbl
rsSP.MoveNext
Loop
Else
msg = "Failed to Establish Link with SQL Server !"
MsgBox msg, vbCritical + vbOKOnly, "SQL SERVER ERROR"
End If
ExitHere:
On Error Resume Next
rsSP.Close
Set rsSP = Nothing
Set cmd = Nothing
Exit Sub
ErrHandler:
strErrMsg = "modRefresh.RefreshData: "
strErrMsg = strErrMsg & Err.Number & " - " & Err.Description
ErrHandle (strErrMsg)
Resume ExitHere
End Sub
Public Function UnlinkODBC() As Boolean
'***********************************************'
' '
' This Procedure unlinks ODBC tables that '
' are in the current database '
' '
' Created by Beny Aycardo '
' Started on 08 April 2002 '
' '
'***********************************************'
Dim db As Database
Dim i As Integer
On Error GoTo ErrHandler
UnlinkODBC = False
Set db = CurrentDb 'Set the Current Database
i = 0 'set table counter to 0
Do While i < db.TableDefs.Count
If (db.TableDefs(i).Attributes And dbAttachedODBC) = 0 Then
i = i + 1 'index count if table is not linked
Else
db.TableDefs.Delete db.TableDefs(i).Name 'Delete the linked table
i = 0 'reset table counter
End If
Loop
UnlinkODBC = True
ExitHere:
On Error Resume Next
db.Close
Set db = Nothing
Exit Function
ErrHandler:
strErrMsg = "modRefresh.UnlinkODBC: "
strErrMsg = strErrMsg & Err.Number & " - " & Err.Description
ErrHandle (strErrMsg)
Resume ExitHere
End Function
Public Function IsTableQuery(DbName As String, TName As String) As Integer
Dim db As Database, Found As Integer, test As String
Const NAME_NOT_IN_COLLECTION = 3265
On Error GoTo ErrHandler
' Assume the table or query does not exist.
Found = False
' Trap for any errors.
On Error Resume Next
' If the database name is empty...
If Trim$(DbName) = "" Then
' ...then set Db to the current Db.
Set db = CurrentDb()
Else
' Otherwise, set Db to the specified open database.
Set db = DBEngine.Workspaces(0).OpenDatabase(DbName)
' See if an error occurred.
If Err Then
MsgBox "Could not find database to open: " & DbName
IsTableQuery = False
Exit Function
End If
End If
' See if the name is in the Tables collection.
test = db.TableDefs(TName).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True
' Reset the error variable.
Err = 0
' See if the name is in the Queries collection.
test = db.QueryDefs(TName$).Name
If Err <> NAME_NOT_IN_COLLECTION Then Found = True
db.Close
IsTableQuery = Found
ExitHere:
Exit Function
ErrHandler:
strErrMsg = "modRefresh.IsTableQuery: "
strErrMsg = strErrMsg & Err.Number & " - " & Err.Description
ErrHandle (strErrMsg)
Resume ExitHere
End Function
Function IsLoaded(ByVal strFormName As String) As Boolean
' Returns True if the specified form is open in Form view or Datasheet view.
Const conObjStateClosed = 0
Const conDesignView = 0
On Error GoTo ErrHandler
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
ExitHere:
Exit Function
ErrHandler:
IsLoaded = False
Resume ExitHere
End Function
'=============================================================
' and you will need this function too
'================================================================
Public Function GetParameter(strParameter As String) As String
'======================================================================================='
' '
' This function returns a parameter value given a parameter name '
' that is supplied as an argument to the function '
' This function calls a stored procedure "stprRetrieveParameter" '
' which must be created on the server '
' '
' '
' Created by Beny Aycardo '
' Started on 23 Janualry 2004 '
' '
'======================================================================================='
Dim strText As String
Dim cmd As ADODB.Command
Dim rsSP As ADODB.Recordset
On Error GoTo ErrHandler
strText = "stprRetrieveParameter '" & strParameter & "', '" & strParameterTable & "'"
Set cmd = New ADODB.Command
cmd.ActiveConnection = DbADOConStr ' Pre dimmed ADO connection string
cmd.CommandType = adCmdText
cmd.CommandText = strText
Set rsSP = New ADODB.Recordset
Set rsSP = cmd.Execute()
If rsSP.EOF Then
GetParameter = ""
Else
rsSP.MoveFirst
GetParameter = rsSP(0)
End If
ExitHere:
On Error Resume Next
rsSP.Close
Set rsSP = Nothing
Set cmd = Nothing
Exit Function
ErrHandler:
strErrMsg = "modSQLServer.GetParameter: "
strErrMsg = strErrMsg & Err.Number & " - " & Err.Description
ErrHandle (strErrMsg)
Resume ExitHere
End Function
' ==============================================================
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Doug
Murphy
Sent: Friday, 6 February 2009 11:42 AM
To: 'Discussion concerning MS SQL Server'
Subject: [dba-SQLServer] SQL Server ODBC optimization
Folks,
I sent this to the AccessD list with no responses. Possibly some one on this
list can share their insight in how best to use an Access front end
connected to a SQL Server BE via ODBC. What I am trying to learn is how to
do this most efficiently from the SQL Server perspective. Do you use
queries from Access, move all queries to SQL server and pass parameters
where required, etc.
I am know that there are some design guidelines that should be followed with
designing a system with an Access front end connected to a SQL Server back
end via ODBC to obtain good performance. This is a pretty broad question,
but could any of you folks who do this on a regular basis provide a list of
the techniques and design approaches you follow or point me to some
references? I am not looking for a treatise on the subject just a list of
items to be aware of and good design practices.
Thanks in advance.
Doug
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com
This email and any attachments may contain privileged and confidential information
and are intended for the named addressee only. If you have received this e-mail in
error, please notify the sender and delete this e-mail immediately. Any
confidentiality, privilege or copyright is not waived or lost because this e-mail
has been sent to you in error. It is your responsibility to check this e-mail and
any attachments for viruses. No warranty is made that this material is free from
computer virus or any other defect or error. Any loss/damage incurred by using this
material is not the sender's responsibility. The sender's entire liability will be
limited to resupplying the material.