Access and SQL Server
I have been using Access with SQL Server backends using "linked tables" for several years now. This has been the most significant and successful change I have made in my use of Access in all my 15 years (is that true?) of using the app. I know that this is sacrilegious to do for reasons I have never understood.
The fact is that by using SQL Server for backends I have seen incredible performance gains. I have applications in Access that are used worldwide in corporations where the user simply opens the Access frontend and it connects back to the central server a world away (literally) and the performance is more than acceptable. There is NO WAY that you should EVER consider doing such a thing with JET backends – forget it.
I have even seen huge performance advantages in bad local connectivity situations. An example is one factory where they use Wireless networks out to the floor. My Access database connected to their SQL Server purrs along since it moved from its original JEY backend. In fact this has worked for three years now daily running a complex Access application capturing hundreds of thousands of records with zero problems.
By using a native JET front end (as opposed to an ADP) I am able to take advantage of local queries and local tables as necessary. Where necessary I create Views and Stored Procs and either link to them or run them using ADO. I can still use my Forms!frmName!ctrlName syntax in my JET queries (which to me is the biggest advantage Access offers over other dev tools – bar none).
Another key technique is the use of a DSNLess connection. Creating a DSN on each user’s machine is a major pain. The easiest way to make a clean, timely connection is to recreate it every time the front end is opened.
Rather than messing with ADOX, I use plain old DAO to connect my tables every time the front-end is opened (this assumes each user has their own local copy of the front end – which is nearly always necessary for performance reasons).
Here is some code I use (I am sure there are more sophisticated ways to accomplish this though so please forgive me if this isn’t pretty enough ;-)):
I have a local table called tblTables that contains a field for the Table name sof the tables I want to and a local table called tblMaster which among other Maintenance things contains a field with the Server name, one for the database and the Master user name and password. You may not want to use this technique in a very security-sensitive app but it is more than “good-enugh” security for most Access apps and certainly makes maintenance easier.
This example passes a SQL Login User name and a password to SQL Server. In this way I can have the application log on and off with one login – removing the necessity to get involved in AD groups and NT Logins (remember I always try to avoid DBAs at all costs – once they give me my database and give me DBO rights I’d rather talk to the DBA’s as seldom as possible). There are many situations where Trusted Logins would be more appropriate and I have included it in-line here commented out.
Sub AttachTables(MasterID,MasterPassword)
Err_Bak:
On Error GoTo Attach_Err
Dim MYWS As DAO.Workspace, MYDB As DAO.Database, MYRS As DAO.Recordset, myTabledef As TableDef
Dim MasterRS As DAO.Recordset
Set MYWS = DBEngine.CreateWorkspace("", MasterID, MasterPassword)
Set MYDB = MYWS.OpenDatabase(CurrentDb().Name)
Set MasterRS = MYDB.OpenRecordset("tblMaster", DB_OPEN_DYNASET)
Set MYRS = MYDB.OpenRecordset("tblTables", DB_OPEN_DYNASET)
MYRS.MoveLast
NbrTables = MYRS.RecordCount
MYRS.MoveFirst
MYWS.BeginTrans
For td = 1 To NbrTables
CurName = MYRS("TableName")
Set myTabledef = MYDB.CreateTableDef(CurName)
myTabledef.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
MasterRS("Database") & ";SERVER=" & MasterRS("Server") & _
";UID=" & MasterRS("UserName") & ";PWD=" & MasterRS("Pwd")
‘If using a trusted connection use:
‘myTabledef.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
‘ MasterRS("Database") & ";SERVER=" & _ ‘MasterRS("Server") & _ ”;Trusted_Connection=Yes;"
myTabledef.SourceTableName = CurName
MYDB.TableDefs.Append myTabledef
MYRS.MoveNext
Next td
MYWS.CommitTrans
Attach_Exit:
MYWS.Close
Exit Sub
Attach_Err:
MsgBox Err.Description, , "Attach"
If Err = 3012 Then Exit Sub
MsgBox CStr(CStr(Err) + " - " + Error$)
MYWS.Rollback
MsgBox "Attachment Failed", vbInformation, "Data Connection"
Application.Quit
GoTo Attach_Exit
End Sub
If one were to Google (or MSN Search) “DSNLess and DAO” you will see several versions of this code (probably some of which borrowed the code like I did from somewhere on the Internet a years ago (thanx Ken Getz I guess)).
My point here is not my particular code but rather to open people’s eyes to the use of DSNless table linking and the use of SQL Server as a backend.
As far as I’m concerned Microsoft has let us all down by not pushing Access as a natural frontend for SQL Server apps. This is a big loss for us solution developers, for our users and even for Microsoft – but I guess they have a better solution out there somewhere (I haven’t found it yet but I’m still open minded).