I have a project that requires the use of SQL Server (in place already) with A03 as the frontend. I've downloaded SQLServer Express 2012 for testing purposes and managed to successfully interface A03 with SQLServer using SQL Server Native Client 11.0. I use the following (ADO instead ODBC) to SELECT the contents of a table which I created in SQLServer Express 12. This code that NOT require a Linked table (dbo.tbl1) in the Access FE.
Code:
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sXMLResult As String
'OLE DB (ADO) Native CLient
con.ConnectionString = "Provider=SQLNCLI11;" _
& "DataSource=MSSQLSERVER;" _
& "Server=(local);" _
& "Database=JP1;" _
& "Uid=sa;" _
& "Pwd=sapass;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open
Set rst.ActiveConnection = con
rst.Open "SELECT * FROM dbo.tbl1;", con, adOpenKeyset, adLockPessimistic
Set Me.Recordset = rst
con.Close
Set con = Nothing
To define a Passthrough query when using ODBC, I can define it in the query section and set the "ODBC Connect Str" property of the query to something like
Code:
ODBC;DSN=XXX;SERVER=YYY;
Do you know how to define Passthrough queries when using ADO instead of ODBC?
Passtrhough with ADO/DAO objects is merely passing the object a remote connection vs a connection to CurrentDatabase. Oh... and in the case of.... I can not remember if it was ADO/DAO objects I thought there was an attribute to flip to "Pass-Through" but I am not seeing that in my code. Perhaps conversion of all queries to Stored Procedures removed that detail/requirement.
What exactly are you asking?
Perhaps confusion using the SQL Server Native Client vs the ODBC driver? Using ADO/DAO objects is the same either way... the difference lies in how you define the connection / to which driver you connect to the server through.
I am new to SQl Server and as a start I am trying to use Access as FE and execute code (such as a query) on SQL Server.
1) Is it advisable to use ODBC (DSN or DSN-less) or the ADO code which I include above and which does not use DSN?
2) I was under the impressin that if I used ODBC then I could use the Query builder in Access (not the graphical builder) to define Passthrough queries, where the "ODBC Connect Str" property is set.
3) If you can direct me to any general "how-to", showing the interaction of Access and SQL Server, it would be appreciated.
John
I know you are such an expert with Access to MySQL... for the most part you should find very little different in reguards to connecting to SQL Server in place of MySQL.
I have only been working with the SQL Server driver that comes with Windows. I have not tried / tested / installed the SQL Server "Native Client" ever.
You can build queries in Access, through when you push the "Pass-Through" button is disables parts of the Access query builder. I believe you must work in the SQL editor in that case. You should notice no difference between working with MySQL to SQL Server in this regard.
All that should really change is that you need a different connection string to connect to the different BE DB. You may choose to configure a DSN, you may go DSN-less.
My SQL Server connection strings are as follows:
Code:
Public Function ODBCConnectString() As String
On Error GoTo Err_ODBCConnectString
'LOC for a DSN'less connection
ODBCConnectString = "ODBC;Driver=SQL Server;Server=" & Me.DBServer & ";Database=" & Me.DBDatabase & ";UID=" & Me.DBUID & ";PWD=" & Me.DBPWD & ";"
Exit_ODBCConnectString:
Exit Function
Err_ODBCConnectString:
Call errorhandler_MsgBox("Class: clsObjAppSettings, Function: ODBCConnectString()")
ODBCConnectString = vbNullString
Resume Exit_ODBCConnectString
End Function
Public Function MSysObjectsConnectString() As String
On Error GoTo Err_MSysObjectsConnectString
'LOC for a DSN'less connection
MSysObjectsConnectString = "Driver=SQL Server;Server=" & Me.DBServer & ";Database=" & Me.DBDatabase & ";Description=Fandango FE;UID=" & Me.DBUID & ";PWD=" & Me.DBPWD & ";APP=2007 Microsoft Office system;"
Exit_MSysObjectsConnectString:
Exit Function
Err_MSysObjectsConnectString:
Call errorhandler_MsgBox("Class: clsObjAppSettings, Function: MSysObjectsConnectString()")
MSysObjectsConnectString = vbNullString
Resume Exit_MSysObjectsConnectString
End Function
The ODBC string is used for DAO objects, such as my nested DAO.QueryDef solution to download records into a FE temp table:
Thanks for your kind words regarding MySQL, but have limited experience in MySQL, although I did manage to connect Access to MySQL with your help.
BTW, did you receive the book which I sent over?
I will try what you mention, but was looking forward to using ADO with the Native Client.
John
Success! I took your advice and created a Srored Procedure (JPSP1) and successfully called it from within Access and displayed the result on a form. I include the code below for reference.
Code:
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
con.ConnectionString = "Provider=SQLNCLI11;" _
& "DataSource=MSSQLSERVER;" _
& "Server=(local);" _
& "Database=JP1;" _
& "Uid=sa;" _
& "Pwd=sapass;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open
[COLOR=red]con.CursorLocation = adUseClient
con.CommandTimeout = 0
Set rst = con.Execute("JPSP1")[/COLOR]
Set Me.Recordset = rst
con.Close
Set con = Nothing
Oh, and as reminder... if you choose to execute the Stored Procedures with an ADODB.Command object then you are also able to make use of ADODB.Parameter which encapsulate data being passed into the SP and provides protection against SQL injection attacks.
The call to .Execute() returns an ADODB.Recordset object with any result set.
I include below, for reference purposes, how A03 can retrieve from SQL Express 2012 all fields that have field lngID = 8, by passing the value 8 in the Parameter @lngNum and calling a Stored Procedure called JPSP3. The code uses driver SQL Server Native Client 11.0
Code:
Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim cmd As New ADODB.Command
'OLE DB (ADO) Native CLient
con.ConnectionString = "Provider=SQLNCLI11;" _
& "DataSource=MSSQLSERVER;" _
& "Server=(local);" _
& "Database=JP1;" _
& "Uid=sa;" _
& "Pwd=sapass;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open
con.CursorLocation = adUseClient
con.CommandTimeout = 0
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = con
.CommandText = "JPSP3"
.CommandType = adCmdStoredProc
.Parameters.Refresh
.Parameters("@lngNum").Value = 8
End With
Set rst = cmd.Execute
Set Me.Recordset = rst
con.Close
Set con = Nothing
Set cmd = Nothing
Set rst = Nothing
As I get more involved with using ADO to connect to SQL Server using the code above, I am wondering how I can populate a form with the table fields using the Access wizard. There is no linked table to use, so I created a DSN which points to the db, created the link to the table, used this link to populate the form, then deleted the link and used ADO thereafter. Is there a more elegant way to populate the form?
Also, do I need to create and destroy the ADO connection for every action which I take such as DELETE, UPDATE, INSERT etc. In other words do I need to
1) Create Connection
2) DELETE record
3) Destroy Connection
Since my architecture uses an FE temp table cache for Multiple Items forms (the record list forms), to make my Add / Edit record forms, I get started by creating a form meshed up with the FE temp table. Then I unbind the form, and all of the fields, to ease the process of creating unbound forms. I also shut off the lower status bar / move to record bar on those single record unbound forms.
As much as possible I moved shared SQL into a "dbutils" module. So calls to empty the FE temp table start in the DB table class, and are forwarded to the shared "dbutils" module. The class method is a wrapper which passes to the shared code the FE temp table name, which itself is an attribute of the DB class instance.
In each of the DB classes, I have a method EmptyLocalTmpTbl() as follows:
Code:
'This API empties the FE temp table used to cache records of this type
Public Function EmptyLocalTmpTbl() As Boolean
On Error GoTo Err_EmptyLocalTmpTbl
'Empty the memory of what was in the temp table
Me.FETempTableContentsID = 0
'Call the shared API and return its return code
EmptyLocalTmpTbl = dbutils_EmptyLocalTmpTbl(Me.FETempTableName)
Exit_EmptyLocalTmpTbl:
Exit Function
Err_EmptyLocalTmpTbl:
Call errorhandler_MsgBox("Class: clsObjPartsTbl, Function: EmptyLocalTmpTbl()")
EmptyLocalTmpTbl = False
Resume Exit_EmptyLocalTmpTbl
End Function
Which in turn wraps a call to shared code / module dbutils_EmptyLocalTmpTbl()
Code:
'This API empties the FE temp table used to cache records of this type
Public Function dbutils_EmptyLocalTmpTbl(ByVal strTableName As String) As Boolean
On Error GoTo Err_dbutils_EmptyLocalTmpTbl
Dim adoCMD As ADODB.Command
Dim strSQL As String
'Define a query to empty the temp table
strSQL = "DELETE" & vbCrLf & _
"FROM " & strTableName & ";"
'Define attachment to database table specifics and execute commands via With block
Set adoCMD = New ADODB.Command
With adoCMD
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.Execute
End With
'Good return code
dbutils_EmptyLocalTmpTbl = True
Exit_dbutils_EmptyLocalTmpTbl:
'Clean up the connection to the database
Set adoCMD = Nothing
Exit Function
Err_dbutils_EmptyLocalTmpTbl:
Call errorhandler_MsgBox("Module: modshared_dbutils, Function: dbutils_EmptyLocalTmpTbl()")
dbutils_EmptyLocalTmpTbl = False
Resume Exit_dbutils_EmptyLocalTmpTbl
End Function
So rather than having the adoCMD object being defined in each DBtable class, I moved that into shared code, where I code it only once.
When I need to create a new DB class, I may copy a DB class to speed up creation of the new class. I start by building the FE temp table, then I copy that FE temp table to a workbench database (another Access DB) where my code builder is. I type the name of the temp table into code builder, un-commented the desired LOC I need built per-DB table column, push the button, and code builder spits out the LOC's based on the schema of the FE temp table. This has greatly sped up dealing with the increased verbosity of OO code.
Code builder takes the column name and plugs that into defined template code. It might be in the form of each LOC works with a given column name. It might be concatenating column names up on a single LOC. Once done, I copy/paste from the Immediate window in the one DB and paste into the VBA editor of the other DB.
In the case of variable initialization, perhaps I need to adjust the generated code to be the correct datatype.
In the case of the FE temp table having extra columns which are not really part of the BE DB table, I simply delete the unnecessary code. Example: For tables involving look-up tables, I still consider that one (logical) table. To INSERT/UPDATE to the main table within that schema, I do not need to send back string names of values looked up from those supporting tables... I only need to send in the ID number that relates to that string, as the main table only stores the ID number. The meaning of the ID number column I may delete in the case of INSERT/UPDATE code. For SELECT code, then I need those supporting columns.
Thank you.
