Doug Murphy
dw-murphy at cox.net
Thu Feb 5 21:26:57 CST 2009
Thank you Darryl,
I will add to my library.
Doug
-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Darryl
Collins
Sent: Thursday, February 05, 2009 6:27 PM
To: 'Discussion concerning MS SQL Server'
Subject: Re: [dba-SQLServer] SQL Server ODBC optimization
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.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com