[AccessD] Harnessing SQL Server with runtime

John,

This got me curious about how I do this, so I did some rooting around.

First, I find the DSN-less ODBC connect string for my SQL Server connection.
My example:

  ODBC;Driver={SQL
Server};Server=MyServer;Database=MyDb;Uid=username;Pwd=password;

For SQL Server 2008, you will need to install the SQL Server Native Client.
For this, the connect string would be:

  ODBC;Driver={SQL Server Native Client
10.0};Server=MyServer;Database=MyDb;Uid=username;Pwd=password;

The native client will connect to all SQL Server instances from version 2000
on up. You can easily connect Access 2000 to SQL Server 2008 in this manner.

Note: for TableDefs, Access conveniently mangles the connect string, so I
keep the correct one in a VBA constant.

Then I just make a SQL Pass-Through query,  and paste the connect string in
the ODBC Connect Str property. Put in your SQL, and save.

My experience is that the connect string is stable, and doesn't need to be
changed, even when you modify the query's SQL.

You can also make a table that is direct-linked to SQL server with the same
ODBC connect string. This may be easier in later versions of Access, but in
2000, you need code. The following requires a reference to ADOX (ADO Ext.
2.8 for DDL and Security):

' Adds a linked External table
'
' Parameters:
'   sProviderString     - ADO Provider string (can be ODBC Connect string)
'   sSourceTbl          - Source table name (provider)
'   sLinkTblName        - Link table name (local)
'   bSavePassword       - True: Set "Cache Link Name/Password" property
'
' Adapted from Visual Basic Programmer's Guide: Data Access Components
'
http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part3/ch14.mspx
'
Public Sub CreateLinkedExternalTable(sProviderString As String, _
    sSourceTbl As String, _
    sLinkTblName As String, _
    Optional bSavePassword As Boolean = False)

    Dim rCatalog As ADOX.Catalog
    Dim rTable As ADOX.Table

    On Error GoTo HandleErr

    ' Get current Catalog
    Set rCatalog = CurrentCatalog

    Set rTable = New ADOX.Table
    With rTable
        ' Name the new Table and set its ParentCatalog property to the
        ' open Catalog to allow access to the Properties collection.
        .Name = sLinkTblName
        Set .ParentCatalog = rCatalog
        ' Set the properties to create the link.
        .Properties("Jet OLEDB:Create Link") = True
        .Properties("Jet OLEDB:Link Provider String") = sProviderString
        .Properties("Jet OLEDB:Remote Table Name") = sSourceTbl
        If bSavePassword Then
            .Properties("Jet OLEDB:Cache Link Name/Password") = True
        End If
    End With

    ' Append the table to the Tables collection.
    rCatalog.Tables.Append rTable
    Set rCatalog = Nothing

    Exit Sub

HandleErr:
    Err.Raise Err.Number, "CreateLinkedExternalTable" & VbCrLf &
Err.Description
End Sub

With this, you can write normal, non-pass-through select queries against the
table, and use it in DAO code with it just like any other TableDef. I
wouldn't go crazy with multi-ODBC-table joins, though.

Alas, you can't write normal update, insert or delete queries against an
ODBC tabledef, as the underlying recordset is not updateable.

For that, you will need the aforementioned ODBC Pass-through queries, and
use some method for storing the base SQL like Robert's.

You can call stored procedures this way, but for that, I would recommend
using ADO, setting the parameters in code, and calling the procedure
directly.

-Ken


---------- Forwarded message ----------

> From: jwcolby <jwcolby at colbyconsulting.com>
> To: Access Developers discussion and problem solving <
> accessd at databaseadvisors.com>
> Date: Fri, 18 Mar 2011 08:18:20 -0400
> Subject: Re: [AccessD] Harnessing SQL Server with runtime
> Robert,
>
> What specific things have to be done to use the pass-through query?
>
> I took a regular query using a table linked to SQL Server and made it
> pass-through by changing the sql specific to pass through.  It immediately
> asked me for a dsn, and when I gave it one it asked me for the user name /
> password.  But it keeps asking me for the dsn / user name / password any
> time I do anything with that query or the form that is bound to that query.
>
> Am I missing something?
>
>