[AccessD] Harnessing SQL Server with runtime

Thanks Ken.  I am slowly getting this sorted.  I will try and write up what I am doing once I get it 
all figured out.

John W. Colby
www.ColbyConsulting.com

On 3/21/2011 12:18 PM, Kenneth Ismert wrote:
> 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?
>>
>>