Using ADO/OLE DB to open linked tables in Access

[BobRodes]

Just out of curiosity, does anyone know of a way to open Access linked tables without using pure OLE DB, meaning not using ODBC? We've been kicking this around on thread700-1400859.

Bob

 

[BobRodes]

Correction: "tables without using pure OLE DB" should read "tables using pure OLE DB".

 

[jrbarnett]

I've been looking at this on and off for a while, and have so far hit two stumbling blocks:

1. The connection information for linked tables seem to need an ODBC data source; so you are stuck with using the OLEDB provider for ODBC regardless of your back end platform. I've tried rewriting the code to use both non DSN ODBC links and through OLEDB and neither work.

2. Pass through queries also seem to need an ODBC data source, if I try and manually set the connection string in the immediate using an OLEDB connection string I get an "Invalid attribute in connection string"

eg
currentdb.querydefs ("qryCustomers").Connect = "Provider=SQLOLEDB;Data Source=server;User ID=userid;Password=pwd;Network Library=dbmssocn;Database=Northwind"

3. My one success in this area, however, is discovering that an Access form can accept an ADO recordset as well as a DAO recordset for its data (Access XP SP3 on WinXP SP2 talking to SQL 2000 SP4 on Windows 2003 SP 1).
This opens the option of starting with an unbound form, executing an SP against the database but then assign this as the recordset of the form. No linked tables required, but able to use a proper SQLOLEDB connection string to connect.

To demonstrate, this code is adapted directly from a reporting system I wrote and now manage:

Private Sub Form_Load()

    Dim cmd As ADODB.Command
    Dim intCount As Integer
    Dim rst As ADODB.Recordset
    
    On Error GoTo err_FormLoad
    
    Set cmd = New ADODB.Command
    CheckConnect ' Check to see if the gcnn is connected or has timed out, reconnect if closed...
    gcnn.CursorLocation = adUseClient

    With cmd
        .ActiveConnection = gcnn ' use global ADODB connection object
        .CommandType = adCmdStoredProc
        .CommandText = "my_sp_name" 
        .Parameters.Refresh
        .Parameters("@username") = gstrUser ' global login ID from login screen
        .Parameters("@acyrcode") = CURRENT_ACADEMIC_YEAR ' value from constant
        Set rst = .Execute ' Run the SP
    End With
    Set Me.Form.Recordset = rst ' now set the form recordset to the results of the code
        
    ' Set the column widths to an appropriate value
    ' Need one control on the form with a name for each field
    ' Fields in the recordset that don't exist on the form
    ' generate an error 438 (property not found)
    ' If this falls over, its in this loop somewhere...

    For intCount = 0 To Me.Form.Recordset.Fields.Count - 1
        Me.Controls(rst.Fields(intCount).Name).ControlSource = Me.Form.Recordset.Fields(intCount).Name
    Next
                
    Exit Sub
    
err_FormLoad:
    MsgBox Err.Number & " " & Err.Description
    Resume Next
    
End Sub

Issues: this is purely reporting, I don't do inserts, deletes and updates through the report view forms (but do on other forms, again by running SPs). It means I need only give execute permission on the stored procedures to the role that the accounts for this app use, which helps data security.

John

 

[Remou]

Here is the MS article:

How to bind Microsoft Access forms to ADO recordsets

http://support.microsoft.com/kb/281998/EN-US/